PDA

View Full Version : Checking that a document in Word was printed from Excel?



sponge
06-14-2005, 08:18 PM
Hi,

I was wondering how I would go about checking that a document in Word was printed from Excel. The code I have so far is as follows:

Dim wdApp As Word.Application

Set wdApp = New Word.Application

With wdApp
.PrintOut
.Quit wdDoNotSaveChanges
End With

Set wdApp = Nothing

Any help would be greatly appreciated.

Killian
06-15-2005, 04:55 AM
Hi and welcome to VBAX :hi:

Rather unhelpfully, VBA methods don't tend to have useful things like return values for success evaluation, so unless an error occurs that you can trap, you'll need to work around that limitation.
The PrintOut method has a number of optional paramaters. (select PrintOut and hit F1 for all the details).
Setting the Background parameter to False means the macro will not continue until the PrintOut is complete. (also, at the moment, because you haven't specified the FileName paramater, the ActiveDocument is printed)
Although this sounds promising, you should be aware "complete" means that the macro will continue when the job has spooled to the print queue... which doesn't actually mean it's physically printed.
(I think it's about the best you can hope for, unless you want get involved in enumerating the print jobs with the Windows API and or waiting for network messages and attempting to intercept them.)

sponge
06-15-2005, 06:45 AM
Hi,

Thanks for your excellent reply. Enabling background printing definitely helped.

I was searching through Microsoft's Knowledge Base and stumbled upon this: http://support.microsoft.com/default.aspx?scid=kb;en-us;151316

There's a piece of code there that deals with print error handling. It works fine for the activesheet in excel but I haven't been able to get it to work with Word documents printed from excel.

I tried playing around with the
"Printout_Successful = ActiveSheet.PrintOut" line but to no avail.
I keep getting and "Expected Function or Variable" compile error.

Any ideas? Many thanks in advance.

Killian
06-15-2005, 08:43 AM
It's not unusual to find inconsistancies in the way similar methods behaves from one Office app to another and here's a case in point. In Excel, PrintOut returns a value that you can assign to the "Printout_Successful" variable, Word's PrintOut doesn't, hence the compile error (the compiler needs somthing after the "=" to assign to the variable and it knows PrintOut doesn't produce one)
You can simulate this by trapping any error produced (e.g. there's no active document, or the doc path you provide isn't valid or there's no printer connected, etc, etc)Dim wdApp As Word.Application

Set wdApp = New Word.Application
With wdApp
.Visible = True
On Error Resume Next
.PrintOut Background:=False
If Err.Number <> 0 Then
MsgBox "Printout failed" & vbLf & Err.Description
End If
.Quit wdDoNotSaveChanges
End With

Set wdApp = Nothing