Printing multiple files

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?
0
appleman (4)
8/23/2004 2:22:42 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
448 Views

Similar Articles

[PageSpeed] 58

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.

"Dot Appleman" <appleman@achamp.gsfc.nasa.gov> wrote in message
news:450301c4891c$a6b5d300$a301280a@phx.gbl...
> 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?


0
roneville (91)
8/23/2004 7:37:00 PM
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





Dot Appleman wrote:
> 
> 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?

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/23/2004 10:57:29 PM
> 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?

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.)
0
8/24/2004 9:54:12 PM
Reply:

Similar Artilces:

Import large text file
Hi, Which options do I have to import a text file to Excel, with more than 200.000 records. Thanks Luis Hi Luis, Did you try opening the file from Excel (file,open) then after going through the file import wizard and you have what you want you can use file, save as, ...) Since you are not used to this suggest you start with a copy of your flat text file. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "L. A. M." ...

How do I print a list of the directory contents
I need to print the disk directory contents. In DOS this was possible. Is it possible in WORD? Thanks No - but you could use the freeware Printfolders utility you can download from my web site that will produce a text listing that you can open in Word. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <...

Multiple invoices to multiple customers from one order?
I think that this is not possible, but I need to ask since it will be happening on a regular basis: We have a new contract with a customer who has independent contractors. The contractors will come in and purchase a specific large item from us - mother company will pay a specific monetary amount and the contractor is responsible for the rest. So basically, we need to create one order, but have two invoices to two different customers that are linked to the original order. Is there ANY way to do this. Thanks Yes, You can use National Accounts for this. You can setup your main contra...

printing copies
under print options: Number of copies___. It will only print out 1 copy. I say 5 and it still prints out 1. I am a new user of 2007 Excel I suspect the problem is with the printer. Try getting the newest driver best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Val in Texas" <Val in Texas@discussions.microsoft.com> wrote in message news:78A83505-189A-4731-AF25-CD5505E33510@microsoft.com... > under print options: Number of copies___. It will only print out 1 copy. > I > say 5 and it still prints out 1. ...

Ghosted print and print preview
Hello everyone, I have a problem with printing in excel. The print and print preview options appear ghosted. I still can print through the print icon on the toolbar. Any suggestions??? Martin ...

Print Dynamic Range
I have a basic excel template used for ordering parts. It runs from A1:N250 and I can print easily repeating rows 1:11 on each page (contains job details) . Trouble is sometimes there may only be 10 part numbers requiring only first page and I finish up throwing away trees by printing entire sheet! I know I can reset print area each time am setting this up on Sharepoint for multi users and need a macro/button to recognise number of items and print accordingly. Any help greatly appreciated -- Message posted via http://www.officekb.com Sub SetPrintArea() 'change this cons...

TCP/IP printing
Hello, I have a networking question. I have a print device with a static IP address on one network. A computer on another network can connect to the printer (NBT is enabled and so is LPR) and the drivers are installed but cannot print. I know this is correct behavior but want to know in detail why it seems to half work. I am guessing it has to do with port 138,139 and 515 but not positive. Probably a better question to ask a networking forum but hey. Thanks can you access the printer through the printer IP address? -- ashvin :D Just chill -pls post your feedback on...

sumproduct .. validating 2 criterias (multiple tabs)
hi guys, i am trying to add additional functionality to my Project Budget Expendature worksheet of which some of you may have seen. anyways, the idea is to have sumproduct calculate all items that have the same GL code & date of which it was purchased (date of purchase) currently i have the following TAB: Spend Calendar ROW C8:N8 = Month #'s (Jan, feb ect) COL B13:B36 = GL Codes (130000,190000 ect) TAB: 001-013 (13 tabs) CELL C6 = Date of Purchase COL C24:C37 = GL Codes of each item within the purchase COL J24:J37 = Total cost of the item which the GL Code is related to can someon...

Adding multiple items to a discount schedule in HQ
I would like to run a discount every Wednesday on most items in my store from 8am to 11am. I know how to create the schedule but can't seem to find a way to add the items to this schedule in HQ without going through each individual item. Any help would be greatly appreciated. Hi, I am just looking through old posts. Are you still wondering about how to script this change? -- Jocelyn "Pierre" wrote: > I would like to run a discount every Wednesday on most items in my store from > 8am to 11am. I know how to create the schedule but can't seem to find a way &g...

