-
Oct 22nd, 2020, 10:55 AM
#1
Thread Starter
New Member
VBScipt to print multiple excel files with multiple tabs within a folder
I currently have a working file to print a single excel file with multiple tabs. I need to modify it to print multiple excel files within the single folder. My folder contains 5 excel files with .xlsx extension and a few other files that I do not want to print. The folder might end up renamed or moved. I need it to continue working if that happens. Thank you for any help you can provide.
set shApp = CreateObject("shell.application")
currentPath = CreateObject("Scripting.FileSystemObject").GetAbsolutePathName(".")
set shFolder = shApp.NameSpace( currentPath )
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False
oExcel.Workbooks.Open currentPath & " \ " & "AHT CDE Fluid Tracking.xlsx"
oExcel.Workbooks(1).Sheets.PrintOut
oExcel.Workbooks.Close
oExcel.Quit
Set shApp = Nothing
Set shFolder = Nothing
Set oExcel = Nothing
-
Oct 22nd, 2020, 12:16 PM
#2
Re: VBScipt to print multiple excel files with multiple tabs within a folder
Can we assume that your 5 workbooks have fixed names? If yes, then you can just add some lines to open each of them and print them out.
oExcel.Workbooks.Open currentPath & " \ " & "Additional Workbook 2.xlsx"
oExcel.Workbooks(1).Sheets.PrintOut
oExcel.Workbooks.Close
-
Oct 22nd, 2020, 12:42 PM
#3
Thread Starter
New Member
Re: VBScript to print multiple excel files with multiple tabs within a folder
Thank you jdc2000 for your suggestion. I have updated my file to this as a temporary fix. A more permanent fix will be needed based on the fact that the folder is located on a shared network drive. The folder can be moved, the files can be renamed, the number of files could increase or decrease. I would like to do something like I did with 'currentPath' but have it loop and print all "*.xlsx" files. The only thing for certain is the .vbs file will stay in that folder. Maybe I can figure it out myself tomorrow after some sleep, but I know some very knowledgeable people are on here and I would appreciate any help offered
Last edited by GUEdiver246; Oct 22nd, 2020 at 01:43 PM.
-
Oct 22nd, 2020, 01:41 PM
#4
Thread Starter
New Member
Re: VBScript to print multiple excel files with multiple tabs within a folder
A new twist. I just learned I also need to either set a specific network printer or give the option of printer. Some people have windows managing their default printer and some people do not. I need these printed in color and some people are defaulted for b&w.
Last edited by GUEdiver246; Oct 22nd, 2020 at 01:44 PM.
-
Oct 22nd, 2020, 02:19 PM
#5
Re: VBScipt to print multiple excel files with multiple tabs within a folder
You can have your script get the path of the current directory (assuming that your script is in the same directory as the files to print), then get the names of the files to print (all .xlsx files), and print them.
Links:
https://stackoverflow.com/questions/...ry-in-vbscript
https://stackoverflow.com/questions/...-them-to-a-csv
For printing in color, you would need to know what printers are available to the current computer and user and that have color capability before you can either select one or have the user select one to print to.
-
Oct 28th, 2020, 11:56 AM
#6
Thread Starter
New Member
Re: VBScipt to print multiple excel files with multiple tabs within a folder
It isn't the prettiest coding, but it works. This one prints to the default printer.
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 shApp = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set colFiles = Nothing
Set oExcel = Nothing
-
Oct 28th, 2020, 11:57 AM
#7
Thread Starter
New Member
Re: VBScipt to print multiple excel files with multiple tabs within a folder
This one prints to a certain printer by setting it as default during the running of the script.
strComputer = "."
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\" & strComputer & "\root\cimv2")
Set colInstalledPrinters = objWMIService.ExecQuery ("Select * from Win32_Printer Where Name = 'TMMWV Cloud Printing'")
For Each objPrinter in colInstalledPrinters
objPrinter.SetDefaultPrinter()
Next
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 objWMIService = Nothing
Set colInstalledPrinters = Nothing
Set shApp = Nothing
Set objFSO = Nothing
Set objFolder = Nothing
Set colFiles = Nothing
Set oExcel = Nothing
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|