VBS Command to tell excel to print entire workbook

Question

I have a VBS that looks at a set of files and prints them to PDF. The issue I am running into now is that it does not print the entire workbook. If there a string I can add to this code below?

 Set fso = CreateObject("Scripting.FileSystemObject")
 currentdir = fso.GetAbsolutePathName(".")

 Set xmldom = CreateObject("MSXML.DOMDocument")
 xmldom.Load(currentdir & "\info.xml")

progid = xmldom.SelectSingleNode("/xml/progid").text

 set obj = CreateObject(progid)

 printername = obj.GetPrinterName

 runonce = obj.GetSettingsFileName(true)

 Set fldr = fso.GetFolder(currentdir & "\in")
 cnt = 0
 For Each f In fldr.files
cnt = cnt + 1
output = currentdir & "\out\" & Replace(f.name, ".xls", "") & ".pdf"

obj.Init
obj.SetValue "Output", output
obj.SetValue "ShowSettings", "never"
obj.SetValue "ShowPDF", "no"
obj.SetValue "ShowProgress", "no"
obj.SetValue "ShowProgressFinished", "no"
obj.SetValue "SuppressErrors", "yes"
obj.SetValue "ConfirmOverwrite", "no"

obj.WriteSettings True

printfile = currentdir & "\in\" & f.name
cmd = """" & currentdir & "\printto.exe"" """ & printfile & """ """ &      printername & """"

Set WshShell = WScript.CreateObject("WScript.Shell")
ret = WshShell.Run(cmd, 1, true)

While fso.fileexists(runonce)
    wscript.sleep 100
Wend
Next

set obj = Nothing

Wscript.Echo cnt & " documents were printed."

Found some articles that may be helpful. http://excel.tips.net/T002001_Printing_an_Entire_Workbook_by_Default.html http://stackoverflow.com/questions/5693189/excel-vb-script-to-print-all-workbooks-sheets @John E — Randy Schuman, Aug 25 '16 at 23:44

score 0 · Answer 1 · answered Aug 29 '16 at 02:22

I believe printto.exe is a 3rd party utility which is available from http://www.reasoft.com/products/pdfprinter/help/using/se/command.html

You may need to contact the software vendor to verify if what you want is supported by the utility.

Anyway, instead of using the 3rd party utility, you may want to follow the suggestion pointed out by @randy-schuman above, i.e. to trigger the printing from Excel application. You can do something like below:

Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False

For Each f In fldr.Files
  If Instr(f.Type,"Excel") Then
    Set book = objExcel.Workbooks.Open(f.Path,0,1)
    book.PrintOut ,,,,printername  '' https://msdn.microsoft.com/en-us/library/office/ff196840.aspx
    book.Close 0
  End If
Next

objExcel.Quit
Set objExcel = Nothing

VBS Command to tell excel to print entire workbook

1 Answers1