export every sheet like .pdf with its own name

[finenovembre]

hi
I'm italian

Someone could please write the code in vba to save every sheet in .pdf format in the same directory with its own sheet's name.

exemple of sheet name:

sheet 1 ---> bike
sheet 2 ---> Jenny
sheet 3 ---> house

I have to save in "desktop" all my .pdf file.
bike.pdf
Jenny.pdf
house.pdf
and at the end i have to print all this new pdf file

thanks you

 

[Domenic]

I'm not sure that this is exactly what you're looking for, but hopefully it's a start. The following macro creates a PDF file for every worksheet in the active workbook, names the PDF file after the name of the worksheet, and saves the PDF file in the same folder as the active workbook...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] SheetsToPDFs()

    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] wks [color=darkblue]As[/color] Worksheet

    strPath = ActiveWorkbook.Path & "\"
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] wks [color=darkblue]In[/color] ActiveWorkbook.Worksheets
        wks.ExportAsFixedFormat xlTypePDF, strPath & wks.Name & ".pdf"
    [color=darkblue]Next[/color] wks
        
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Note that the ExportAsFixedFormat method has other parameters that can be set. Have a look at the help file for additional information.

 

[finenovembre]

thank you Domenic...

I have make also this Macro that print out alse the sheets.

Sub macro1()

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Sheets(1).Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveSheet.PrintOut copies:=1
Sheets(2).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Sheets(2).Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveSheet.PrintOut copies:=1
Sheets(3).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Sheets(3).Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveSheet.PrintOut copies:=1

Sheets(next number).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
Sheets(next number).Name, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
False
ActiveSheet.PrintOut copies:=1
[I go on with number untill 20, so when it rached the last sheet it's stopped]
Sheets(1).Select
End Sub

It's work perfectly on Excel2010 ...
but yours is better than my

The problem is that:
I need these two Macro work on 2003 but they didn't work...
Because in Excel2003 --->ExportAsFixedFormat Type:=xlTypePDF<--- this function doesn't exist... How can I save in pdf format with this version?And (after), what's the "new" VBA code for Excel2003 with the same function? thanks you

 

[Domenic]

See if this helps...

http://www.excelguru.ca/node/21

 

[finenovembre]

Hi, thanks you...
I downloaded PDFCreator and I try the code in your link:
Print a Single Worksheet to a PDF File:

Sub PrintToPDF_Early()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: Print to PDF file using PDFCreator
'   (Download from http://sourceforge.net/projects/pdfcreator/)
'   Designed for early bind, set reference to PDFCreator

    [COLOR="Red"]Dim pdfjob As PDFCreator.clsPDFCreator[/COLOR]
    Dim sPDFName As String
    Dim sPDFPath As String
    Dim bRestart As Boolean

    '/// Change the output file name here! ///
    sPDFName = "testPDF.pdf"
    sPDFPath = ActiveWorkbook.Path & Application.PathSeparator

    'Check if worksheet is empty and exit if so
    If IsEmpty(ActiveSheet.UsedRange) Then Exit Sub

    'Activate error handling and turn off screen updates
    On Error GoTo EarlyExit
    Application.ScreenUpdating = False

    Set pdfjob = New PDFCreator.clsPDFCreator

    'Check if PDFCreator is already running and attempt to kill the process if so
    Do
        bRestart = False
        Set pdfjob = New PDFCreator.clsPDFCreator
        If pdfjob.cStart("/NoProcessingAtStartup") = False Then
            'PDF Creator is already running.  Kill the existing process
            Shell "taskkill /f /im PDFCreator.exe", vbHide
            DoEvents
            Set pdfjob = Nothing
            bRestart = True
        End If
    Loop Until bRestart = False

    'Assign settings for PDF job
    With pdfjob
        .cOption("UseAutosave") = 1
        .cOption("UseAutosaveDirectory") = 1
        .cOption("AutosaveDirectory") = sPDFPath
        .cOption("AutosaveFilename") = sPDFName
        .cOption("AutosaveFormat") = 0    ' 0 = PDF
        .cClearCache
    End With

    'Delete the PDF if it already exists
    If Dir(sPDFPath & sPDFName) = sPDFName Then Kill (sPDFPath & sPDFName)

    'Print the document to PDF
    ActiveSheet.PrintOut copies:=1, ActivePrinter:="PDFCreator"

    'Wait until the print job has entered the print queue
    Do Until pdfjob.cCountOfPrintjobs = 1
        DoEvents
    Loop
    pdfjob.cPrinterStop = False

    'Wait until the file shows up before closing PDF Creator
    Do
        DoEvents
    Loop Until Dir(sPDFPath & sPDFName) = sPDFName

Cleanup:
    'Release objects and terminate PDFCreator
    Set pdfjob = Nothing
    Shell "taskkill /f /im PDFCreator.exe", vbHide
    On Error GoTo 0
    Application.ScreenUpdating = True
    Exit Sub

EarlyExit:
    'Inform user of error, and go to cleanup section
    MsgBox "There was an error encountered.  PDFCreator has" & vbCrLf & _
           "has been terminated.  Please try again.", _
           vbCritical + vbOKOnly, "Error"
    Resume Cleanup
End Sub

But i have an error in the red line. Why?
(I'm using Excel2003 with service pack 3 and with PDFCreator printer)
I'm sure that PDFCrator is right configure because this code work fine:

Sub Create_Pdf_PDFCreator()
'
' Create_Pdf_PDFCreator Macro
' Macro recorded 25/10/2011 by Matteo
'
' Keyboard Shortcut: Ctrl+j
'
    Sheets(1).Select
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="_PDFCreator"
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="_HP Photosmart B110 series"
    Sheets(2).Select
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="_PDFCreator"
ActiveSheet.PrintOut Copies:=1, ActivePrinter:="_HP Photosmart B110 series"

End Sub

I activated autosave PDFCreator but it save .pdf with file's name and not with his own sheet's name.
I have to change option of PDFCreator using VBA in Excel and set Sheet(number).Name for autosaving.
Thank you

 

[Domenic]

Did you set a reference (Tools > References) to PDFCreator?

 

[finenovembre]

Thank you but I have just resolved my problem... see you next time

 

[Domenic]

Glad that you've resolved your problem. And thanks for the feedback.