I did a search on PDF in the Excel forum but I didn’t find anything that answered my question (or, if I did, I wasn’t smart enough to know it).
I have a series of files that all contain multiple worksheets. All the worksheets have defined print areas. I need to create a macro that will loop through each sheet, capture the sheet name and use that as the file name, and create a PDF file from the print area, then store that file in a specific folder.
I can do everything except actually create the PDF file. I have tried several methods, as follows:
This uses the SENDKEYS method, but it doesn’t actually create the PDF file, for whatever reason. It selects the wrong folder and seems to go through the motions of creating the PDF file, but it doesn’t actually do it. Don’t know why.
Sub TestPrint1()
Dim sCounter As Integer
Dim iSheet As Integer
Dim sName As String
Dim FILENAM
sCounter = ActiveWorkbook.Worksheets.Count
For iSheet = 1 To sCounter
Worksheets(iSheet).Activate
getprinter = ActivePrinter
sName = ActiveSheet.Name
ActivePrinter = “Adobe PDF on Ne04:”
FILENAM = “c:PDF_Tests” & sName & “.PDF”
Application.SendKeys FILENAM, True
Application.SendKeys “{Enter}”
Application.SendKeys “Y”
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:=”Adobe PDF on Ne04:”
Application.Wait (Now + TimeValue(“0.00.01”))
ActivePrinter = getprinter
Next iSheet
End Sub
Here is another approach that seemed promising, but it doesn’t do the job either. It generates several error messages.
Sub TestPrint1()
Dim sCounter As Integer
Dim iSheet As Integer
Dim sName As String
sCounter = ActiveWorkbook.Worksheets.Count
getprinter = ActivePrinter
For iSheet = 1 To sCounter
Worksheets(iSheet).Activate
sName = ActiveSheet.Name
getprinter = ActivePrinter
ActivePrinter = “Adobe PDF on Ne03:”
ActiveSheet.PrintOut PrintToFile:=True, PrToFileName:=”c:PDF_Tests” & sName & “.PDF”
ActivePrinter = getprinter
Next iSheet
End Sub
Does anyone on this forum have any ideas for me? I would certainly appreciate them.
Thanks,