Printing multiple files

D

Dot Appleman

I have a set of EXCEL files, 661-001, 661-002...3, 662-001,
662-002...5, 681-001...3, 685-001, 681-025 ------etc. Each
file has 2 worksheets. How can I print each file,
consecutively without opening, printing ws-1, switch to
ws-2, closing, then opening the next file, etc., etc., etc?
Would this be done with a macro? Is there another way
where possibly the number of copies of each worksheet which
varies by file could be modified each month?
 
R

Richard O. Neville

If you combined all the files into one Excel document you could print them
all at once. You would do this by right-clicking on any sheet tab and
choosing "Select all." The print command and print quantity would then apply
to all sheet tabs. However, you would not be able to vary the quantities,
but you could print the entire document in the largest quantity and just
throw away the excess--unless this results in too much waste.
 
D

Dave Peterson

I don't see the pattern. Sometimes you go to -003 and sometimes you go to -025.

I think I'd create a new workbook.

If all the files are in the same folder, put the folder name in D1. If the
files vary, put the folder name in each cell (A1:Axxx). And put the number of
copies to be printed in that workbook in B1:Bxxx.

So it would either look like:
c:\my documents\excel\test\661-001.xls 3
c:\my documents\excel\test\661-002.xls 1
c:\my documents\excel\test\661-003.xls 2
etc...

or

661-001.xls 3 c:\my documents\excel\test
661-002.xls 1
661-003.xls 2
etc...

(D1 just because it's out of the way!)

Then you could create a macro that would read those cells and print the way you
want:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myFileName As String
Dim myFolderName As String

Dim mstrWks As Worksheet
Dim wkbk As Workbook
Dim wks As Worksheet
Dim testStr As String
Dim iCtr As Long

Dim okToPrint As Boolean
Dim QtyToPrint As Variant

Set mstrWks = ThisWorkbook.Worksheets("sheet1")

With mstrWks
.Range("c:C").ClearContents

If IsEmpty(.Range("d1")) Then
myFolderName = ""
Else
myFolderName = .Range("d1").Value
If Right(myFolderName, 1) <> "\" Then
myFolderName = myFolderName & "\"
End If
End If

For Each myCell In .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
myFileName = myFolderName & myCell.Value
testStr = ""
On Error Resume Next
testStr = Dir(myFileName)
On Error GoTo 0

If testStr = "" Then
myCell.Offset(0, 2).Value = "Missing!"
Else
Set wkbk = Workbooks.Open(Filename:=myFileName, ReadOnly:=True)

QtyToPrint = myCell.Offset(0, 1).Value

okToPrint = False
If IsNumeric(QtyToPrint) Then
If QtyToPrint > 0 _
And QtyToPrint < 10 Then
okToPrint = True
End If
End If
If okToPrint Then
For Each wks In wkbk.Worksheets
wks.PrintOut _
copies:=myCell.Offset(0, 1).Value, preview:=True
Next wks
' ' or
' For iCtr = 1 To 2
' wkbk.Worksheets(iCtr).PrintOut _
' copies:=myCell.Offset(0, 1).Value, preview:=True
' Next iCtr
Else
myCell.Offset(0, 2).Value = "Quantity Error!"
End If
wkbk.Close savechanges:=False
End If
Next myCell

If Application.CountA(.Range("c:c")) > 0 Then
MsgBox "At least one workbook error!"
End If
End With

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

I have preview:=true to save paper while testing.

I figured that 1-9 copies was sufficient. Change this portion if you want to
allow larger values:

And QtyToPrint < 10 Then

This portion has two variations.

If you want to print all the worksheets, use the first version.

For Each wks In wkbk.Worksheets
wks.PrintOut _
copies:=myCell.Offset(0, 1).Value, preview:=True
Next wks

if you know that you want to print 2 worksheets, use this version.


' ' or
' For iCtr = 1 To 2
' wkbk.Worksheets(iCtr).PrintOut _
' copies:=myCell.Offset(0, 1).Value, preview:=True
' Next iCtr
 
J

Jay

I have a set of EXCEL files, 661-001, 661-002...3, 662-001,
662-002...5, 681-001...3, 685-001, 681-025 ------etc. Each
file has 2 worksheets. How can I print each file,
consecutively without opening, printing ws-1, switch to
ws-2, closing, then opening the next file, etc., etc., etc?
Click to expand...

One way is to keep the files in a binder rather than a folder.

Start at the desktop and right-click
New >> Microsoft office binder
Then open the binder and drag the Excel files into the left panel of its
window.

The binder's built-in "help" gives an overview of how to use it. Basically,
it's kind of like a folder, but can contain only MS-Office files (e.g.,
Excel, Word). The binder command relevant to your need is:
File >> Print Binder

(I have Office 97.)
 
Top