Search
« Do the mons go on the left or the right? | Main | Converting ZIP files to 7Z archive file using VBScript and 7-Zip »
Thursday
Dec202012

VBScript to Convert Microsoft Office Files (Word, Excel, PowerPoint) to PDF

I spend a lot of my time working with Word documents and Excel spreadsheets - it's in the nature of what I do. I frequently found that I need to create PDF copies of these files (to send out to clients) and eventually I grew tired of having to open the files one by one and select to save each as a PDF. As a result I created a bit of VBScript to automatically convert files for me (in Windows).

A shortcut to the script should be placed in your send-to folder to allow you to send files directly to the script. For each file it is passed (and you can pass different types of files at once) the script tries to determine what application to open it with and then opens that file in that application. Once open it saves the file as a PDF (in the same folder as the original file) and then closes the application.

There are a couple of settings that you might wish to amend to suit your own purposes.

  • If the constant SUMMARY_DISPLAY is set to TRUE it will display a window that summarises the files that have been converted. If set to FALSE no confirmation is displayed.
  • The variables g_strFileTypesWord, g_strFileTypesExcel and g_strFileTypesPowerPoint contain lists of file extensions delimited by pipes "|". These are for Word, Excel and PowerPoint files respectively. You can extend the lists to include other file types (from those presented here) - templates, TXT, RTF, CSV, etc.

There's nothing in it for handling errors, but obviously feel free to extend this script to meet your own needs. There are a number of constants and settings that I've set out to make it easier for you to tweak or amend. Maybe you want to save the files as XPS files or you want to change the final location of all your output files - please customise away :)

I use this script regularly with Microsoft Office 2007 without any issues (so far). Hopefully it's compatible with Office 2010 (and beyond), so if you do try it with another version of Office why not leave a comment below to let people know if it works or needs a particular amendment to get it to work.

Option Explicit

'---------------------
' CONFIGURATION START
'---------------------
'Display a summary in a message box when the conversions are complete
Const SUMMARY_DISPLAY = TRUE
Const SUMMARY_TITLE = "Conversion Complete"

'File extensions for PDFs
Const PDF_Extension = "pdf"

'Results for CheckFile Function
Const CHECKFILE_OK = 0
Const CHECKFILE_FileDoesNotExist = 1
Const CHECKFILE_NotMSOFile = 2

'Settings to produce PDFs from the applications
Const EXCEL_PDF = 0
Const EXCEL_QualityStandard = 0
Const WORD_PDF = 17
Const POWERPOINT_PDF = 32

'File types returned from OfficeFileType function
Const FILE_TYPE_NotOffice = 0
Const FILE_TYPE_Word = 1
Const FILE_TYPE_Excel = 2
Const FILE_TYPE_PowerPoint = 3

'Valid file type lists
Const FILE_TYPE_DELIMITER = "|"
Dim g_strFileTypesWord
g_strFileTypesWord="|DOC|DOCX|"
Dim g_strFileTypesExcel
g_strFileTypesExcel="|XLS|XLSX|"
Dim g_strFileTypesPowerPoint
g_strFileTypesPowerPoint="|PPT|PPTX|"
'----------------------
' CONFIGURATION FINISH
'----------------------


'Call the main routine
Main
'--------------------
' MAIN ROUTINE START
'--------------------
Sub Main()
	Dim colArgs, intCounter, objFSO, strFilePath
	
	'Get the command line arguments for the script
	' - Each chould be a file to be processed
	Set colArgs = Wscript.Arguments
	If colArgs.Count > 0 Then
		For intCounter = 0 to colArgs.Count - 1
			strFilePath = Wscript.Arguments(intCounter)
			
			'Check we have a valid file and process it
			Select Case CheckFile(strFilePath)
				Case CHECKFILE_OK
					Select Case OfficeFileType(strFilePath)
						Case FILE_TYPE_Word
							SaveWordAsPDF strFilePath
							
						Case FILE_TYPE_Excel
							SaveExcelAsPDF strFilePath
							
						Case FILE_TYPE_PowerPoint
							SavePowerPointAsPDF strFilePath
					End Select
					
				Case CHECKFILE_FileDoesNotExist
					MsgBox """" & strFilePath & """", vbCritical, "File " & intCounter & " does not exist"
					WScript.Quit
					
				Case CHECKFILE_NotMSOFile
					MsgBox """" & strFilePath & """", vbCritical, "File " & intCounter & " is not a valid file type"
					WScript.Quit
			End Select
		Next
	Else
		'If there's not even one argument/file to process then exit
		Msgbox "Please pass a file to this script", 48,"No File Provided"
		WScript.Quit
	End If
	
	
	'Display an optional summary message
	If SUMMARY_DISPLAY then
		If colArgs.Count > 1 then
			MsgBox CStr(colArgs.Count) & " files converted.", vbInformation, SUMMARY_TITLE
		Else
			MsgBox "1 file converted.", vbInformation, SUMMARY_TITLE
		End If
	End If
