Printing all worksheets in multiple excel files without opening each file
Posted: Thu Apr 22, 2021 6:56 am
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.
*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.
- strComputer = "."
- Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
- Set colPrinters = objWMIService.ExecQuery _
- ("Select * from Win32_Printer Where Default = TRUE")
- For Each objPrinter in colPrinters
- strOldDefault = objPrinter.Name
- strOldDefault = Replace(strOldDefault, "\", "\\")
- Next
- Set colPrinters = objWMIService.ExecQuery _
- ("Select * from Win32_Printer Where Name = '\\\\tmmwv-print\\TMMWV Cloud Printing'")
- For Each objPrinter in colPrinters
- objPrinter.SetDefaultPrinter()
- Next
- Wscript.Sleep 2000
- Set shApp = CreateObject("shell.application")
- currentPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
- Set objFSO = CreateObject("Scripting.FileSystemObject")
- Set objFolder = objFSO.GetFolder(currentPath)
- Set colFiles = objFolder.Files
- For Each objFile in colFiles
- If UCase(objFSO.GetExtensionName(objFile.name)) = "XLSX" Then
- Set oExcel = CreateObject("Excel.Application")
- oExcel.Visible = False
- oExcel.Workbooks.Open currentPath & "\" & objFile.Name
- oExcel.Workbooks(1).Sheets.PrintOut
- oExcel.Workbooks.Close
- oExcel.Quit
- End If
- Next
- Set colPrinters = objWMIService.ExecQuery _
- ("Select * from Win32_Printer Where Name = '" & strOldDefault & "'")
- For Each objPrinter in colPrinters
- objPrinter.SetDefaultPrinter()
- Next
- Set objWMIService = Nothing
- Set colInstalledPrinters = Nothing
- Set shApp = Nothing
- Set objFSO = Nothing
- Set objFolder = Nothing
- Set colFiles = Nothing
- Set oExcel = Nothing