Excel Save Issue

[waynesingh]

Hi All,

I am using Excel to display some report information. Basically the user selects Excel as their output and it opens Excel and pushes a URL into it, as the reports are created using J2EE. This works fine, including the file that it saves. Then if you leave this Worksheet open and run a second report in Excel, it opens a second Excel window, and saves it with the correct name, but when you open this second report file, it is a copy of the first report. I have included the Procedure below and am hoping that someone can show me what I am doing wrong. As you can see I Release the Objects at the end, but maybe there is something else I need to do?????

PROCEDURE ShowExcel:
DEF INPUT PARAMETER ipURL AS CHAR NO-UNDO.
DEF INPUT PARAMETER ipFile AS CHAR NO-UNDO.

DEF VAR lvPath AS CHAR NO-UNDO.

def var chSpreadsheet as com-handle.
def var chWorkBook as com-handle.

RUN sys/spget.p (INPUT "REPORT",
INPUT "EXCELPATH",
OUTPUT lvPath).

IF lvPath = "" THEN
DO:
MESSAGE "REPORTS-EXCELPATH has not been setup in System Parameters."
VIEW-AS ALERT-BOX ERROR TITLE "System Parameter No Set...".
RETURN NO-APPLY.
END.

ipFile = lvPath + ipFile.

CREATE "excel.Application" chSpreadsheet CONNECT NO-ERROR.
if error-status:error then
do:
CREATE "excel.Application" chSpreadsheet NO-ERROR.
if error-status:error
then do:
message "Unable to Connect to Microsoft Excel.".
return error.
end.
end.

chSpreadSheet:Workbooks:OPEN(ipURL) NO-ERROR.
chSpreadSheet:VISIBLE = TRUE.
chWorkBook = chSpreadSheet:Workbooks:ITEM(1) NO-ERROR.
chWorkBook:SaveAs(ipFile,43,,,,,) NO-ERROR.

RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chSpreadSheet NO-ERROR.

END PROCEDURE. /* ShowExcel */

the spget.p call is just to get a system parameter (the save file path).

I even tried putting RELEASE OBJECT statements above the CREATE statement just to be sure, but had no effect.

Thanx to anyone that offers any help, as I really need it at the moment.
Cya's.

 

[waynesingh]

Hi All,

I managed to figure it out (I usually end up answering my own questions ). I had to change the procedure to the one below.

PROCEDURE ShowExcel:
DEF INPUT PARAMETER ipURL AS CHAR NO-UNDO.
DEF INPUT PARAMETER ipFile AS CHAR NO-UNDO.

DEF VAR lvPath AS CHAR NO-UNDO.
DEF VAR l-WSheets AS INT NO-UNDO.

def var chSpreadsheet as com-handle.
def var chWorkBook as com-handle.

RUN sys/spget.p (INPUT "REPORT",
INPUT "EXCELPATH",
OUTPUT lvPath).

IF lvPath = "" THEN
DO:
MESSAGE "REPORTS-EXCELPATH has not been setup in System Parameters."
VIEW-AS ALERT-BOX ERROR TITLE "System Parameter No Set...".
RETURN NO-APPLY.
END.

ipFile = lvPath + ipFile.

RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chSpreadSheet NO-ERROR.

CREATE "excel.Application" chSpreadsheet CONNECT NO-ERROR.
if error-status:error then
do:
CREATE "excel.Application" chSpreadsheet NO-ERROR.
if error-status:error
then do:
message "Unable to Connect to Microsoft Excel.".
return error.
end.
end.

l-WSheets = chSpreadSheet:WorkBooks:COUNT + 1.
chSpreadSheet:Workbooks:OPEN(ipURL) NO-ERROR.
chSpreadSheet:VISIBLE = TRUE.
chWorkBook = chSpreadSheet:Workbooks:ITEM(l-WSheets) NO-ERROR.
chWorkBook:SaveAs(ipFile,43,,,,,) NO-ERROR.

RELEASE OBJECT chWorkBook NO-ERROR.
RELEASE OBJECT chSpreadSheet NO-ERROR.

END PROCEDURE. /* ShowExcel */

You will notice the extra lines are the ones for l-WSheets, firstly I declare it as an integer, then I get a count of how many worksheets we already have open, then create the new Worksheets as the next one.

Anyway hopefully this will help other people too.
Cya.