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

[sadiablo]

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.

[Jan Karel Pieterse]

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.

[snb]

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

[sadiablo]

Thanks heaps, works now