1

strong textI have a simple word form that I want to print. I pass the printer name to a sub routine (myprint) in the variable oprinter. The variable printcomplete will pass back the name of the printer that was successful in printing the data.

This logic works when I am in debug mode but appears to bypass the print commands when not in debug. I have tried adding delays, I have added a MSGBOX after the print statement (prior to printcomplete = oprinter > Exit Sub). The MSGBOX does display - so it should have executed the print statement. I do not get the msgbox under myprinterr.

Any idea why it won't print out of debug mode?

Here is my code:

'Print MS Word Form to one of three networked printers                
Sub MyPrint(oprinter, printcomplete)                                                  
Dim sPrinter As String            
On Error GoTo myprinterr               
Sleep (5000)

With Dialogs(wdDialogFilePrintSetup)                    
sPrinter = .Printer                     
.Printer = oprinter                    
.DoNotSetAsSysDefault = True                
.Execute                

   Sleep (5000)

Application.PrintOut FileName:=""                    
.Printer = sPrinter                
.Execute                                                       
End With                                                          
   Sleep (5000)

MSGBOX "Did it print? "

printcomplete = oprinter                                
Exit Sub

myprinterr:
   MsgBox "oops  printer error on: " & oprinter
 
End Sub
Sheila Stohlmann
  • 11
  • 2

1 Answers1

0

In the code I've noticed several lines of code:

CreateObject("Excel.Application").Wait (Now + TimeValue("00:00:05"))    'delay to try to get print to work when not in debug mode

There is no need to create a new Excel Application instance each time.

If you want to introduce any delay you can use the Sleep Windows API function instead. It suspends the execution of the current thread until the time-out interval elapses.

Eugene Astafiev
  • 47,483
  • 3
  • 24
  • 45
  • @eugeneastafiev thanks for the logic improvement. I've changed the .wait to sleep as you've suggested, however it still will only print in debug mode. Any other ideas? – Sheila Stohlmann Jun 21 '21 at 16:49
  • I still don't see the source code updated in your post. A lot of Excel instances are still created. – Eugene Astafiev Jun 21 '21 at 16:51
  • When code executes correctly when debugging but not in real time, the issue is almost always that the delay you've added isn't long enough. Both changing printers and printing run very slowly compared to VBA. – John Korchok Jun 21 '21 at 17:18
  • Sub MyPrint(oprinter, printcomplete) Dim sPrinter As String On Error GoTo myprinterr Sleep(5000) With Dialogs(wdDialogFilePrintSetup) sPrinter = .Printer .Printer = oprinter .DoNotSetAsSysDefault = True .Execute Sleep(5000) Application.PrintOut FileName:="" .Printer = sPrinter .Execute End With Sleep (5000) MSGBOX "Did it print? " printcomplete = oprinter Exit Sub myprinterr: MsgBox "error on: " & oprinter End Sub – Sheila Stohlmann Jun 21 '21 at 18:17
  • I have three 5 second delays, any suggestion as to how long of delay I need and where the delay needs to be placed? – Sheila Stohlmann Jun 21 '21 at 18:19