Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slow performance after print preview | Excel Discussion (Misc queries) | |||
Performance | Excel Discussion (Misc queries) | |||
Performance IF(IF or IF(AND | Excel Worksheet Functions | |||
XLL performance in NT and XP | Excel Programming | |||
Bad performance | Excel Programming |