Urgent !!!! Excel Tray Switching Problem

[richard.feng]

I developed an application using VBA addin managed extension in C#.
Currently i face a problem that Excel can't switch tray when printing a
multi-section report.

(I don't want to use SendKey method in VBA. It is a dirty and
troublesome approach to the problem. I tried before, it doesn't work
and the result is even worse)

So I wrote the switching tray process in Win32(C) API, then it
inter-operates with C# code in my managed COM object for the
automation. But some worksheets still printed from the wrong tray.
After research I found that I used Wn32's SetPrinter function which
globally changed the printer's setting. And if some jobs haven't been
printed out, the rest of the worksheet will print from the wrong tray.
Moreover, using threading sleeping method to pause the print job does
not really work because I can't predict a printer job when it will be
finished. I tried EnumJobs to detect the number of printer jobs before
switching the tray, but without any success.

So after, I decided to use Win32's DocumentProperties to change printer
settings per application. However, I found that documentproperties
would not take effect if I don't rewrite the HDC paint code. Obviously,
it is not possible for me to rewrite Excel Printout function using HDC
approach.

Then the last choice i found is that I might be able to set up multiple
drivers with different tray set up on each driver. But it also didn't
work because Windows prevents me from installing multiple printer
drivers.

I am totally desperate, now. Can anyone give me some ideas on solving
my problem, even recommendation I will take it. (Sorry that I don't
want to hear anything about SendKeys approach to solve this problem.
But if you insist the SendKeys function with very unique method, I will
give a try as well.

Many thanks to all
Richard

 

[Jonathan West]

Hi Richard

Take a look at this article

Controlling the Printer from Word VBA
Part 1: Using VBA to Select the Paper Tray
http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=101

Although it is Word VBA, you should be able to adapt it to Excel.

--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[richard.feng]

Hi Jonathan, thanks for the reply. But the article is using SetPrinter
Win32 API to accomplish the goal. But the potential problem of using
SetPrinter is that this function will globally change the default
printer settings. So for example if SetPrinter changes the tray 1 to
tray 2, while tray1's job hasn't fully completed yet (its normal if a
lot of jobs are in the queue), then SetPrinter will evetually change
the other existing jobs (belogn to tray 1) to tray 2 as well.

So.. as Microsoft suggested that, its good to use DocumentProperties
Win32 API to change printer's setting per application, rather globally
changing the default settings.

However, DocumentProperties requires me to rewrite HDC painting
functions...

So, thank you. Do you have any idea on how to install multiple drivers
(same printer driver) on a Windows 2000 server machine while rename
them? I tried, but windows prevented me from installing Multiple
drivers (currently, i guess only this solution will really work, except
rewrting printout function)

Any ideas?

 

[Jonathan West]

Hi Jonathan, thanks for the reply. But the article is using SetPrinter
Win32 API to accomplish the goal. But the potential problem of using
SetPrinter is that this function will globally change the default
printer settings. So for example if SetPrinter changes the tray 1 to
tray 2, while tray1's job hasn't fully completed yet (its normal if a
lot of jobs are in the queue), then SetPrinter will evetually change
the other existing jobs (belogn to tray 1) to tray 2 as well.

So.. as Microsoft suggested that, its good to use DocumentProperties
Win32 API to change printer's setting per application, rather globally
changing the default settings.

However, DocumentProperties requires me to rewrite HDC painting
functions...

So, thank you. Do you have any idea on how to install multiple drivers
(same printer driver) on a Windows 2000 server machine while rename
them? I tried, but windows prevented me from installing Multiple
drivers (currently, i guess only this solution will really work, except
rewrting printout function)

Any ideas?

You could use Karl Peterson's PrnInfo code (go to
http://vb.mvps.org/samples/) and use that to do a regular check to see
whether all print jobs have spooled off the printer before changing the
settings and sending a new print job.


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org