[VBA] - Need to add File Location and Print Page Setup in Extract PDF Sub

[rsolanki]

I found this working Sub which exports Worksheet in PDF, however how can I change the Location of Export / File and How can i make sure that Page setup is All Row and Column is printed in Landscape and fits into one page?

Sub Extract_PDF()

Dim wb As Workbook
Dim sh As Worksheet

Set wb = ThisWorkbook

For Each sh In wb.Worksheets

    sh.Select

    pdf_name = sh.Name & ".pdf"
    

    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=ActiveWorkbook.Path & pdf_name, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

Next

End Sub

 

[Domenic]

Try the following...

Sub Export_All_Worksheets_to_PDF()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destFolderName As String
    Dim saveAsFileName As String
    
    destFolderName = "c:\users\john\desktop\" 'change the path accordingly
    
    If Right$(destFolderName, 1) <> "\" Then
        destFolderName = destFolderName & "\"
    End If
    
    Set wb = ThisWorkbook
    
    For Each ws In wb.Worksheets
    
        saveAsFileName = destFolderName & ws.Name & ".pdf"
        
        formatWorksheet ws
        
        exportSheetToPDF saveAsFileName, ws
    
    Next
    
End Sub


Private Sub formatWorksheet(ByVal ws As Worksheet)

    Application.PrintCommunication = False
    
    With ws.PageSetup
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        .Orientation = xlLandscape
    End With
    
    Application.PrintCommunication = True
    
End Sub

Private Sub exportSheetToPDF(ByVal saveAsFileName As String, ByVal sh As Object)

        sh.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=saveAsFileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    
End Sub

Hope this helps!

 

[rsolanki]

Try the following...

Sub Export_All_Worksheets_to_PDF()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim destFolderName As String
    Dim saveAsFileName As String
   
    destFolderName = "c:\users\john\desktop\" 'change the path accordingly
   
    If Right$(destFolderName, 1) <> "\" Then
        destFolderName = destFolderName & "\"
    End If
   
    Set wb = ThisWorkbook
   
    For Each ws In wb.Worksheets
   
        saveAsFileName = destFolderName & ws.Name & ".pdf"
       
        formatWorksheet ws
       
        exportSheetToPDF saveAsFileName, ws
   
    Next
   
End Sub


Private Sub formatWorksheet(ByVal ws As Worksheet)

    Application.PrintCommunication = False
   
    With ws.PageSetup
        .FitToPagesTall = 1
        .FitToPagesWide = 1
        .Orientation = xlLandscape
    End With
   
    Application.PrintCommunication = True
   
End Sub

Private Sub exportSheetToPDF(ByVal saveAsFileName As String, ByVal sh As Object)

        sh.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=saveAsFileName, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
   
End Sub

Hope this helps!

Hey Domenic, Thanks its working!! Yay!

Just wanted to know is it possible to Add Header in each PDF like Organization Name, Address and Logo?

Look forward to hear from you

Thanks in Advance

 

[Domenic]

Have you considered using the header section of the worksheet to add the address and logo, whether manually or via code?

Ribbon >> View >> Workbook Views >> Page Layout >> Add header

Or, maybe, for more flexibility, simply amend you worksheets to include them?