Printing and saving multiple Excel worksheets into PDF

[magicrjm]

What I'm trying to do is, automatically print an Excel workbook that has up to 400 worksheets. The curve ball is, I want each individual worksheet to be automatically converted into a PDF and automatically be saved the name of the worksheet within the Excel workbook. How can I accomplish this?

Ryan

 

[sxschech]

Have a look at this thread...

Programmatically create a PDF from Access 2002
thread707-1119207

1) Copy the code from the 08 Nov 2005 November Post and paste into an excel vba module

2) Scroll down to the 28-Mar-2007 post from Sandra123 where she explains which lines of code need to be changed to run under excel. She also provides an answer for printing charts to PDF.

3) For the naming as the same name as the worksheet, I created a sub that is called from an existing macro. This one creates a numeric sequence number at the end of the file as I am giving the file name the same name. Since you want the filename to be the same as the worksheet, you can eliminate that step and have the code like this...

[tt]
RunReportAsPDF prmRptName:=ActiveSheet.Name, prmPdfName:=Replace(stpath & ActiveSheet.Name, ".XLS",".PDF")
[/tt]

Sub PrintPDF()
    stpath = "\\Discimageserver\PDFfiles\" & ActiveWorkbook.Name
    intCounter = intCounter + 1
    
    If intPrintType = 0 Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1
    Else
        RunReportAsPDF prmRptName:=ActiveSheet.Name, prmPdfName:=Replace(stpath, ".XLS", intCounter & ".PDF")
    End If
End Sub