(1) I have a Macro defined in Excel (say test.xls) as 'PrintSheet()'
which uses the following code:
Workbooks(1).Sheets(1).Activate
ActiveSheet.PrintOut
(2) I have already configured Acrobat Distiller as the default Printer
and doesn't prompt for file-names and should the printing behind the
screen and save the file in a predefined directory (PDF Port).
(3) I have a ASP script (say test.asp) that opens the above test.xls
and executes the macro PrintSheet from ASP on excel application that
was opened.
My Question is: Somehow, when I execute the above test.asp from IIS
(from a browsre), the Excel hangs and does not generate the PDF file
at all. However, when I put a button on one of the Excel sheets, open
the Excel application by itself, and clicks on the button to execute
'PrintSheet()' subrountine, then it works. Only when the Excel was
created and opened from ASP script, then it is not working.
The environmentis:
OS : Windows 2000
Acrobat : 5.0 version
Excel : Excel 2000
Please help. Also let me know if there is any better way of generating
PDF from Excel (either by using PDFWriter or so)...
Thank you. |
Request for Question Clarification by
mathtalk-ga
on
31 Oct 2002 22:54 PST
Hi, eti-ga:
Are you trying to open Excel on the IIS machine (server) or on the
browser machine (client)? There are lots of issues with trying to
execute Excel on an IIS server process, and Microsoft pointedly
discourages this. I have some experience with automating the Excel
engine on a server (though not from ASP in IIS, but enough to put
caution in my steps!), and also with doing document automation on the
client side (which Microsoft prefers because, among other things, it
gives a user context for the Office software to run in).
regards, mathtalk-ga
|
Clarification of Question by
eti-ga
on
31 Oct 2002 23:59 PST
Hi mathtalk,
Yes, this whole thing gets executed on the server side (IIS). And
browser sends only one ASP request to the server and it is required to
execute on Server side since we have one single installation of any
required software such as Excel, Acrobat, etc and the client shouldn't
worry about any of these. Once the Acrobat (PDF) file was generated
from Excel content on server since server has all the DB data
accessibility from ASP, the customer would see only the PDF file where
he/she needs only Acrobat Reader to view the file.
Please note that this is working as expected on Windows ME and not on
Windows-2000. That was the reason why I was seeking help to resolve
this for Windows-2000 or suggest any better way of acheiving this
either by using PDFWriter and so on. I hope it clarifies. If not,
please let me know.
Thank you.
|
Request for Question Clarification by
mathtalk-ga
on
01 Nov 2002 09:27 PST
Please explain a little more about what has to be done to provide the
Excel data for the PDF representation. Is the source Excel document
"static" or is it essentially performing "value add" computations
parameterized by the users request?
If Excel is really an essential part of the process (rather than a
passive data store or convenient VBA code engine), then we might look
at a "server friendly" version of Excel that Microsoft provides (if I
remember correctly).
If Excel is not essential, or can be treated as an ODBC data source,
then we can look at the problem as a "report writing" requirement;
shouldn't be too hard ! ! :-)
regards, mathtalk-ga
|
Here is Microsoft's KnowledgeBase article on general
requirements/issues with server-side automation of Office
applications:
[KnowledgeBase Article Q257757]
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q257757&
A fundamental concern is that Office components are generally
non-reentrant and single-threaded apartment components, to use the COM
description. This means that they will not scale well into the
multi-user environments typical of Web applications.
This article further details the configuration of Office components to
run under a specified user account:
[KnowledgeBase Article Q288367]
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q288367&
For the specific problem of failing to print from an ASP script, which
may be the crux of your difficulty, see this article:
[KnowledgeBase Article Q184291]
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q184291
which explains that the System account contains no registry entries
for printing. Although not necessarily the recommended solution for
your needs, the article shows how to create printer entries for the
System account using Regedit.
I was unable to locate the Microsoft "server friendly" version of
Excel that I remember seeing last year at about this time, so it may
have been taken off their site. I will make inquiries on the
newsgroups...
regards, mathtalk-ga |
Request for Answer Clarification by
eti-ga
on
01 Nov 2002 15:31 PST
Hi,
The process I have is like this:
(1) User clicks on a button on Browser that executes an ASP script on
IIS
(2) That ASP creates an Excel Object using Server.CreateObject("...")
(3) ASP also fetches some data from backend database and populates
some of the cells and sheets in Excel that was opened above
(4) ASP executes some subroutines defined in Excel VBA by calling them
from ASP which will do some calculations, graphing, etc.
(5) ASP executes another subroutine defined in Excel to generate PDF
file for a seleted WorkSheet in Excel by using ActiveSheet.PrintOut
In summary, the flow is ASP-->Excel-->Acrobat-PDF-File.
I really appeciate your investigation and helpful information.
However, I need a straight answer to resolve my problem, if possible.
Thank you.
|
Clarification of Answer by
mathtalk-ga
on
01 Nov 2002 22:03 PST
Hi, eti-ga:
Here's my understanding of your situation, your options, and what I
would do in your position.
You are using Microsoft Excel as a reporting engine on the IIS server,
automated through ASP script that populates an Excel spreadsheet
already primed with a certain amount of formulas and VBA code to
create graphics. Your goal is to create a PDF document to stream down
to the user's browser, and you get that by "printing" the Excel
document to Adobe's Acrobat Distiller.
There is an immediate issue, probably to do with this printing step,
that causes the process to hang. You may already have done the
necessary testing to confirm that this is the case, but if not it
would be relatively quick to modify your ASP script so that instead of
printing, it instructs Excel to save and close the completed workbook,
and then to exit the application. Success in doing these things (as
confirmed by the presence of the new workbook in the designated server
file directory) would confirm that the immediate problem lies with
printing.
So it would understandably be attractive to deal with this immediate
problem as briefly as possible. Along those lines would seem to lie
the discussion in:
[KnowledgeBase Article Q184291]
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q184291
which explains how to create printer entries for the System account
using Regedit. Naturally you would want to create an entry
corresponding to the existing "printer" setup (presumably now under
your "interactive" usercode) that treats Acrobat Distiller as a "print
to file" mechanism.
As another Microsoft article points out:
[KnowledgeBase Article Q257757]
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q257757&
there are concerns with doing server-side automation of Excel that go
beyond the printing difficulty. These include licensing issues (none
of the client desktops will have a license to use Excel), the lack of
a user identity connected with the running instance of Excel (the
System account versus an individual user account), difficulty in
noting and resolving error conditions due to invisiblity of the
process to the desktop, scalability (what volume of reports will need
to be produced in this fashion?), robustness (because of many design
features of Excel that hinge on user interaction, e.g. installation of
features "on demand" of first time use), and security.
I have done a project that involved server-sider creation of Excel
documents, albeit using MS Access as the code engine rather than
IIS/ASP. I've also done projects in which creation of Excel, Word,
and Crystal Reports documents was shifted to the client side, both in
desktop and Web applications.
So to me the difficulties warned about in the links I've passed on to
you have a known context. Certainly if the Web application you are
working on is expected to handle a burden of simultaneous requests, I
would seriously look at using a tool other than Excel to create these
reports.
That that is not an option, for whatever reason, the most important
step that we could take to improve the reliability of the server-side
Excel automation is to associate a real user identity with its
execution. That last Microsoft article above outlines three ways to
do this, of which the most attractive is probably to associate the
execution with the same user ID currently logged onto that server.
This would presumably be yours (or whoevers the Acrobat Distiller
printer is defined for), so that you would be required to stay logged
in on that server.
Directions for making the necessary configuration are found about
two-thirds of the way down this site:
[KnowledgeBase Article Q288366]
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q288366
This approach (of the three; see previous article for links to two
other ways to associate a user account with the Excel execution) is
best for debugging purposes as it provides visibility for unexpected
errors, etc. on the desktop (so long as the user remains logged into
the server).
So I think that would be the direction I would go in immediately,
while evaluating the cost/benefits of doing without Excel altogether.
regards, mathtalk-ga
|
Request for Answer Clarification by
eti-ga
on
03 Nov 2002 01:51 PST
Hi mathtalk,
I really appreciate your explanation and thanks for that. One last
thing, if you can help me out, that would be really great.
If I try to click on a button on Excel workbook that executes
ActiveSheet.PrintOut (please note that there is not ASP involved as
part of automation), I get an error message saying that:
"There was an error found when printing the document "t.xls" to
"C:\pdfdocs\*.pdf". Do you want to retry or cancel the job?"
In what situation, we get this error?
May be the price that I paid is less for you, but I would like to get
any hint or some suggestion about the issue. Please help. Thank you.
|
Clarification of Answer by
mathtalk-ga
on
03 Nov 2002 08:57 PST
I have two concrete ideas about the cause of the error, which I list
in order of likeliness:
1) The error message suggests that the printer driver (Distiller?) is
trying to create a file named *.pdf. The filename is missing; when
you create a "print to file" printer option, you need to supply the
filename there. It would be nice if the printer driver would by
default "inherit" the filename to agree with the file being printed,
e.g. t.xls goes to t.pdf, but evidently that is not the case (I've had
this problem myself). Check the documentation for Distiller about how
to assign the filename.
2) The second idea is to make sure that the directory exists, there
are no permission restrictions to prevent the printer driver from
writing into that directory, and that there is no "read only" file
already there with the same name as the one to be created.
Here are some further thoughts about diagnosing the problem,
From the description I'd suspect this error message (in a popup dialog
box?) is coming from the printer driver, rather than from Excel
itself.
Perhaps you can use Task Manager to determine whether Excel or
Distiller is putting the message out? Or perhaps it is simply a
matter of looking carefully at the title of the dialog box. Or,
perhaps you can open the Printers "menu" under Settings (or from the
system tray, if the icon is active there) and see what sort of print
jobs have been created).
best wishes, mathtalk-ga
|
Request for Answer Clarification by
eti-ga
on
03 Nov 2002 11:14 PST
Thank you.
Regarding '*.pdf', Acrobat Distiller automatically puts that as part
of PDF-Port-Path when we automate the printing job. So, it is required
by Distiller. And the expectation is to have t.pdf generated for
t.xls.
|
Clarification of Answer by
mathtalk-ga
on
03 Nov 2002 15:20 PST
I do not have Acrobat Distiller, so perhaps I'm not the expert to help
you with this new problem. However I have reviewed what the Acrobat
Distiller API Reference:
http://partners.adobe.com/asn/developer/acrosdk/docs/dst_api.pdf
and the Acrobat Distiller Control Interface Reference:
http://www.wsl.ch/relics/rauminf/documents/Fileformats/Acrobat/5158.distapi.pdf
have to say about the output file name setting, in the job options.
The setting, as far as I can tell, should simply be the directory
location, with no "*.pdf". Perhaps this is the way you have
configured the output file name, i.e. "C:\pdfdocs". An example of
this setting is given toward the end of the control interface
documentation above.
Assuming this is how you have set the outputfile property on job
options, I would proceed with my other suggestions. If you want to
pursue this new question further, I think it would be fair to create a
new thread, so that other experts may have an opportunity to make
suggestions.
regards, mathtalk-ga
|
Clarification of Answer by
mathtalk-ga
on
04 Nov 2002 17:53 PST
Hi, eti-ga:
Thanks for taking the time to rate my answer, even though I was unable
to completely solve your problems. It means a lot to me.
I found this Adobe Knowledge Base article that discusses a
"permissions" issue (with a shared folder under NTFS) with similar but
not identical symptoms to your printing problem:
http://www.adobe.com/support/techdocs/1ba12.htm
It gives, if I recall, four different workarounds for this issue (Full
Access rights for the interactive user are apparently not "honored" in
some respects).
regards and best wishes, mathtalk-ga
|
