Macro to PDF all sheets automatically

[jaxidern]

Hi all Gurus,

Hope you can help me out with this simple request. Thanks

System:
1. Excel 2003
2. Acrobat Acrobat 9 Standard

Issue:
I have to do an admin job of filtering files every week!!! There are 20 tabs, and i have to pdf them one-by-one base on each of the tab names. Can someone lessen my misery, thanks!!!

Requirements:
Macro should pdf all sheets in the excel file according the the tabs available.
The front part of the PDF name should carry the file name then followed by the sheet name behind. Example, filename is called test. First sheet is called apple, second sheet called orange and so on.... So the pdf file of the first sheet should be called <<TEST apple.pdf>> and the second sheet called <<TEST orange.pdf>>

 

[Worf]

Hello

Try the code below. Please note that it was tested with Excel 2007 & Adobe Reader 10.1.1

Sub SaveAsPDF()

Dim dir_s$, tail$, sht As Worksheet

dir_s = ThisWorkbook.Path
ChDir dir_s
dir_s = dir_s & "\" & ThisWorkbook.Name
For Each sht In ThisWorkbook.Worksheets
    tail = dir_s & "_" & sht.Name & ".pdf"
    sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=tail, quality:=xlQualityStandard, _
    includedocproperties:=True, ignoreprintareas:=False, openafterpublish:=False
Next
    
End Sub

 

[jaxidern]

Hi Worf,

Thanks for sharing. I receive this prompt: "compile error: Method or data member not found" when i try running the macro. It was highlighting "ExportAsFixedFormat" in the code as well. Not sure where the issue is.. thanks

 

[Worf]

I receive this prompt: "compile error: Method or data member not found" when i try running the macro. It was highlighting "ExportAsFixedFormat" in the code as well.

Hi

Assuming you are using Excel 03, this probably means this method doesn't exist in this version, at least with this name. I don't have access to Excel 03 right now, and don't quite remember how it handles PDFs. As I said, my macro was developed in Excel 07.

Can you use a Office 07 machine to perform that task?

 

[mgm05267]

Hi....

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

This might help....

or

if you have pdf writer (when you go for file save as option),

ActiveWorkbook.SaveAs Filename:= _
"D:\Document.pdf", _
FileFormat:=pdf, CreateBackup:=False

Regards,

MGM

 

[mgm05267]

Hi....

ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"

This might help....

or

if you have pdf writer (when you go for file save as option),

ActiveWorkbook.SaveAs Filename:= _
"D:\Document.pdf", _
FileFormat:=pdf, CreateBackup:=False

Regards,

MGM