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:
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:


