EXP Systems Forum: Giving Name to a pdf created via VBA

Message

   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

Author	Message
Kabutosan Newbie Joined: 17 Jul 06 Posts: 4	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 Logged

Michel_K17 Moderator Group Forum Administrator Joined: 25 Jan 03 Posts: 1673	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 Logged

Kabutosan Newbie Joined: 17 Jul 06 Posts: 4	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 Logged

Michel_K17 Moderator Group Forum Administrator Joined: 25 Jan 03 Posts: 1673	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 Logged

Kabutosan Newbie Joined: 17 Jul 06 Posts: 4	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 Logged

Michel_K17 Moderator Group Forum Administrator Joined: 25 Jan 03 Posts: 1673	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 Logged

Kabutosan Newbie Joined: 17 Jul 06 Posts: 4	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 Logged

Programming
EXP Systems Forum : PDF reDirect : Programming