As mentioned in my previous post I have created a script that creates a Excel xls file for reporting. (VBScript) In debug it works fine but not when it runs as a service. I read this on msdn which worries me and I thought the OL pros might be able to help.
It is the limitation to office design to server-side automation.
Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.
Is there a way around this?
EDIT: The line of code in question is as follows. objWorkbook.Saveas "D:\ClientName\Payslips\Proofs\Client - " & aDate, 51
Not really. What you’re doing, behind the scenes, is launching the Excel application and essentially running the equivalent of a Macro. Excel was never designed to be automated like this, especially by a service like Workflow that may be running with local system privileges. That means Excel may not be completely thread-safe, which could potentially cause memory corruption issues.
Note that the above is the theory of it all. Excel may very well run properly in this context, but the simple fact that Microsoft discourages this type of use should alert you to the fact that they probably know there are some vulnerabilities in doing so.
My question to you is: do you absolutely need an XLS file for reporting or can’t you simply generate a CSV file, which wouldn’t require you to call Excel under the Hood?
Apologies for taking a while to respond, I was driving home from work.
Thanks for your insight. Unfortunately I have a new client and I have already shown them the Excel file report to which they approved for their upcoming mailing, so going back would be embarrassing. The script also adds formatting to the xls with cell borders thus creating a table, something a csv wont allow been text based.
I’m not sure what to do now. Never would I have thought of this restriction with MS and automation software.
Can I save the script as a VBS file and run it externally from a Workflow service? I would have to write the data/PostScript totals to a file and modify the script to read said file to use those totals in the xls. Or is this just going to result in the same issue? I’m treading uncharted territory here, so excuse silly suggestions.
Edit2: If I could create a HTML email with a table in the body of the email with totals etc. That would be saving face but the send email plugin is text only. Unless I create a email template…
I never new that and read the user guide many times without noticing the HTML portion. I made a table in Connect designer and formatted it the way I wanted, then copied the source to the email plugin, however once the email is sent the formatting is lost. This is the html source I used.
Well as you just found out, HTML emails are finicky… No two email clients process them the same way and each client has its own set of restrictions and peculiarities.
At this stage, you might be better off generating an email template, since the Connect Designer has been tailored to minimize the number of potential issues with email clients. You’ll also have much better control of any future changes than if you just use a text editor.
Yeah I basically started over with creating the table. Did the header and then the rows/cells. It is looking a lot better than my first attempt. I might explore the email template but this table will always be the same for this job.
Anyways, thanks for your advice. Very much appreciated.
I basically started over with creating the table. Did the header and then the rows/cells. It is looking a lot better than my first attempt. I might explore the email template but this table will always be the same for this job.