End Sub
'---------------------
' MAIN ROUTINE FINISH
'---------------------


'--------------------
' SUB-ROUTINES START
'--------------------
Sub SaveExcelAsPDF(p_strFilePath)
	'Save Excel file as a PDF
	
	'Initialise
	Dim objExcel, objWorkbook
	Set objExcel = CreateObject("Excel.Application")
	
	'Open the file
	Set objWorkbook = objExcel.Workbooks.Open(p_strFilePath)
	
	'Save the PDF
	objWorkbook.ExportAsFixedFormat EXCEL_PDF, PathOfPDF(p_strFilePath), EXCEL_QualityStandard, TRUE, FALSE, , , FALSE
	
	'Close the file and exit the application
	objWorkbook.Close FALSE
	objExcel.Quit
End Sub


Sub SaveWordAsPDF(p_strFilePath)
	'Save Word file as a PDF
	
	'Initialise
	Dim objWord, objDocument
	Set objWord = CreateObject("Word.Application")
	
	'Open the file
	Set objDocument = objWord.Documents.Open(p_strFilePath)
	
	'Save the PDF
	objDocument.SaveAs PathOfPDF(p_strFilePath), WORD_PDF

	'Close the file and exit the application
	objDocument.Close FALSE
	objWord.Quit
End Sub


