.xlsx output

[Diane Camerer]

Version 9.1D

I have a few custom report that creates a .xlsx file. It runs during a nightly batch routine and then gets emailed out to users. It has worked well for a few years now with no problems, but the last few weeks there have been issues where the completed report only has the reported data in the worksheet. Headers and formatted areas, grid lines, highlighted cells, etc are not there.
It does not happen on every run for every report, but it has effected almost all of them at least once in the last couple weeks.
I have been told that the program/process uses the default printer to pick up fonts and if the printer is not hooked up or available it that could cause a problem in building the worksheet. Is that true?
Or, Can anyone give me an idea what may be the problem?
I have never gotten an error message in the batch log file when this happens except once - and this is it
Error occurred while accessing component property/method: centerheader.
Unable to set the CenterHeader property of the PageSetup class
Error code: 0x80020009 spreadsheet-open prog/assy-Phr.p (5890)
Unable to set com-handle property. (5677)
Not sure if this is a help or not.
ALSO, any change that was made would be that we added a process in the batch that PRINTS a report to the default printer.
Not sure if that would have anything to do with it or not, but that is the only change that occurred right before this started to happen.
Any ideas would be appreciated,
Thanks, Diane

[Rob Willoughby]

Quick fix - remove (comment out) the print section that was recently added, and see if the problem goes away.

Com-objects can be affected by user interaction or the printer not setup the same as you have it in your code. Printing programmatically can add a whole set of new ways to break your program. I know, since we sometime print from RF scanners and tablets, and also generate XLSX files and email to users.

[Kurt Gunderson]

Unable to set the <WHATEVER> property of the PageSetup class.

In my experience, this PageSetup error message is almost a dead giveaway
to there not being a default printer set... usually in a Citrix environment.

Regards,
K.

--
Kurt Gunderson
Consulting Systems Architect

"I have never in my life learned anything from any man who agreed
with me." *Dudley Malone*

[Rob Willoughby]

Too bad there's not an odbc setting for printers - or an IP printer you could reference that accepts the Generic driver. Just thinking out loud.

[Paul Roberts]

Diane,

I would first check that the Server version (or machine that the batch file is executed on) has the same Excel installation as the clients.

Then check that the server has an installed printer, as it uses the default printer to determine font formatting appropriate to the data.

Another simple check is to confirm you have a printer installed before attempting to set any print related items, which can cause errors if no printer is installed on the machine accessing the com object.

ASSIGN chPageSetup = chExcelApplication:ActiveSheet:PageSetup NO-ERROR.
IF VALID-HANDLE(chPageSetup) THEN
DO:
/*print setings*/
END.

If none of this helps and you find the solution, please let me know.

Regards

Paul

----Original Message-----
From: postm...@peg.com [mailto:postm...@peg.com] On Behalf Of Diane Camerer
Sent: 12 June 2014 13:17
To: p...@peg.com
Subject: .xlsx output

[Jeff Pilant]

Rob Willoughby writes:
> Com-objects can be affected by user interaction or the printer not setup
the
> same as you have it in your code. Printing programmatically can add a
whole
> set of new ways to break your program. I know, since we sometime print
> from RF scanners and tablets, and also generate XLSX files and email to
users.

Excel uses the current printer to determine things like paper size
available, fonts available, and so on. Having NO printer selected causes
easily spotted errors like what was see the one time. Having a DIFFERENT
printer selected can cause font issues, availability of features, and so on,
like you are seeing. I have seen COM object failures when used with a less
capable printer selected. No code change, in fact, the code was in the
middle of a run when the default printer was changed. [I.e. push a button
for a report. Wait. Receive it. Change printer. Repeat and fail.]

Change your code. Do NOT assume the correct printer is selected. Add code
to save the current printer, then select the correct one. When the report
is done, reset the printer back to the saved value. Have an error exit if
the needed printer is not available. There are system variables with the
current printer and available printers. Interrogate them. For now, to
prove this is the issue, just add the current printer as an extra line of
text in the email after the body. Then if the failed reports have a strange
printer, you know why it fails and can fix.

/jeff

http://www.linkedin.com/pub/jeff-pilant/65/1a/462/