Batch converting Excel files to PDF

13 Oct 2016 1 mins JavaScript, MS Office, PowerShell, Windows, Windows Scripting Host

I have – or rather I’ve had – bunch of Excel files and I needed these in PDF. If I would be just five or so of these I would do it manually in Excel. But there was bit over 200 files. Doable but not fun.

So I started scripting. Some quick search on the internet brought some examples using Word in WSH. With that in my hands it was just a matter of firing Excel.Application ActiveX and going though the documentation gluing it together. I’ve spent few minutes looking for proper file type for SaveAs method, finding eventually the PDF (xlTypePDF) is 57.

Here’s the result.

var fso = new ActiveXObject('Scripting.FileSystemObject');
var filePath = fso.GetAbsolutePathName(WScript.Arguments(0));
var pdfPath = filePath.replace(/\.xlsx$/i, '.pdf');

var objExcel = null;
try {
	WScript.Echo(pdfPath);

	objExcel = new ActiveXObject('Excel.Application');
	objExcel.Visible = false;

	var objWorkbook = objExcel.Workbooks.Open(filePath);

	WScript.Echo('  saving');

	var xlTypePDF = 57;
	objWorkbook.SaveAs(pdfPath, xlTypePDF);
	objWorkbook.Close(false);

	WScript.Echo('  done');
}
finally {
	if (objExcel != null) {
		objExcel.Quit();
	}
}

Nothing special. Just call it cscript.exe xlsx2pdf.js <filename> (where xlsx2pdf.js is the script from above) and that’s it. Doing the batch with PowerShell was piece of cake.

gci -Recurse -Filter *.xlsx | %{ cscript.exe xlsx2pdf.js $_.FullName }

Minute or two later it finished. Enjoy (as much as I did).

Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.