Printing Multiple Workbooks through Master Document?

  • Thread starter Thread starter ScottP33
  • Start date Start date
S

ScottP33

Word has a facility to pull together multiple documents
into one "master" document. I have 120 separate workbooks
that need to be grouped and printed in specific orders.
Unfortunately that order changes monthly. In order to
print easily does Excel have master document capabilities
or would a macro openening and closing specific files from
an input source be better? I've tried linking and
inserting the print ranges into a workbook but this has
not worked. Suggestions?
 
Hi ScottP33

If you want to have control of the order you can fill in the names of the workbooks
from a directory(C:\Data) in the master workbook.on a empty sheet

Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles(i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


You can sort the list or do something else and then run this macro to print them all

Sub printall()
Dim cell As Range
Dim Wb As Workbook
For Each cell In Columns("A").SpecialCells(xlCellTypeConstants)
Set Wb = Workbooks.Open(cell.Value)
Wb.PrintOut
Wb.Close False
Next
End Sub
 
Thanks! Because I was vague in the post I had to rework it
a bit but that helped me learn more. Much appreciated
-----Original Message-----
Hi ScottP33

If you want to have control of the order you can fill in the names of the workbooks
from a directory(C:\Data) in the master workbook.on a empty sheet

Sub test()
Dim i As Long
With Application.FileSearch
.NewSearch
.LookIn = "c:\Data"
.SearchSubFolders = False
.MatchTextExactly = False
.FileType = msoFileTypeAllFiles
If .Execute(msoSortOrderDescending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file (s) found."
For i = 1 To .FoundFiles.Count
Cells(i, 1).Value = .FoundFiles(i)
Cells(i, 2).Value = FileDateTime(.FoundFiles (i))
Cells(i, 3).Value = FileLen(.FoundFiles(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub


You can sort the list or do something else and then run this macro to print them all

Sub printall()
Dim cell As Range
Dim Wb As Workbook
For Each cell In Columns("A").SpecialCells (xlCellTypeConstants)
Set Wb = Workbooks.Open(cell.Value)
Wb.PrintOut
Wb.Close False
Next
End Sub



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)




"ScottP33" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
 
Back
Top