Printing booklet
I am producing a 50 to 60 page booklet using Pubisher 2003. The page format will be using 8.5 x 11 paper folded in half. I need to print both sides of the paper. How do I print the booklet so that I print half the project on one side of the paper and the other half on the back. Will publisher keep track of the pages so that for example; that page 2 and page 50 print on the same side/sheet of paper so when all pages are printed, folded and bound the book reads correctly. Thanks John Houston, Tx more info...I am using a single side printer so I have to run the pages through twice ...

Why is my 11-pg .xls file 35 MB (yes, EM bee)?
Why is my .xls file 35 MB (yes, EM bee)? I have an excel file with 11 worksheets. Each sheet is less than 1 page of figures. Some cells derive their values by looking up a cell in another worksheet. The print area is defined for each sheet. Saved separately, the individual worksheets are only 15 to 50 KB a piece, which is what I would expect. All together, I would expect the file size to be under 500k, not 35 MB. Dang! I don't get it! (I'm not a frequent excel user - I've probably done something really bone head, but I cannot image what. Teelbee Hi Teelbee, Don't...

Check printing in GP 7.0
Dear Guys, I have check payments for vendors, where I'm dealing with LOCAL CURRENCY & FORIGHN CURRENCY. When I paid by check in LOCAL CURRENCY then the amount in words prints perfectly but if i paid by check in FORIGN CURRENCY then the amount in word prints in numeric which i want to print amount in words instead of numbers. Please help me to print checks amount in words for FORIGN CURRENCY as well. Your suggestion would be appricated on above issue thnx Shamin ...

Excel BUG (All Versions) Excel Macro Margins using Print Preview
Where can i post this BUG? (Excel Macro Margins using Print Preview) Is there an Excel BUG reporting site? Steps: 1) New worksheet, write something in it 2) Start recording a new macro 3) File -> Print Preview 4) Click on the Margins Button, the page margins will appear, so that you can visually change and move them 5) Set one or more margins 6) Close the Print Preview 7) Stop the Macro What happened is that in the VBA code there will be writtenthe following lines: ..LeftMargin = Application.InchesToPoints(0.91) ..RightMargin = Application.InchesToPoints() Where in second line ther...

Multiple Pivot tables from One selection
I would like 2 or 3 pivot tables to use on pivot table selection. I.e the selection criteria of the 1st pivot table on the menu page determines what the page data of the other pivot table displays on seperate sheets where the other pivot tables are contained. So the selection of one pivot table controls the rest. -- dapo ------------------------------------------------------------------------ dapo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29967 View this thread: http://www.excelforum.com/showthread.php?threadid=496616 Check the "Synchronize 2 Piv...

CHtmlView and Printing
Hi Everyone, The CHtmlView is giving me alot of head aches. Here is what I am doing. I am opening an XML file (being formatted using XLST) in a hidden CHtmlView and printing it. Here is how the process goes: 1. I create the XML file on the fly. 2. load the XML file in to CHtmlView. 3. Once loaded I tell it to print the resulting HTML page. 4, I need to delete XML file after the printing is done. (this is the problem). I can't figure out how to find out when the CHtmlView is done printing. CHtmlView::OnFilePrint is asynchronous. And CHtmlView doesn't tell me that it is currently p...

