Pagesetup every sheet in Excel with vbscript

Question

I got a VBscript to convert all xls files in a folder (c:\folder) to pdf files. All columns are also set to fit one page, and with orientation landscape. The script works fine for Sheet1, but if the xls file has more then 1 sheets, it will only set the Pagesetup settings to the "active" sheet, and not to all.

How do I get my Pagesetup settings to apply to all sheets in all xls files in the folder?

Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("c:\folder")
For Each file In f.Files
set xlWB = xlObj.Workbooks.Open(file)
With xlWB.ActiveSheet.PageSetup
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With    
thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
xlWB.Sheets.Select
xlWB.ActiveSheet.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 0,,,0
xlWB.close False
Next
xlObj.quit

Tim Williams Tim Williams · Accepted Answer · 2016-08-30T23:22:41

It's not clear how you want to handle exporting the other sheets? As separate files, or all together in the same PDF?

Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("c:\folder")
For Each file In f.Files
    set xlWB = xlObj.Workbooks.Open(file)
    for each sht in xlWB.Worksheets
        With sht.PageSetup
            .Zoom = False
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With 
        'can't use the same filename for all sheets !
        thisFileName = Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
        sht.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 0,,,0
    next sht
    xlWB.close False
Next
xlObj.quit

Pagesetup every sheet in Excel with vbscript

2 Answers