Printing files with VBA takes a long time

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Hi forum,

I have a VBA code which prints out a bunch of files in a folder. The files are named with numbers, which are listed in a column in my sheet. It takes a very long time for the first file to be sent to the printer (~30 secs), and for each time the next time is printed I have to wait another 30 secs as well. This is very inconvenient, since large numbers of files have to be printed.
The code runs through all the cells in the column and dims their value as variable 'VR1'.

Code:
Declare Function apiShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long
 
Public Sub PrintFile(ByVal strPathAndFilename As String)
     
       Call apiShellExecute(Application.hwnd, "print", strPathAndFilename, vbNullString, vbNullString, 0)
End Sub


Sub Print()
........
PrintFile "" & sPath & "\TEMP_printen\" & VR1 & ".pdf"   'sPath is the desktop path of the user
End Sub

NB: the pc is not directly connected with the printer, but rather via a network.

Does anyone know why this is taking so long, and how this can be done faster?


Thanks in advance
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

HackSlash

Active Member
Joined
Nov 18, 2016
Messages
360
1. Connect the PC directly to the printer.

2. Try asking this question in the support forum of the printer manufacturer
 

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
1. Not possible, since I am at an office.
2. Could do, but printing just by hand doesn't take that long.
 

Tim_Excel_

Well-known Member
Joined
Jul 12, 2016
Messages
512
Did not (yet), since I felt like this is excel/vba based and not really a problem caused by my pc or the network
 

Watch MrExcel Video

Forum statistics

Threads
1,123,037
Messages
5,599,444
Members
414,311
Latest member
liznic

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top