Databases
StackOverflow2013
Postsdbo
POExcel Macro: iterate through workbooks and print individual sheets from each
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POExcel Macro: iterate through workbooks and print individual sheets from each
    text
    copied!<p>The steps I need to carry out are repetitive, but I'm not sure how to iterate through each workbook and then each sheet.</p> <p>My task is to:</p> <ol> <li>Look in a folder: (Source folder)</li> <li>Iterate through each workbook in that folder (Source file)</li> <li>Print each of the 4 worksheets (sheet name) in each workbook to a PostScript Printer (Printer Name/Path).</li> <li>Name the printed to file PS file = Source file+sheet name</li> <li>Final PS output files placed in final folder (destination folder)</li> <li>Original workbook closed and not saved.</li> </ol> <p>I searched for iteration VBA/Macro and have seen some ideas, but I'm unsure how the code looks when it's working through workbooks and worksheets.</p> <p>Also, the PS printer is done through Printing To File. Does this cause problems.</p> <p><strong>UPDATED WITH CODE I'VE TRIED SO FAR:</strong></p> <pre><code>Sub Make_PS_Files() Dim path2 As String, path3 As String path2 = "Drive:\Source folder\" path3 = " Drive:\Destination folder\" Workbooks.Open Filename:=path2 + "File_Name.XLS" Sheets("Specific_Sheet_Name1").Activate Application.ActivePrinter = "\\PRINTER NAME\LOCATION:" ActiveWindow.SelectedSheets.PrintOut copies:=1, PrintToFile:=True, Collate _ :=True, Prtofilename:=True ActiveSheet.PrintOut copies:=1, Prtofilename:=path3 + " Specific_Sheet_Name1.ps" Sheets("Specific_Sheet_Name2").Activate Application.ActivePrinter = "\\VS PRINTER NAME\LOCATION:" ActiveWindow.SelectedSheets.PrintOut copies:=1, PrintToFile:=True, Collate _ :=True, Prtofilename:=True ActiveSheet.PrintOut copies:=1, Prtofilename:=path3 + " Specific_Sheet_Name2.ps" Sheets("Specific_Sheet_Name3").Activate Application.ActivePrinter = "\\ PRINTER NAME\LOCATION:" ActiveWindow.SelectedSheets.PrintOut copies:=1, PrintToFile:=True, Collate _ :=True, Prtofilename:=True ActiveSheet.PrintOut copies:=1, Prtofilename:=path3 + " Specific_Sheet_Name3.ps" ActiveWorkbook.Close Sheets("Specific_Sheet_Name4").Activate Application.ActivePrinter = "\\ PRINTER NAME\LOCATION:" ActiveWindow.SelectedSheets.PrintOut copies:=1, PrintToFile:=True, Collate _ :=True, Prtofilename:=True ActiveSheet.PrintOut copies:=1, Prtofilename:=path3 + " Specific_Sheet_Name4.ps" ActiveWorkbook.Close End Sub </code></pre> <p>Apologies for not posting this last night when I posted. It seems very long winded for what it is doing. I thought it could be polished a bit more so it is more universal and can be pointed at any workbook and any number of sheets.</p> <p>Not all the sheets have the Specific_Sheet_Name, so I would like to iterate through without reference to the name.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload