Page 1 of 1

Printing all worksheets in multiple excel files without opening each file

Posted: Thu Apr 22, 2021 6:56 am
by mskaggs21
I am not experienced with VBScript but I was able to piece together something that mostly works for what I need. Each user has to install their own printers from the network. Sometimes they have a specific printer set up to direct print to, but I was wanting it to go to TMMWV Cloud Printing without permanently changing their default printer. This prevents sending a large print job to a printer that someone else might be using. From my limited testing, it seems to work correctly. Does everything look correct or should I change something to make it better?

*This is printing 5 different excel files contained within a single folder. Each excel file contains between 8 and 25 worksheets. The .vbs file is contained in the same folder as the excel files.
  1. strComputer = "."
  2. Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
  4. Set colPrinters =  objWMIService.ExecQuery _
  5.     ("Select * from Win32_Printer Where Default = TRUE")
  6. For Each objPrinter in colPrinters
  7.     strOldDefault = objPrinter.Name
  8.     strOldDefault = Replace(strOldDefault, "\", "\\")
  9. Next
  10. Set colPrinters =  objWMIService.ExecQuery _
  11.     ("Select * from Win32_Printer Where Name = '\\\\tmmwv-print\\TMMWV Cloud Printing'")
  12. For Each objPrinter in colPrinters
  13.     objPrinter.SetDefaultPrinter()
  14. Next
  16. Wscript.Sleep 2000
  18. Set shApp = CreateObject("shell.application")
  19. currentPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
  20. Set objFSO = CreateObject("Scripting.FileSystemObject")
  21. Set objFolder = objFSO.GetFolder(currentPath)
  22. Set colFiles = objFolder.Files
  23. For Each objFile in colFiles
  24.     If UCase(objFSO.GetExtensionName( = "XLSX" Then
  25.         Set oExcel = CreateObject("Excel.Application")
  26.         oExcel.Visible = False
  27.         oExcel.Workbooks.Open currentPath & "\" & objFile.Name
  28.         oExcel.Workbooks(1).Sheets.PrintOut
  29.         oExcel.Workbooks.Close
  30.         oExcel.Quit
  31.     End If
  32. Next
  34. Set colPrinters =  objWMIService.ExecQuery _
  35.     ("Select * from Win32_Printer Where Name = '" & strOldDefault & "'")
  36. For Each objPrinter in colPrinters
  37.     objPrinter.SetDefaultPrinter()
  38. Next
  40. Set objWMIService = Nothing
  41. Set colInstalledPrinters = Nothing
  42. Set shApp = Nothing
  43. Set objFSO = Nothing
  44. Set objFolder = Nothing
  45. Set colFiles = Nothing
  46. Set oExcel = Nothing

Re: Printing all worksheets in multiple excel files without opening each file

Posted: Thu Apr 22, 2021 3:26 pm
by jvierra
Since you are not a skilled scripter and since VBScript is obsolete I recommend using PowerShell as it will make the whole project much easier for anyone who is just starting to try and write a script.

Also consider that the whole project can be done entirely in Excel using the same VB methods. You can also more easily set the correct printer in Excel although it can be done in any language using the Excel object. To understand how to do that then post in eth Excel VBA developer forum on the Microsoft site.

Re: Printing all worksheets in multiple excel files without opening each file

Posted: Thu Apr 22, 2021 3:31 pm
by jvierra
Here is how to do this in Excel VBA with a good discussion of how the code works: ... excel.html

The VBA script is mostly usable as VBScript.