Display file name without the file type extension
Is it possible to return the file name value to a cell without the '.xlsx'? Thanks. Try the below in a saved workbook. =TRIM(LEFT(SUBSTITUTE(MID(CELL("filename",A1), FIND("[",CELL("filename",A1))+1,255),".xl",REPT(" ",255)),255)) If this post helps click Yes --------------- Jacob Skaria "NAHolmes" wrote: > Is it possible to return the file name value to a cell without the '.xlsx'? > > Thanks. Try this: =MID(CELL("filename",A1),FIND("[",CELL("filename",A...

How do I convert PDF files to Excel?
I need to convert PDF files to formatted a Excel file so that I can make adjustments to the amounts in one column. "Kusko" <Kusko@discussions.microsoft.com> wrote in message news:59E00681-1630-4D2D-BB1A-2DEB11DA3AF3@microsoft.com... > I need to convert PDF files to formatted a Excel file so that I can make > adjustments to the amounts in one column. You need Adobe Acrobat if you want to modify a pdf file. /Fredrik Scan it and send it to a OCR program. Hoping that you have a clean legible master to scan. -- HansM "Kusko" <Kusko@discussions.microsof...

Problem on Windows XP Pro x64Bit and System Registry Hive file.
I am italian, but I haven't received no response in italian newsgroup... I have the following computer: OS: Win XPpro Corporate x64Bit CPU: Core2Quad Q9650 RAM: Kingston ValueRAM DDR2 4GB CL6 HDD: CaviarBlue 500GB WD x3 VGA: Sparkle GeForce 9400GT 512MB I installed various graphics programs such 3DSMAX9 x32 and x64Bit Maya 2010 Entire suite Adobe CS4 Master Collection Nero Reloaded 9 Office etc. ... A few days I have bought online in more than 2 memory modules (for achieve the 8GB total) identical to the DDR2 memory that I had and namely: 2GB Module 1...

"Tiled" print setting.
I just want the page that I designed to print on one page! I can't change the print setting "tiled" and as it is, it's printing on four pages all broken up. It is possible that you have created your document on a paper size that is not supported by your printer. Try changing your paper size to 8 1/2 by 11 and see if the print options are more to your liking. "MissMarple" <MissMarple@discussions.microsoft.com> wrote in message news:A6776BEA-37EE-4645-B389-866A6CDB2D96@microsoft.com... >I just want the page that I designed to print on one ...

sharing .pst files using the Briefcase
I want to share .pst files using the briefcase, but unfortunately this doesn't work properly. The pst file I'm delivering emails to is displayed incorrectly on the computer using the Briefcase. The only folders displayed are the default Contacts, Inbox, Sent Items, etc - I can't see the other folders I created. The folders are displayed correctly on the computer where the original pst is stored. The pst files are equal in size. I attempted to research the problem using the Microsoft Knowledgebase but unfortunately the relevant article (294759) has been removed for an unspecifie...

Missing "Save Embedded Files dialog box"
I am using Frontpage 2003 (portable). After replacing my harddrive and reinstalling Frontpage 2003 I am unable to upload pictures to my website. The "Save Embedded Files dialog box" is missing and does not come up after adding a new picture to a page and saving. Suggestions? Import your images into FP web/site first (into folder of your choice), then place them into your design...you will never have a problem doing it this way. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression Web "USKDMMA" <USKDMMA@discussions.micro...

CRM Multiple Companies
MSCRM should have the ability to host multiple companies in one installation of the product. Some of our clients have multiple companies within their business model that are truly seperate. The next version of CRM needs this functionality to stay competitive with the other CRM products. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-base...

Problems with printing
Dear all I have an application, which has been working without problems for the last 7 years. The application has some printing capabilities. Now, we have just gotten a new printer (HP Laserjet CM4370), and when I try to print, my application crashes. The app crashes in the print setup dialog even before I press OK. If I select any other printer as default printer, the app doesn't crash. Then I can show the print setup dialog without problems. But then, when I select the CM4370, the app crashes again, even if I do not press OK. Even if I choose to not show the print setup dialog, and j...

Multiple Bar Chart Formats
How do I combine a bar chart with summary data (for example, 5 categories of summary data) with a stacked bar chart (a sixth category made up of the parts that make the summary)? For example, I have 6 construction projects. I wish to show summary data for five of the projects (assume they are complete) as a bar chart. I have 4 categories of subtotals that make up each summary. In the case of the sixth project, wish to show these sub totals as a stacked bar. I want to do this on one chart. I think you want a stacked chart for all of this if I understand your question. Set up your data ...

Printing--How not to Print Bkground
In my wrksht there is a cell range highlighted with an autofill color. We use this to show the user where to enter the data. Is there anyway to avoid printing this background color. We want to keep the color in the cell range in our wrksht but we dont want it printed. Hi Jeff, have you tried Print preview, Setup, Sheet, Black & white? Gilles "Jeff" <anonymous@discussions.microsoft.com> wrote in message news:001201c3ba81$023b8af0$a301280a@phx.gbl... > In my wrksht there is a cell range highlighted with an > autofill color. We use this to show the user where t...