Export Excel Sheet to PDF available for printing

[littlejilly]

Hello All,

I have an excel workbook with several sheets in it. On the Summary tab, I have added in an ActivX command button in hopes that I can correctly write code that will copy the content seen on the sheet (values only) and copy it to a pdf and open the file.

I have used the following code but I am coming up with an error:

Private Sub PrintPDF_Click()

Dim fileName As String

Sheets("Summary").Select
'set the path and file name
fileName = "C:WINDOWSDesktopsheet1.PDF"
'Enters the name and path of the PDF file when the PrintFileAs dialog box is opened
SendKeys fileName & "{ENTER}", False
'prints the file
Worksheets(1).PrintOut ActivePrinter = "Acrobat PDFWriter on LPT1:"

end sub

I have colored the line in red where I receive the following error:

Run-time error '1004': The number must be between 1 and 32767. Try again by entering a number in this range.

I am not even sure that this is the correct code. I dont necessaily need to print automatically but if I could at least get the pdf to open with a copy of the summary tab, that would be most helpful.

Any suggestions are greatly appreciated.

 

[sergioMabres]

Change printer "Acrobat PDFWriter on LPT1:" to another printer and test the macro
I think the 1004 error is from the printer driver not from excel

 

[littlejilly]

Thanks for your response. It was most helpful. I was able to edit the code so that it prints to PDF file. Any idea how to automatically name the file and have it save to the desktop, lets say "Summary" Also, might you have a suggestion on how to add in code so that it scales down the excel sheet and prints to a landscape direction pdf?

THank you again