vbScript to Save Excel file as PDF

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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.

Rich (BB code):
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.
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,364
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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:

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

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,364
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
It would basically be something like:
Rich (BB code):
xlWB.ActiveWindow.SelectedSheets.ExportAsFixedFormat 1, thisFileName & ".pdf", 1, True, False, False
replacing the 1s with the relevant values.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,934
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:

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

New Member
Joined
Sep 22, 2011
Messages
1
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,930
Messages
5,508,176
Members
408,669
Latest member
AgsikapAko

This Week's Hot Topics

Top