Print to PDF with file name referencing a cell

[03aviator]

I have a large workbook that I print about 11 print ranges into PDF files and I want to give a name to the saved PDF files based on a different cell in the main workbook. When running individual macros for each print range everything works fine, but when trying to run all as one macro only the first file is created and the others aren't. I am using Windows XP Professional SP 3 with MS Office Excel 2007 with Adobe Acrobat 9 Standard Edition.

This is my code:

Sub PrintPDFPage1()
'
' PrintPDFPage1 Macro
'
'
Application.Goto Reference:="Print_Page_1"
Filename = ActiveSheet.Range("A100").Value
SendKeys Filename & "{ENTER}", False
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$74"
Application.ActivePrinter = "Adobe PDF on Ne01:"
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,""Adobe PDF on Ne01:"",,TRUE,,FALSE)"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
'
' Sheets("LBPS Bucket Report").Select
Range("A1").Select '
End Sub

each print macro is the same, just the cell refernce for the filename changes.

Any recommendations? I am just learning how to use the programming functions of MS Excel.

Thanks

 

[Derek Brown]

Hello and welcome to The Board.
I don't know if you will find the answer to your specific problem in the following link, but you may find it useful anyway:
http://www.excelguru.ca/search/node/creator+pdf

 

[03aviator]

Thanks for looking at my problem!

I've check the links you provided and they are helpful, however my work computer doesn't have PDFCreator, nor can I add it without input / concern from the IT department.

My biggest problem remains with my not being able to change the filename in my save function - the first name that is used remains in effect no matter how many times the reference tries to rename it. This is even though each time it passes the following line of code the cell reference changes:

Filename = ActiveSheet.Range("A100").Value

Each pass I change the A100 to A101 then A102, etc.; but the actual name for saving is not changed when I do the "SendKeys Filename" command.

 

[03aviator]

Any ideas on how to change the filename while running this MACRO?

 

[Derek Brown]

I don't use Adobe for creating PDFs so I am unable to test anything but have a look at the following:
http://www.mrexcel.com/forum/showthread.php?t=154163

 

[03aviator]

The first run through it will save to the initial filename defined, however, the filename will remain the same even thought the cell reference (with the filename in it) changes on each pass. Therefore only Page one of my report is saved to PDF - not all eleven.