Consulting

Results 1 to 4 of 4

Thread: Code behaving differently between "Module Sub" and "Private Sub Workbook_Open()"

  1. #1

    Unhappy Code behaving differently between "Module Sub" and "Private Sub Workbook_Open()"

    I am trying to write a Macro to print certain areas of multipal docs. I have it working as expexted from a module but when I try to put the same code into "Private Sub Workbook_Open()" so it will run when I open the document, it prints the entire documents not the selected parts. What am I missing?

    I have high lighted the area that seems to be not working in orange.

    The code I am using is,

    Private Sub Workbook_Open()
         
        Set WB = Workbooks.Open("C:\Folder\subfolder\book1.xls")
        WB.PrintOut , Copies:=2, Collate:=True
        WB.Close SaveChanges:=False
         
        Set WB = Workbooks.Open("C:\Folder\subfolder\book2.xls")
        WB.PrintOut , Copies:=1, Collate:=True
        WB.Close SaveChanges:=False
         
        Set WB = Workbooks.Open("C:\Folder\subfolder\book3.xls")
         
        Range("A39:Q76").Select
        ActiveSheet.PageSetup.PrintArea = "A38:Q75"
        With ActiveSheet.PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
              
        ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
        WB.Close SaveChanges:=False
         
        Set WB = Workbooks.Open("C:\Folder\subfolder\book4.xls")
         
         
        Range("A40:P78").Select
        ActiveSheet.PageSetup.PrintArea = "A40:P78"
        With ActiveSheet.PageSetup
            .FitToPagesWide = 1
            .FitToPagesTall = 1
        End With
         
        ActiveWindow.SelectedSheets.PrintOut , Copies:=1, Collate:=True
        WB.Close SaveChanges:=False
         
        Set WB = Nothing
         
        With CreateObject("Word.Application")
            With .Documents.Open("C:\Folder\subfolder\doc1.doc")
                .PrintOut
                .Close False
            End With
            .Quit
        End With
         
         
        With CreateObject("Word.Application")
            With .Documents.Open("C:\Folder\subfolder\doc2.doc")
                .PrintOut
                .Close False
            End With
            .Quit
        End With
         
    End Sub


    Any help would be very much appeciated.

  2. #2
    Put the code in the normal module and call it from workbook_Open like this:

    Private Sub Workbook_Open()
    Application.OnTime Now(), "TheMovedSub"
    End Sub
    Workbook_Open is often triggered while Excel is still busy doing housekeeping chores. The method shown above ensures Excel is done doing that.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,921
    Although you use With ... End With, you forget to refer to that object using a dot before .Range

    Private Sub Workbook_Open() 
       for j=1 to 2
         with getobject("C:\Folder\subfolder\book" & j & ".xls")
            .PrintOut , 3-j, -1 
            .Close 0
         end with
    
         with getobject("C:\Folder\subfolder\book" & j+2 & ".xls") 
           with .sheets(1).PageSetup
              .PrintArea = choose(j,"A38:Q75","A40:P78")
              .FitToPagesWide = 1 
              .FitToPagesTall = 1 
           End With 
           .sheets(1).PrintOut , 1, -1 
           .Close 0
        end with
    
        With Getobject("C:\Folder\subfolder\doc" & j & ".doc") 
           .PrintOut 
           .Close 0 
         End With
       next
    End Sub
    Or you could use:

    Private Sub Workbook_Open() 
       with createobject("shell.application")
         .shellexecute "C:\Folder\subfolder\book1.xls",,,"print",0
         .shellexecute "C:\Folder\subfolder\book1.xls",,,"print",0
         .shellexecute "C:\Folder\subfolder\book2.xls",,,"print",0
         .shellexecute "C:\Folder\subfolder\doc1.doc",,,"print",0
         .shellexecute "C:\Folder\subfolder\doc2.doc",,,"print",0
      end with
    
       for j=1 to 2
         with getobject("C:\Folder\subfolder\book" & j+2 & ".xls") 
           with .sheets(1).PageSetup
              .PrintArea = choose(j,"A38:Q75","A40:P78")
              .FitToPagesWide = 1 
              .FitToPagesTall = 1 
           End With 
           .sheets(1).PrintOut , 1, -1 
           .Close 0
        end with
      next
    
    End Sub
    Last edited by snb; 10-31-2013 at 02:19 AM.

  4. #4
    Thanks heaps, works now

Posting Permissions

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