vbScript to Save Excel file as PDF

[schielrn]

Ok this has something to do with Excel, so I thought it was applicable to post here. I am trying to create a vbScript that will take all Excel files in a folder and save them as a pdf as well.

The commented line is not working in the script and I cannot for the life of me figure out the syntax to get it to work. I know thisFileName is correct because of the message box. The script will just not run if I uncomment the line in red, but that is the exact syntax in Excel?

I know I could do this through Excel, but was just hoping to have a script that could easily be kicked off.

Just thought I'd check if anyone here had an idea before I went to the all Excel approach.

Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("M:\Macros and Stuff for GL\New Folder")
For Each file In f.Files
set xlWB = xlObj.Workbooks.Open(file)
thisFileName =Left(xlWB.FullName , InStrRev(xlWB.FullName , ".") - 1)
xlWB.Sheets.Select
msgbox(thisFileName)
'xlWB.ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= thisFileName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
xlWB.close False
counter = counter + 1
WScript.Echo "File " & counter & " of " & f.Files.count & " Done"
Next
xlObj.quit

Thanks for any help anyone can give.

 

[RoryA]

You can't have a reference set to Excel in VBScript, so you need to use the literal values of the Excel constants - e.g. replace xlTypePDF with its actual value. You also can't use named arguments, so you would need to just specify the values (I'm not in front of 2007 so I don't know what they are offhand).

 

[schielrn]

I thought I might have been closer with this, but still didn't work. I didn't like the activesheet that was being used before:

xlObj.activewindow.selectedsheets.ExportAsFixedFormat Type:=xlTypePDF, Filename:= thisFileName & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

 

[schielrn]

You can't have a reference set to Excel in VBScript, so you need to use the literal values of the Excel constants - e.g. replace xlTypePDF with its actual value. You also can't use named arguments, so you would need to just specify the values (I'm not in front of 2007 so I don't know what they are offhand).

Ok thanks I will look into that and see what I can do.

 

[RoryA]

It would basically be something like:

xlWB.ActiveWindow.SelectedSheets.ExportAsFixedFormat 1, thisFileName & ".pdf", 1, True, False, False

replacing the 1s with the relevant values.

 

[schielrn]

I was hoping you might see this Rory, I have it fixed now, didn't know about the Excel constants, so here is the script then:

Set xlObj = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("M:\Macros and Stuff for GL\New Folder")
For Each file In f.Files
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"
Next
xlObj.quit

 

[Azrinsani]

Hi!
I just tried this macro and it works fine. Thanks!

There's just 1 tiny thing. How do you get rid of the "Publishing" Progress bar which pops out when saving to PDF?

Thanks

 

[Jerry V]

Script works great. Would really like to get rid of progress bar.

 

[smoothdurban]

Anyone have any success exporting to image type format such as .png or .jpeg?

Thanks