Consulting

Results 1 to 6 of 6

Thread: Solved: File Print Macro

  1. #1
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location

    Solved: File Print Macro

    I would like to be able to print 25 excel files in a certain order:

    gvyield
    gvopstcp
    gvefscon
    gvfrprod
    gvprosum - *needs to be in portrait*
    gvpaws
    gvgibhar
    gvadcostcp
    gvbonewhbb
    gvdebnew
    gvbonedk
    gvplgen
    gvwaste
    gvrepmai
    gvadmin
    gvcstfp
    gvfdmill
    gvfdhaul
    gvtruck
    gvospur
    gvfdmill trucking
    gvqa
    gvmaint
    gvpurch
    gvsanit

    If I could put the macro in the gvsanit file and when I am done editing it I could use some keystroke and it would print those files in order that would be great. Thanks in advance.

    Steve

  2. #2
    VBAX Tutor
    Joined
    May 2004
    Location
    Germany, Dresden
    Posts
    217
    Location
    I'd arrange the workbooks to be printed in an Excel table. For that example, name you need 2 columns: name of the workbook, orientation (1 = portrait, 2 = landscape). Now name the first name cell "rngPrint" and try the following macro (don't forget to change sPath!):


    [VBA]
    Sub PrintAll()
    Dim iRow%, iCol%, Wb As Workbook, MainWs As Worksheet, sPath$, Ws As Worksheet

    sPath = "E:\test\" 'Adjust to your path!!

    Set MainWs = ActiveWorkbook.Worksheets(1)
    iRow = MainWs.Range("rngPrint").Row
    iCol = MainWs.Range("rngPrint").Column

    Do Until MainWs.Cells(iRow, iCol).Value = ""
    Set Wb = Application.Workbooks.Open(sPath & MainWs.Cells(iRow, iCol).Value)
    For Each Ws In Wb.Worksheets
    Ws.PageSetup.Orientation = MainWs.Cells(iRow, iCol + 1).Value
    Ws.PrintOut
    Next Ws
    Wb.Close False
    iRow = iRow + 1
    Loop
    End Sub
    [/VBA]
    Daniel

  3. #3
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    OKay... when I tired this I get a 400 error code.

    I made a new workbook, enter all the file names in column A, in column B I put 1 or 2. I named cell A2 "rngPrint"

    Am I missing something?

  4. #4
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    First, check that your path is okay, and has "\" at the end.

    If you are still receiving the error, try stepping through the code. Minimise every application that you have running, then display the workbook with the filenames and the code. In a blank spot on your Taskbar (at the bottom of the screen) click the right mouse button and select Tile Windows Horizontally. Now click in the code and press F8 to step through one line at a time. This will highlight the line that is causing the problem.

  5. #5
    VBAX Tutor
    Joined
    May 2004
    Location
    Germany, Dresden
    Posts
    217
    Location
    Geekgirlau is completely right.
    And it would sure be helpful to see where the error appears, because the error message is not too meaningful.

  6. #6
    VBAX Regular
    Joined
    Jul 2004
    Posts
    10
    Location
    Thanks for your help. I did not have the "\" . Thanks again....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •