Print Performance in VBA
 

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

It there are several factors. Is the printer on a network? Is the printer
busy? Was the primary printer specified? VBA will automatically step
through these, which takes very little time, but if the printer is not ready,
and depending on the type of printer, it can take a couple of seconds. Still
it should indicate that the file was sent to the printer pretty quickly.

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

I just noticed your syntax, Sheet2.Print.

This is not the normal syntax for printing sheets. I would use:

Sheet2.PrintOut

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

I believe it is indeed Sheet2.printout. I'm typing from memory!

This is a stand-alone system - a P4 PC with an attached printer. No
network. In this particular configuration, it is printing to an HP LaserJet
1400 that has a 8 meg buffer. The entire application is less than 1 meg!

While the PrintOut command is executing, the screen locks up - that is, the
hourglass replaces the cursor and nothing else can happen in the application
until it finishes. Naturally, I can go to another application - Word,
Solitaire - while waiting for Excel to execute the print, but I can't
understand why this bogs down so heavily. After all, the sheet has been
completely formatted and all I'm doing is sending it to the printer!


"JLGWhiz" wrote:

I just noticed your syntax, Sheet2.Print.

This is not the normal syntax for printing sheets. I would use:

Sheet2.PrintOut

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

ISS6,

Does it ever finish? Perhaps you could post the code you are using to get
your printout.

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

Aye, it finishes, just slower than molasses.

The code is:

Sheet2.PrintOut

That's it. The sheet is built from data selected rom Sheet1. This happens
very quickly (I stepped through the code to isolate the problem). I can
Print Preview the report and it's perfect. Then I step onto Sheet2.PrintOut
and wait up to a minute. The report is printed and then control returns to
the menu.


"JRForm" wrote:

ISS6,

Does it ever finish? Perhaps you could post the code you are using to get
your printout.

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

Just guessing, but it sounds like a communication problem with the printer.
Maybe the printer driver. But you said that manually it zips right out, so
probably not the driver. I am not really a techie, so I don't know what the
difference between a VBA print command and a click event command amount to.
I would think, very little. I do not think it is VBA per se that is the
problem.

"ISS6" wrote:

Aye, it finishes, just slower than molasses.

The code is:

Sheet2.PrintOut

That's it. The sheet is built from data selected rom Sheet1. This happens
very quickly (I stepped through the code to isolate the problem). I can
Print Preview the report and it's perfect. Then I step onto Sheet2.PrintOut
and wait up to a minute. The report is printed and then control returns to
the menu.


"JRForm" wrote:

ISS6,

Does it ever finish? Perhaps you could post the code you are using to get
your printout.

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

The report is formatted to Sheet very quickly. However, the actual command
PrintOut is extremely slow in executing. It just sits there on this line for
up to a minute, do what I have no idea. Maybe waiting for the printer to
power up? (The printer is on all the time, but I think it goes into a
standby mode to conserve energy) Whatever happened to the Windows print
spooler? I thought that the spooler was an automatic part of Windows, but
maybe I need to manage it programatically?

If I'm just waiting for the printer to that is the case, can the printer be
"poked" to wake it up when the Reports Menu is entered - say in the
UserForm_Activate module - without anything actually being printed?

Oh, what a tangled web.......


"JLGWhiz" wrote:

Just guessing, but it sounds like a communication problem with the printer.
Maybe the printer driver. But you said that manually it zips right out, so
probably not the driver. I am not really a techie, so I don't know what the
difference between a VBA print command and a click event command amount to.
I would think, very little. I do not think it is VBA per se that is the
problem.

"ISS6" wrote:

Aye, it finishes, just slower than molasses.

The code is:

Sheet2.PrintOut

That's it. The sheet is built from data selected rom Sheet1. This happens
very quickly (I stepped through the code to isolate the problem). I can
Print Preview the report and it's perfect. Then I step onto Sheet2.PrintOut
and wait up to a minute. The report is printed and then control returns to
the menu.


"JRForm" wrote:

ISS6,

Does it ever finish? Perhaps you could post the code you are using to get
your printout.

"ISS6" wrote:

I have developed an EXCEL 2003 VBA application that works like a charm -
except for printing various reports.

The user selects a report to be produced and it is built to a new sheet.
Watching the process, I can see that this happens very quickly.

However, once the command Sheet2.Print is executed, processing drops to a
crawl. It takes up to a full minute to get the sheet sent to the printer.
What's going on? I can perform the same function from the spreadsheet in a
blink of an eye, but within VBA, it takes forever.

Am I forgetting to set something?

Print Performance in VBA
 

Guys,

Isn't the syntax supposed to be

Sheets(2).PrintOut

?


--JP

On Oct 4, 6:23 pm, ISS6 wrote:
The report is formatted to Sheet very quickly. However, the actual command
PrintOut is extremely slow in executing. It just sits there on this line for
up to a minute, do what I have no idea. Maybe waiting for the printer to
power up? (The printer is on all the time, but I think it goes into a
standby mode to conserve energy) Whatever happened to the Windows print
spooler? I thought that the spooler was an automatic part of Windows, but
maybe I need to manage it programatically?

Print Performance in VBA
 

Probably not.

Next time you're in the VBE, look at the project explorer window.

If you expand the objects, you'll see something like:

Sheet2 (Sheet2)
or
Sheet2 (SheetnameYouSeeOnTheTabInExcel)

The name on the left is called the Codename. The name in parentheses is the
name (what's seen on the tab in excel).

My bet is the original poster was refering to the Codename of the worksheet.
This codename is usually much more difficult for the average user to change--and
isn't susceptible to any sheet name changes that the user may make.

Sheets("sheetnamehere").printout
would fail if the user renamed "sheetnamehere" to something else.

And if the user reordered the tabs, then
sheets(2).printout
may not print the sheet that the developer wanted.
This will print the second sheet (starting on the left).

Using codenames in your macros will make your code more robust.

=====
The original poster could have declared a variable and named it Sheet2, but
that's pretty unlikely--and with a variable with a name like that would confuse
the heck out of me!

JP wrote:

Guys,

Isn't the syntax supposed to be

Sheets(2).PrintOut

?

--JP

On Oct 4, 6:23 pm, ISS6 wrote:
The report is formatted to Sheet very quickly. However, the actual command
PrintOut is extremely slow in executing. It just sits there on this line for
up to a minute, do what I have no idea. Maybe waiting for the printer to
power up? (The printer is on all the time, but I think it goes into a
standby mode to conserve energy) Whatever happened to the Windows print
spooler? I thought that the spooler was an automatic part of Windows, but
maybe I need to manage it programatically?


--

Dave Peterson