PDA

View Full Version : [SOLVED] Code behaving differently between "Module Sub" and "Private Sub Workbook_Open()"



sadiablo
10-30-2013, 05:46 PM
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
10-30-2013, 11:04 PM
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
10-31-2013, 02:05 AM
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
10-31-2013, 07:07 AM
Thanks heaps, works now :)