Active TopicsActive Topics  Display List of Forum MembersMemberlist  Search The ForumSearch  HelpHelp
  RegisterRegister  LoginLogin
Programming
 EXP Systems Forum : PDF reDirect : Programming
Topic: Giving Name to a pdf created via VBA Post Reply Post New Topic
Author Message
Kabutosan
Newbie
Newbie


Joined: 17 Jul 06
Posts: 4
Quote Kabutosan Replybullet Topic: Giving Name to a pdf created via VBA
    Posted: 17 Jul 06 at 1:16PM

Hi,

I've written a short macro which convert a specified range of a spreadsheet in pdf.

By default, the pdf created has the same name as the worksheet, and I'd like to specify in the code another name.

Thanks for helping!

Ben

IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet Posted: 18 Jul 06 at 8:55AM
Hello,

   Yes you can. I have written sample VBA Code that shows you how, and includes a VB Class to simplify your code. It is posted on the forum here:
http://www.exp-systems.com/Forum_exp/forum_topics.asp?FID=7&PN=0

   Cheers!

Michel
IP IP Logged
Kabutosan
Newbie
Newbie


Joined: 17 Jul 06
Posts: 4
Quote Kabutosan Replybullet Posted: 18 Jul 06 at 1:48PM

Hi Michel,

In the meanwhile I somehow managed to create this:

Sub PDF()

Dim PSFileName  As String
Dim PDFFileName As String

Dim PfNbre      As Long
Dim DatePf      As Variant

PfNbre = Sheets("Portfolio Details").Range("A13").value
DatePf = Sheets("Portfolio Details").Range("C4").value

PSFileName = "C:\Documents and Settings\katlambe\Desktop\trial.ps"
PDFFileName = "C:\Documents and Settings\katlambe\Desktop\Mdys_Pfolio" & PfNbre & DatePf & ".pdf"

Dim MySheet As Worksheet
Set MySheet = ActiveSheet
MySheet.Range("B5:AF140").PrintOut copies:=1, preview:=False, ActivePrinter:="Acrobat Distiller", printtofile:=True, collate:=True, prtofilename:=PSFileName

Dim myPDF As PdfDistiller
Set myPDF = New PdfDistiller
myPDF.FileToPDF PSFileName, PDFFileName, ""

Kill PSFileName

End Sub

Basically, the aim is to create a pdf from a given range, and to give to the pdf created a specific name using data from the workbook (PfNbre and DatePf).

Unfortunately, it doesn't work, and sometimes tries to print the pdf without saving it (the opposite of wht I want..)

Thanks for helping!!

Ben 

 

 

IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet Posted: 24 Jul 06 at 3:46PM

Hi,

   I see that you declared your variable "PfNbre" as a "Long" (ie a number), but then you are trying to place it in the filename without converting it into a "string" (ie a text variable). My suggestions is that you convert PfNbre into a string, and also use the Trim function to remove the leading space character that VB adds during the number-to-string conversion.

So, ... & PfNbre & ".pdf" should be: ... & Trim$(Str$(PfNbre)) & ".pdf"

   Cheers!

Michel

P.S. Sorry for the late reply. I'm in Northern Canada visiting my parents. Unfortunately, the only internet access is through my PDA which is very slow.

 

IP IP Logged
Kabutosan
Newbie
Newbie


Joined: 17 Jul 06
Posts: 4
Quote Kabutosan Replybullet Posted: 31 Jul 06 at 4:56AM

Hi,

Thanks a lot, now it's working properly and exactly as I want.

A tiny flaw though: whereas the range I want to convert has colors, the pdf file created is in black and white..

Do you know how this could be fixed?

Many thanks,

Ben

IP IP Logged
Michel_K17
Moderator Group
Moderator Group
Avatar
Forum Administrator

Joined: 25 Jan 03
Posts: 1666
Quote Michel_K17 Replybullet Posted: 01 Aug 06 at 5:05PM
Hi,

   I would check the printer properties (with the sheet that you are trying to print selected in Excel) and ensure that the setting for "color" is selected.

   You could also check the setting in your code, but you cannot change the setting in VBA: the property is "read only". For example:

Private Sub MyCommand_Click()
    Dim BW As Boolean
    BW = Thisworksheet.PageSetup.BlackAndWhite
    If BW Then
        MsgBox "Output will be in Black & White"
    Else
        MsgBox "Output will be in Color"
    End If
End Sub


   You could change the settings using VBA, but it requires quite a bit of code and declarations to read all the settings, make the changes, and then save them back. I think it would be easier just to make sure that the properties are setup correctly.

   Cheers!

Michel

IP IP Logged
Kabutosan
Newbie
Newbie


Joined: 17 Jul 06
Posts: 4
Quote Kabutosan Replybullet Posted: 22 Aug 06 at 9:34AM

I managed to work it out:

Creating the PDF file from a given range is easily done via 'Record macro' as long as you have an Adobe Printer.

It gets slightly complicated afterwards, because we have to copy the default pdf file (same name as the workbook), change its name, store it in the appropriate folder and destroy the first PDF...it requires a 'CloseWindow' module , and pauses in the program.

If interested, let me know..

IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum