Print Page | Close Window

Giving Name to a pdf created via VBA

Printed From: www.exp-systems.com
Category: PDF reDirect
Forum Name: Programming
Forum Discription: VBA and Batch Tools to control PDF reDirect Pro
URL: http://www.exp-systems.com/Forum_exp/forum_posts.asp?TID=242
Printed Date: 22 Nov 24 at 8:33AM


Topic: Giving Name to a pdf created via VBA
Posted By: Kabutosan
Subject: Giving Name to a pdf created via VBA
Date 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


Replies:
Posted By: Michel_K17
Date 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

Posted By: Kabutosan
Date 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 

 

 

Posted By: Michel_K17
Date 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.

 

Posted By: Kabutosan
Date 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

Posted By: Michel_K17
Date 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


Posted By: Kabutosan
Date 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..


Print Page | Close Window