PDA

View Full Version : [SOLVED] Print two different worksheets from two files within one folder



oam
05-13-2014, 06:05 PM
I have two reports that are exported from a database and saved into one folder as Excel .xls files. Contained within the two files are sheets that need (one sheet from each file) to be printed; one of the sheets is named “Total Statement” and the other sheet is named “Total Pieces Statement”. Due to the nature of the database, the names of the exported excel files come out as a random string of numbers and are unpredictable so I have been unable to use the standard print macros. In addition, the files come out in random order where the first file in the folder could be the “Total Statement” and the second could be the “Total Piece Statement” or vice versa.

I need a macro that will open each file and print out two copies of the either the “Total Statement” or “Total Piece Statement” depending on which sheet is contained within the file then close the files.

Can this be done?
Thank you for any and all help.

mancubus
05-14-2014, 12:08 AM
i understand workbook and worksheet names are identical...
perhaps...


Sub prt()
Dim WbWsNames As Variant
Dim i As Long
Dim fPath As String

fPath = ThisWorkbook.Path & "\"
'or
'fPath = C:\Docs\FilesToPrint\blah\blah\

WbWsNames = Array("Total Statement", "Total Piece Statement")

On Error Resume Next
For i = LBound(WbWsNames) To UBound(WbWsNames)
Workbooks.Open (fPath & WbWsNames(i) & ".xls")
Worksheets(WbWsNames(i)).PrintOut Copies:=2
Next i
End Sub

oam
05-14-2014, 02:37 PM
The file name are NOT identical, today one of the files (the database programmers had fun with this!) could be named "3202014153716_9150969003681220078099.xls" and the other file could be named "3202014153648_9275096900368122078086.xls" and tomorrow the files will have a completly different set of numbers that make up the name. That is why I need the code to open each file within one folder and search for the sheet names “Total Statement” or “Total Piece Statement” depending on which sheet is contained within the file, print the sheet, and close the files.

Thank you for your help!

Aussiebear
05-14-2014, 03:02 PM
Just how do you find the files, given the complexity of the file names?

mancubus
05-14-2014, 10:45 PM
Sub prt()
Dim WsNames As Variant
Dim i As Long
Dim fPath As String, fName As String

fPath = "C:\Docs\FilesToPrint\blah\blah\" 'change to suit
WsNames = Array("Total Statement", "Total Piece Statement")

On Error Resume Next
'to resume the code when any wb in the folder does not contain one or both of the ws's in the array

fName = Dir(fPath & "*.xls*")
Do While fName <> ""
Workbooks.Open (fPath & fName)
For i = LBound(WsNames) To UBound(WsNames)
Worksheets(WsNames(i)).PrintOut Copies:=2
Next i
ActiveWorkbook.Close False
fName = Dir()
Loop

End Sub

oam
05-20-2014, 04:33 PM
It works! Thanks for all your help!