Printing multiple embedded Word Documents

[michael.haight]

I'm a novice VBA coder, but am trying to make this work. I have an
Excel workbook that contains 4 worksheets. The first worksheet is a
summary, and this is where I'd like to add a button to "Print All".
The remaining 3 worksheets each contain data as well as an embedded
Word document. I would like the "Print All" button to print both the
data from each sheet, as well as the embedded Word document from each.
I've put together the code below, but receive an error on the
"PrintOut" line.

Any help???

Thanks!
-Mike


Private Sub cmdPrintAll_Click()

Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets

For i = 1 To ActiveSheet.OLEObjects.Count
ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut

Next i

Next sh

End Sub

 

[Gary''s Student]

perhaps activate each sheet in turn:

Private Sub cmdPrintAll_Click()

Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
For i = 1 To ActiveSheet.OLEObjects.Count
ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut

Next i

Next sh

End Sub

 

[michael.haight]

perhaps activate each sheet in turn:

Private Sub cmdPrintAll_Click()

Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
For i = 1 To ActiveSheet.OLEObjects.Count
ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut

Next i

Next sh

End Sub

--
Gary''s Student - gsnu200756











- Show quoted text -

Thanks for the advice! Unfortunately, when I tried that change I
received the following error:

Run-Time Error '438':
Object doesn't supposrt this property or method

Thoughts?!?!

 

[Gary''s Student]

Suppose one of the sheets has no Object?? Let's try:

Private Sub cmdPrintAll_Click()

Dim CurVis As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
If ActiveSheet.OLEObjects.Count > 0 Then
For i = 1 To ActiveSheet.OLEObjects.Count
ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut
Next i
End If
Next sh

End Sub

 

[michael.haight]

Yes, the very first sheet had no embedded document. But sadly, even
with that code change I still receive the same error message. But I
know those documents are OLE Objects because I wrote code for a button
that would open each document when clicked.

Any other thoughts on this one?

(sorry to monopolize your time - I'm just out of ideas)

-Mike

 

[Gary''s Student]

My time is of no value.
To continue de-bugging we should put an MsgBox inside the For Loop. We need
to find out which sheet and which object is causing the problem!

 

[michael.haight]

Thanks for the tip! I did what you said and determined what was
happening. I have multiple OLEObjects on each sheet (buttons, etc.) -
and so the code below was trying to print the first one on each page,
rather than finding just the Word document.

Any tips on how I might be able to change the code to print only the
single embedded Word document on each page? If it helps, on each page,
the embedded Word document object was given the same name of
"objStatusReport".

Thanks!
-Mike

 

[Gary''s Student]

Instead of:

ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut

use

If ActiveSheet.OLEObjects(i).Name = "objStatusReport" Then
ActiveSheet.OLEObjects(i).Activate
ActiveSheet.OLEObjects(i).Object.Application.PrintOut
End If