Printing to Acrobat - Possible to use VBA to program PDF file name

[Tom Joseph]

I would like to automate creation of a series of PDFs. Each from a print
range on a different Excel sheet.

I would like the PDF file names to be taken from a series of cells in Excel.
eg

PDF 1 would be called "20090129_ED-IPSTAT-ONC.pdf". This string would be
stored in Cell A1.

PDF 2 would be called "20090129_PerformanceSummary.pdf". This string would
be stored in Cell A2.

and so on.


Any help would be greatly appreciated.

 

[SeanC UK]

Hi Tom,

The best way to find any solution to something that you do manually is to
record a macro to see how Excel does that process itself, and then build your
code around that. In doing so using the "Save As... PDF or XPS" in Excel 2007
you will get something like:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\FileLocation\myName.pdf", Quality:= _
xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
OpenAfterPublish:=False

So the Filename parameter of this function is what you need to set to your
filename string in cells A1, A2 etc. So something like:
strLocation = "C:\FileLocation\"

and as you loop through each range you wish to save you would have:
strFileName = strLocation & MyWorksheet.Cells(RowCounter,1)
incrementing the RowCounter for each Range.

If you are not using the above method of SaveAs, then I'd suggest you simply
record you method as you do it and see what code you get. No marco will give
you looping, it will just be what you do, so if you do it for one sheet you
can then build a loop in yourself.

Cheers,

Sean.

 

[Tom Joseph]

Hi Sean.

Thanks for the note. Is anything like this possible in Excel 2003? This
client does not have 2007. Thanks.

 

[Tom Joseph]

Thanks, Sean.

Is something like your recommendation possible in Excel 2003?
This client does not have Excel 2007.

 

[brzak]

Thanks, Sean.

Is something like your recommendation possible in Excel 2003?
This client does not have Excel 2007.  

An alternative is to use CutePDF [www.cutepdf.com]
combined with AutoIT [www.autoitscript.com].

You install both of the above programs. As well as being
useful in your current situation, they will be useful
elsewhere

CutePDF gives you the option of printing to PDF from
any program. AutoIT allows you to automate tasks.

The process will involve sending sheets to print with VBA code,
and have an AutoIT script running to enter filenames, press enter.

1. Write macro to print required sheets. e.g.

sub exportMyPDFs()
dim n as integer

for n = 1 to 10
'activate CutePDF Writer
Application.ActivePrinter = "CutePDF Writer on CPW2:"
'print your files
ActiveWindow.SelectedSheets.PrintOut Copies:=1
next n
end sub

2. Write script for the Save As dialog box from CutePDF. e.g.

$i = 0

Do
; WinWait("Save As")
WinWaitActive("Save As")
$i = $i + 1
Send("c:\destination path\Printout" $i)
Send("{ENTER}")
WinWaitClose("Save As")
Until $i >= 10

3. Ensure script is running in background, run macro.

Notes:

Script will run on ALL 'Save As' dialog boxes,
so pause/exit while you don't want it to be active

 

[SeanC UK]

Hi Tom,

Excel 2007 supports this feature (I think it is downloadable either as an
Add-In or was part of the Office 2007 Service Pack, maybe both). I don't
think that Microsoft supported this for previous versions, although I'm not
certain, but I believe that such a feature is available, possibly if you have
Adobe PDF writing software installed.

If you know that the target PCs already have the ability to Save As PDF, or
more likely to Print As PDF then you should try recording a macro to see what
code appears when you do it. I have 2003 and 2007 installed on this machine,
but I don't have any PDF functionailty in 2003, so I am unable to help you
with any code.

