Using VBScript which saves Excel as PDF how can I remove blank space at top and bottom in resulting PDF?

Viewed 178

I am creating a VBScript that will run a loop and get all XLSX files in a specified folder then save them as PDF. That part I got working. The issue I am having is the resulting PDF files have blank/white space at top and bottom. I am just testing with a small XLSX file with 2 columns and 10 rows.

Question: How can I eliminate the blank space at top and bottom of the PDF file that gets saved by the VBScript?


'Set Objects and Variables
Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\myserver\mypath\vbstest")

'Run a loop
For Each file In f.Files
'IF statement to look for XLSX files only
If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
    set xlWB = xlObj.Workbooks.Open(file)
    thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
    xlWB.Sheets.Select
    xlWB.ActiveSheet.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 0,,,0
    xlWB.close False
    counter = counter + 1
    'WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
End IF

'Next loop
Next

'Quit Excel
xlObj.Quit
Set xlObj = Nothing
Set xlWB = Nothing

Note: I also tried the ExportAsFixedFormat with the following True/False (1/0) switches which didn't make a difference:

        xlWB.ActiveSheet.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 1, 1,,0

The XLSX files I used as a test look like this:

enter image description here

Once the VBScript runs and creates a PDF then the result looks like below with large blank space at the top and bottom of the result:

enter image description here

2 Answers

I found an answer to my own question after searching further. Adjusted my script by adding a section to change margins via PageSetup

    'Set Page Margins to eliminate blank/white spaces
With xlWB.ActiveSheet.PageSetup
     .TopMargin = 0.25
     .LeftMargin = 0.25
     .RightMargin = 0.25
     .BottomMargin = 0.25
End With

Here is the full script after adding that section

On Error Resume Next

'Set Objects and Variables
 Set xlObj = CreateObject("Excel.Application")
 Set fso = CreateObject("Scripting.FileSystemObject")
 Set f = fso.GetFolder("\\myserver\mypath\vbstest")

 'Run a loop
 For Each file In f.Files

'IF statement to look for XLSX files only
If LCase(fso.GetExtensionName(file.Name)) = "xlsx" Then
set xlWB = xlObj.Workbooks.Open(file)
thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
xlWB.Sheets.Select

'Set Page Margins to eliminate blank/white spaces
 With xlWB.ActiveSheet.PageSetup
     .TopMargin = 0.25
     .LeftMargin = 0.25
     .RightMargin = 0.25
     .BottomMargin = 0.25
End With

'Save as PDF
'ExportASFixedFormat options info here: https://powerspreadsheets.com/save-excel-file-pdf-vba/
xlWB.ActiveSheet.ExportAsFixedFormat 0, thisFileName & ".pdf", 0, 1, 0,,,0
xlWB.close False
counter = counter + 1

'For Testing Only - Echo
'WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
End IF

'Next loop
Next

'Quit Excel
 xlObj.Quit
 Set xlObj = Nothing
 Set xlWB = Nothing

 'Error Checking and any steps associated with post error

 'Quit Excel
  xlObj.Quit
  Set xlObj = Nothing
  Set xlWB = Nothing

End result is a PDF that looks like this. The extra space at the top and left is now gone. Space on the Right and Bottom is just the empty section of Page 1 because there is no content there in Excel to begin with

enter image description here

Related