How can export all part of columns to pdf file with VBA code?

Question

Please download the sample.xlsx from my dropbox.
https://www.dropbox.com/scl/fi/dm7okj2j5l2onlq2lmd6m/sample.xlsx?rlkey=obm03i1ysexnajay1zzv2jddy&st=tan1kkj1&dl=0

It contains simple data.

I want to export all columns in the sample.xlsx into sample.xlsx with VBA code:

Sub pdf()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim saveLocation As String
    templePath = "d:\sample.xlsx"
    Set wb = Workbooks.Open(templePath)
    Set ws = wb.Sheets(1)
    With ws.PageSetup
        .Orientation = xlLandscape
    End With
    saveLocation = "d:\sample.pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
    wb.Close SaveChanges:=False
    Set wb = Nothing
    Set ws = Nothing 
End Sub

The setting .Orientation = xlLandscape can't make all columns saved in sample.pdf.

How can export all part of columns to pdf file with VBA code?

Answer 1

Your script is almost complete; the only thing left is to reset the worksheet's print area settings.

Please check the print area in Page Break Preview mode.

Sub pdf()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim saveLocation As String
    Application.ScreenUpdating = False
    Dim templePath As String: templePath = "d:\sample.xlsx"
    Set wb = Workbooks.Open(templePath)
    Set ws = wb.Sheets(1)
    Application.PrintCommunication = False
    With ws.PageSetup
        .PrintArea = "" ' reset print area setting
        .FitToPagesWide = 1 ' fit all columns to a page
        .Orientation = xlLandscape
    End With
    Application.PrintCommunication = True
    saveLocation = "d:\sample.pdf"
    ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
    wb.Close SaveChanges:=False
    Set wb = Nothing
    Set ws = Nothing
    Application.ScreenUpdating = True
End Sub