You might find something on the net with demo code that does this sort of
thing, but you will most likely require something installed that will be
referenced in your code (like a PDF Distiller). If your target PCs can't
already Print As PDF then I think you will be having difficulties. If they do
then recording a macro as you produce a dummy PDF version will be the best
way to produce the code. If you can do this but then have problems
implementing the code then feel free to paste it here and I can try to help
you further (albeit rather blndly as I won't be able to test it myself).

Sean.

 

[Tom Joseph]

Thanks. I appreciate the help.

Hi Tom,

Excel 2007 supports this feature (I think it is downloadable either as an
Add-In or was part of the Office 2007 Service Pack, maybe both). I don't
think that Microsoft supported this for previous versions, although I'm not
certain, but I believe that such a feature is available, possibly if you have
Adobe PDF writing software installed.

If you know that the target PCs already have the ability to Save As PDF, or
more likely to Print As PDF then you should try recording a macro to see what
code appears when you do it. I have 2003 and 2007 installed on this machine,
but I don't have any PDF functionailty in 2003, so I am unable to help you
with any code.

You might find something on the net with demo code that does this sort of
thing, but you will most likely require something installed that will be
referenced in your code (like a PDF Distiller). If your target PCs can't
already Print As PDF then I think you will be having difficulties. If they do
then recording a macro as you produce a dummy PDF version will be the best
way to produce the code. If you can do this but then have problems
implementing the code then feel free to paste it here and I can try to help
you further (albeit rather blndly as I won't be able to test it myself).

Sean.

 

[Tom Joseph]

Thanks. I appreciate it.

Thanks, Sean.

Is something like your recommendation possible in Excel 2003?
This client does not have Excel 2007.

An alternative is to use CutePDF [www.cutepdf.com]
combined with AutoIT [www.autoitscript.com].

You install both of the above programs. As well as being
useful in your current situation, they will be useful
elsewhere

CutePDF gives you the option of printing to PDF from
any program. AutoIT allows you to automate tasks.

The process will involve sending sheets to print with VBA code,
and have an AutoIT script running to enter filenames, press enter.

1. Write macro to print required sheets. e.g.

sub exportMyPDFs()
dim n as integer

for n = 1 to 10
'activate CutePDF Writer
Application.ActivePrinter = "CutePDF Writer on CPW2:"
'print your files
ActiveWindow.SelectedSheets.PrintOut Copies:=1
next n
end sub

2. Write script for the Save As dialog box from CutePDF. e.g.

$i = 0

Do
; WinWait("Save As")
WinWaitActive("Save As")
$i = $i + 1
Send("c:\destination path\Printout" $i)
Send("{ENTER}")
WinWaitClose("Save As")
Until $i >= 10

3. Ensure script is running in background, run macro.

Notes:

Script will run on ALL 'Save As' dialog boxes,
so pause/exit while you don't want it to be active

 

[Mark]

I have a Home sheet that has a series of names

Example: A11 = c:\PDFFiles\Sheet1.pdf
A12 = c:\PDFFiles\Sheet2.pdf


You can use formulas for create names.

I have the vba sub linked to a button on Home Sheet.

Sub CreatePFF()

Sheets("Sheet1").Select
Range("a1").Select

Dim PDFFilename As String

Let PDFFilename = Sheets("Home").Range("a11")
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PDFFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Let PDFFilename = Sheets("Home").Range("a12")
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PDFFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Range("a1").Select

Sheets("Home").Select
Range("b5").Select

End Sub

 

[Mark]

Sorry this posted before I was done

I have a Home sheet that has a series of names
Print setup is done in each sheet based on the reports

Clicking the button will create all 20 reports and place them in the folder
on my C Drive on I can change it to a folder on the Server.


Example: A11 = c:\PDFFiles\Sheet1.pdf
A12 = c:\PDFFiles\Sheet2.pdf
I have 20 names for reports listed

You can use formulas for create names.

I have the vba sub linked to a button on Home Sheet.

Sub CreatePDF()

Sheets("Sheet1").Select
Range("a1").Select

Dim PDFFilename As String

Let PDFFilename = Sheets("Home").Range("a11")
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PDFFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False

Sheets("Sheet2").Select
Range("a1").Select

Let PDFFilename = Sheets("Home").Range("a12")
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=PDFFilename, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


Sheets("Home").Select
Range("a1").Select

End Sub