Programmatically saving excel 2010 files....

[stokefc22]

Morning All,
This is my first post so please go gently with me :blush1:.
We’re using open edge version 10.2b and I have a machine running vista.
The problem I have is as follows: -
I’m running a report via a background process that we developed in house. This report generates a load of data outputs it to excel, saves the file and then attaches to email and sends the email to the required people. Using Excel 2007 this works fine but the minute I try on it a machine using excel 2010 I have problems saving the file. I’m using the following line of code in an attempt to save the file where chrPathFile = “..\tmp\afile.xlsx”.

chWorkbook:SaveAs(chrPathFile,,,,,,).

I’ve read about the need to specify the file format in excel versions 2007 and above so have tried a combination of the following with no joy....
chWorkbook:SaveAs(chrPathFile,51,,,,,).
chWorkbook:SaveAs(chrPathFile,56,,,,,).
chWorkbook:SaveAs(chrPathFile,-4146,,,,,).
I’ve also tried naming the file “..\tmp\aFile.xls” with no joy either.
Please can somebody help me before I tear all of my hair out!!
Thanks in Advance

 

[rzr]

I'm on 10.2A / Windows 7

DEFINE VARIABLE chExcelApp   AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkBook   AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE chWorkSheet  AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE lAutoClose   AS LOGICAL    NO-UNDO INITIAL NO.

CREATE "Excel.Application" chExcelApp.

chWorkBook = chExcelApp:Workbooks:ADD().
chExcelApp:SheetsInNewWorkbook = 1.
chExcelApp:sheets(1):Name = "FIRST".
chExcelApp:Range("A1"):FONT:ColorIndex = 10.
chExcelApp:Range("A1"):FormulaR1C1 = "2nd Data".
chWorkBook:SaveAs("c:\temp\New.xlsx",,,,False,False,). 

IF NOT lAutoClose
   /* Open Excel for user access */
THEN ASSIGN chExcelApp:visible = true.
   /* Close Excel */
ELSE chExcelApp:QUIT(). /* Doesn't work! */

RELEASE OBJECT chWorkBook  NO-ERROR.
RELEASE OBJECT chExcelApp  NO-ERROR.
RELEASE OBJECT chWorkSheet NO-ERROR.

 

[stokefc22]

Cheers rzr,
Is it excel 2010 you are using??

I've tried this but still no joy... it's gotta be something stupid!!

 

[rzr]

yes, I am using MS Office Standard 2010 (32-bit).

 

[Osborne]

Does adding NO-RETURN-VALUE at the start solve the problem?

NO-RETURN-VALUE chWorkbook:SaveAs(chrPathFile,56,,,,,).

If not, it could be the path name and it may need to be in the form of “C:\tmp\aFile.xls”.

 

[rzr]

btw.. what is the error message you get ??

 

[stokefc22]

Hi Osborne,
Unfortunately the no-return-value has no effect either. Apologies but I gave dud info in the description of the issue, the path is indeed of the form c:\tmp\afile.xls. I have managed to get this to work when running the program independently so it seems the problem is something to do with running it from the background process.......
Thanks for your thoughts..

 

[stokefc22]

The error message is as follows rzr...

Error occurred while accessing component property/method: SaveAs.
SaveAs method of Workbook class failed
Error code: 0x80020009 ExcelSaveCloseExit utils/ExclWrit.p (5890)

The knowledge base does have a resolution for this but that doesn't solve it either!!

 

[stokefc22]

I'm pleased to say I have found the solution..... as thought it was something ridiculous!! The machine with the 2010 version of excel is a 64 bit machine running windows server 2008. It seems that in this version of windows there is an empty folder that no longer exists.. create this folder and away you go it works!!!
See the link below for more details...
http://social.msdn.microsoft.com/Fo...e/thread/b81a3c4e-62db-488b-af06-44421818ef91

Thanks for your help guys it's only taken about 2 days to figure it out

 

[rzr]

would have never guessed !!!... good work...