How to check whether the printing is successful or failed

[jinbao]

Firstly, sorry for my poor English.

Sheet1 B5 stores the invoice No. The following code makes the invoice no add 1 after printing. I want to add one function which can tell me when the printing is failed. i.e (paper jam, short of paper). So I can have a chance to fix the printer. And the invoice No stays same value.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("sheet1").[b5]
.Value = .Value + 1
.NumberFormatLocal = "00000000"
End With
End Sub

[Leith Ross]

Hello jinbao,

Since VBA doesn't have any native functions, methods, or properties to return the printer status, it requires using the Windows API to retrieve the information. I needed to get this information a few years back and wrote these macros to retrieve the printer status along with other printer information. This works with Windows 2000 through 2003. It has not been tested on Windows 2007 and up. Copy all the code below into a standard VBA module. I have included instructions below the code.

Run the macro PrinterTest. This will return the status of the active printer. You can modify this same code to check any printer by replacing the varaible P with the name of the printer you want to check.

Please Login or Register  to view this content.

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

[jinbao]

thank you Leith Ross,

I am quite busy on my work these days. I will try your code sometime next week. I am appreciated your help. And I will let you know the result.

[Leith Ross]

Hello jinbao,

If you have any questions about the code, let know.

[jinbao]

Hi, I have followed the instruction to run the code. But I got error on Excel2010. The error information is

Problem signature:
Problem Event Name: APPCRASH
Application Name: EXCEL.EXE
Application Version: 14.0.4756.1000
Application Timestamp: 4b9c08e8
Fault Module Name: StackHash_dfb4
Fault Module Version: 6.1.7600.16559
Fault Module Timestamp: 4ba9b21e
Exception Code: c0000374
Exception Offset: 000c2913
OS Version: 6.1.7600.2.0.0.256.1
Locale ID: 2052
Additional Information 1: dfb4
Additional Information 2: dfb4403f62700cdb987018121b21bab5
Additional Information 3: 6e39
Additional Information 4: 6e39d163e533fff4805e5a1779b5459a

Read our privacy statement online:
http://go.microsoft.com/fwlink/?link...8&clcid=0x0409

If the online privacy statement is not available, please read our privacy statement offline:
C:\Windows\system32\en-US\erofflps.txt

And my printer is HP PSC 1315 All-in-one.

Thanks

[jinbao]

Hi Leith Ross,

Do you know what is the problem?

Thanks

[Leith Ross]

Hello jinbao,

Since I haven't purchased 2010 yet, I really don't know what the problem is. There are a few things I do know that might be contributing to the problem. All the old functions have been rewritten and depending on your machine Office may either be 32 bits or 64 bits. These functions are all 32 bit. 2010 is so new that there is little information regarding the API or exactly what changes have been made to which calls.

[paul bishop]

I have tried this with Excel 2003 and Windows XP for both a local and a networked printer. I always get a response of "ready", despite having removed the paper or switched the printer off. The only exception when I have paused printing, which correctly reports a status of "paused".