Results 1 to 7 of 7

Thread: VBScipt to print multiple excel files with multiple tabs within a folder

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    5

    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

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    5

    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.

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    5

    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.

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    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.

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    5

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    5

    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
  •  



Click Here to Expand Forum to Full Width