Sub SavePowerPointAsPDF(p_strFilePath)
	'Save PowerPoint file as a PDF (slides only)
	
	'Initialise
	Dim objPowerPoint, objSlideDeck
	Set objPowerPoint = CreateObject("PowerPoint.Application")
	
	'PowerPoint errors if it isn't visible :-(
	'objPowerPoint.Visible = TRUE
	
	'Open the file
	Set objSlideDeck = objPowerPoint.Presentations.Open(p_strFilePath, , , FALSE)
	
	'Save the PDF
	objSlideDeck.SaveAs PathOfPDF(p_strFilePath), POWERPOINT_PDF, True

	'Close the file and exit the application
	objSlideDeck.Close
	objPowerPoint.Quit
End Sub
'---------------------
' SUB-ROUTINES FINISH
'---------------------


'-----------------
' FUNCTIONS START
'-----------------
Function CheckFile(p_strFilePath)
	'Check file exists and is an office file (Excel, Word, PowerPoint)
	
	'Initialise
	Dim objFSO
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	
	'Check the file exists and is an office file
	If IsOfficeFile(p_strFilePath) then
		If objFSO.FileExists(p_strFilePath) then
			CheckFile = CHECKFILE_OK
		Else
			CheckFile = CHECKFILE_FileDoesNotExist
		End If
	Else
		CheckFile = CHECKFILE_NotMSOFile
	End If
End Function


Function OfficeFileType(p_strFilePath)
	'Returns the type of office file, based upon file extension
	
	OfficeFileType = FILE_TYPE_NotOffice
	
	If IsWordFile(p_strFilePath) then
		OfficeFileType = FILE_TYPE_Word
	End If
	
	If IsExcelFile(p_strFilePath) then
		OfficeFileType = FILE_TYPE_Excel
	End If
	
	If IsPowerPointFile(p_strFilePath) then
		OfficeFileType = FILE_TYPE_PowerPoint
	End If
End Function

Function IsOfficeFile(p_strFilePath)
	'Returns true if a file is an office file (Excel, Word, PowerPoint)
	
	IsOfficeFile = IsWordFile(p_strFilePath) OR IsExcelFile(p_strFilePath) OR IsPowerPointFile(p_strFilePath)
End Function


Function IsWordFile(p_strFilePath)
	'Returns true if a file is a Word file
	
	IsWordFile = IsInList(GetFileExtension(p_strFilePath), g_strFileTypesWord)
End Function


Function IsExcelFile(p_strFilePath)
'Returns true if a file is an Excel file

	IsExcelFile = IsInList(GetFileExtension(p_strFilePath), g_strFileTypesExcel)
End Function


Function IsPowerPointFile(p_strFilePath)
'Returns true if a file is a PowerPoint file

	IsPowerPointFile = IsInList(GetFileExtension(p_strFilePath), g_strFileTypesPowerPoint)
End Function


Function IsInList(p_strSearchFor, p_strSearchIn)
	'Search a delimited list for a text string and return true if it's found
	
	Dim intResult
	
	intResult = InStr(1, p_strSearchIn, FILE_TYPE_DELIMITER & p_strSearchFor & FILE_TYPE_DELIMITER, vbTextCompare)
	
	If IsNull(intResult) then
		IsInList = FALSE
	Else
		If intResult = 0 then
			IsInList = FALSE
		Else
			IsInList = TRUE
		End If
	End If
End Function


Function GetFileExtension(p_strFilePath)
	'Return the file extension from a file path
	
	Dim objFSO
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	GetFileExtension = objFSO.GetExtensionName(p_strFilePath)
End Function


Function PathOfPDF(p_strOriginalFilePath)
	'Return the path for the PDF file
	'The path will be the same as the original file but with a different file extension
	
	Dim objFSO
	
	'Initialise
	Set objFSO = CreateObject("Scripting.FileSystemObject")
	
		'Build the file path
	'Set the directory
	PathOfPDF = objFSO.GetParentFolderName(p_strOriginalFilePath) & "\"
	
	'Add the original file name without the extension
	PathOfPDF = PathOfPDF & Left(objFSO.GetFileName(p_strOriginalFilePath), Len(objFSO.GetFileName(p_strOriginalFilePath)) - Len(objFSO.GetExtensionName(p_strOriginalFilePath)))
	
	'Finally add the the new file extension
	PathOfPDF = PathOfPDF & PDF_Extension
End Function
'------------------
' FUNCTIONS FINISH
'------------------

PrintView Printer Friendly Version

EmailEmail Article to Friend

Reader Comments (32)

This is a great script. Thank you.
It works great when I execute it directly.

However, I have it set as a Task in Server 2008 and it does not run.
I believe this is due to 2008 no longer supporting interactive sessions in batch mode.
When the script runs it displays a 'Publishing' progress bar for each conversion. Is there a way in the script to disable this?

Thank you
Tom

August 27, 2013 | Unregistered CommenterTom

Thanks a lot for this great script! It solved my problem to convert a bulk (approx. 500) of Excel and Word files to PDF. I just had to build in some recursion to find the way through a folder tree (which was a nice execise, compared to the boring work to do all this manually).
Have a nice day!
Sincerely,
Michael Schmid

October 27, 2013 | Unregistered CommenterMichael Schmid

It's working with Office 2010. That was exactly what I was looking for! Thanks a lot!

December 6, 2013 | Unregistered CommenterLPCormier

This is very helpful - well done!

January 12, 2014 | Unregistered CommenterJosh

Tom.

Sorry for the delay in responding ... apparently the CMS I use has only been informing me of "some" of the comments people have left on posts :(

I've only seen the publishing progress bar appear for PowerPoint, but it is part of PowerPoint's save process and there doesn't appear to be a scripting option to disable it.

It might be worth trying to schedule a PSEXEC based task and use PSEXEC with the interactive option (-i) to run the script. I don't know for sure it will work (i.e. i haven't tested it), but definitely worth a shot.

You can get more info on PSEXEC (a Microsoft tool) here - http://technet.microsoft.com/en-gb/sysinternals/bb897553.aspx

March 2, 2014 | Registered CommenterStephen Millard

Michael.

Glad you found it useful. If you want to share what you did with people I'd be happy to upload a copy onto the site or add a link to anything you want to post on it.

Failing that I think I've got a script somewhere that allows you to iterate through a folder structure and run a script on each folder/file. It'll probably take some tidying up though ... a job for another day.

March 2, 2014 | Registered CommenterStephen Millard

Thank you very much for this truly amazing and powerful script.

MUCH obliged.

March 24, 2014 | Unregistered CommenterArtem S. Tashkinov

This is exactly what I needed! I am new to using vbscript and and .hta, but am putting it to work regularly now. I need to know exactly how to "pass the file" to the script? Is it something I can add to the existing .vbs file?

March 27, 2014 | Unregistered CommenterDC

DC.

To pass the file to the script, drag and drop it onto the VBS file. What I actually have is a shortcut to the VBS file in my send-to menu. I then simply select the files I want to convert, and direct them to the script via the send-to entry.

Regards,

Stephen.

May 1, 2014 | Registered CommenterStephen Millard

I ran this script from command prompt in windows server 2008 R2 64 bit (having office 2010) and it works fine for Excel & Word. However for Powerpoint it fails in objPowerPoint.Presentations.Open.

Its surprising since I do have access to the Powerpoint file and can open it manually.

It works fine however in windows 7 64 bit with office 2010

November 8, 2014 | Unregistered CommenterBubai

This is great - perfect for what I need! Thanks so much for sharing this!

November 28, 2014 | Unregistered CommenterSN

Works with Office 2013! Great Script, thanks a lot!

Regards Boris

December 23, 2014 | Unregistered CommenterBoris

Hi Bubai. Do you have any details of what the error is?

February 24, 2015 | Registered CommenterStephen Millard

Hi
Im still a beginner in this stuff. Hope u dont mind if i throw some stupid questions.

Firstly, If i want to use this script with .bat file in order to schedule it. How can i pass a file to this script?

Also i tried the drag and drop method, it converted perfectly. However when i tried to open the file, error message appears which tell me that file.pdf not supported or damage.

Thanks and Regards
MA

May 25, 2015 | Unregistered CommenterMA

MA

First running the script. If the VBS script is called "convert.vbs" and is in "c:\scripts" and you want to convert two documents "doc1.docx" and "doc2.docx" both in "c:\docs", I'd write something like this in the BAT file.


@echo off
cd /d c:\scripts
cscript convert.vbs "c:\docs\doc1.docx" "c:\docs\doc2.docx"

I think that would work. Knowing about CSCRIPT is the key (do some background reading on Windows Script Host and CSCRIPT for more information). If you were passing files into the BAT file, then you would just substitute the BAT parameters for the doc entries ... but the double quotes will be important if you have any spaces in the file paths/names.

With regards to the corrupted PDF, what happens if you open the source file in your office application, save as PDF and then try to open that? The script is just automating the office apps to do exactly that without any interaction from you. This means that any corruption occurring would most likely be introduced by the office app in its save process. Also sometimes there can be glitches that occur in anything.

There are also lots of things you can consider and try in determining what is going on...
Can this issue be reliably reproduced?
Does it always happen with any file or just one?
Does the same thing happen on another/someone else's computer (maybe running a different version of office apps or operating system?
What happens if you move it to another directory or use the source file to an older file format?
What happens if you use a different PDF reader?
etc.

If you can try some of these it might give you some clues as to what is going on. I'm not saying you should try all of these, but with a bit of experimentation you might get some clearer insight into the cause of the issue.

Regards,

Stephen.

May 30, 2015 | Registered CommenterStephen Millard

Thanks, works pretty good on 2013 version.

June 11, 2015 | Unregistered CommenterVikram

Fabulous bit of code. Thank you.
This is such a boon for getting round the hurdles I'm experiencing with PowerPoint VBA.

August 6, 2015 | Unregistered CommenterJax Killington

Thanks for the code. It is very useful. To turn auto macro off, we can add the following after line 130

'Disable any potential macros of the word document.
objWord.WordBasic.DisableAutoMacros

July 26, 2016 | Unregistered Commenterkenlo

This script works like a charm, and is especially great for powerpoint!

Two questions:

1. Is it possible to adjust the settings for the Excel converstion? What im looking for here is the scales and specificly I want to use the "adjust all columns to one page" function available to me when I print (or convert through printer).

2. Is it possibly to adjust the script to enable .msg converstion?

Thnx for a great script!

August 9, 2016 | Unregistered CommenterTT

I'm new to using VBScript. I copied and pasted the code into notepad and saved as vbs file. When I dragged and dropped a .docx file onto the vbs file, nothing happened. I did not see any new pdf file in the current folder or any message box prompt. I also tried setting the send-to short cut but that also doesn't seem to do anything different. Any idea on where I should start troubleshooting or how to check whether the script executed?

August 22, 2016 | Unregistered CommenterHC

Hi TT.

For point 1 I think what you are after is the fit to page option. Try incorporating the details from that post and see if it gives you what you're after.


In regards to point 2, if MSG files are set to open for you in Outlook by default then you should be able to add some VBScript to open the MSG file, run something along the lines of this Save Outlook mail as a PDF script and then close the MSG file.

Regards,

Stephen.

August 23, 2016 | Registered CommenterStephen Millard

HC.

I'd start with seeing if you have any issues with other VBScripts. Perhaps a simple "Hello World" type script would be your best starting point.

After that is confirmed as working you would need to work through the script here.

The first thing is to check if the script gives you an error message if you run it without receiving a file. Likewise it should give an error if passed say an image file.

IF they work you would need to step through the code verifying actions and variables as with any other scripting/coding endeavour. The scope is far too large to detail in its entirety in s quick comment.

Hope that helps get you started.

Regards,

Stephen.

August 23, 2016 | Registered CommenterStephen Millard

Got it working with your help! Thnx!

I do however have 1 more issue. As I'm converting Word files, I notice that when the documents have track changes, there is a pop-up message asking me if I want to save the track changes.

From what I gather, this is because word auto warns when a mark-uped document is printed, saved, or closed. After some googling i found that this can be disabled whilst running a macro with: Options.WarnBeforeSavingPrintingSendingMarkup = FalseĀ 
However, using your script, no macro is run.


Is there a way to (only whilst using your VBscript - and not otherwise) remove the confirmation box when converting track changes documents?

October 24, 2016 | Unregistered CommenterTT

Any idea about how to do this in W2008; Someone said:"I believe this is due to 2008 no longer supporting interactive sessions in batch mode", that seems to be my case

November 29, 2016 | Unregistered CommenterAngel

invalid output procedure error occur. please assist.

May 13, 2017 | Unregistered Commentervelu

How about Alt+F11 to disable 'Save As: PDF'

June 7, 2017 | Unregistered Commenterchitchatid

Great work as im reading and now working to become a system admin i need to expand my scripting and progamming from zilch to noob. I was trying to follow your work and i could find how to change the save from pdf to txt. Im trying to batch a docx or pdf to simple text. Of course it has only text no pics i can make single files but is too slow. I hope im making sense.

July 19, 2017 | Unregistered CommenterArt

Thanks, Stephen! Your script saves me hours.

I found an edge case (for me). I would prefer to "print to pdf" rather than "export to pdf" because I often have "ink" on my PowerPoint slides and Excel WorkBooks. That is, I write on them with a stylus. When export (i.e., the .SaveAs method), the "ink" doesn't export.

Do you have any hints? I tried replacing

objSlideDeck.SaveAs PathOfPDF(p_strFilePath), POWERPOINT_PDF, True

with

objSlideDeck.PrintOut PrintToFile:="TestPrnt"

But I get "Error: Expected statement".

Is there a good reference for printing to pdf in VB Script? Thanks!

January 24, 2018 | Unregistered CommenterRichard

thank you for this post. This script work with 2013 office and give aa great result.thank you for this post. This script work with 2013 Microsoft office and give a great result.

January 26, 2018 | Unregistered CommenterOmar

Angel.

Maybe this post on scheduled tasks will help. I think you're probably hitting the Session 0 Isolation constraints.

March 28, 2018 | Registered CommenterStephen Millard

TT.

I think you could add the following to every time a Word document is opened:


ActiveDocument.AcceptAllRevisions
ActiveDocument.TrackRevisions = False

Now, this assumes that you want to export the PDF with all changes approved. If you wanted to do the opposite well I'm sure you can figure out how to do that. The file gets closed without saving so I don't think that would throw up any additional issues.

March 28, 2018 | Registered CommenterStephen Millard

Art.

PDF to TXT ... you'll need a non-Microsoft Office solution for that.

DOC* to TXT ... try recording a macro of you saving a document as a TXT file in Word and then take a look at the VBA it produces. You should then be able to compare it to the SaveAs code in the above and see where the difference lies.

March 28, 2018 | Registered CommenterStephen Millard

PostPost a New Comment

Enter your information below to add a new comment.
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>