Link to home
Start Free TrialLog in
Avatar of Rick100Lynne
Rick100LynneFlag for United States of America

asked on

Foxpro and using Excel 2010 as an object

Part of my main Foxpro program converts Excel files to Excel 97/95 to be appended later on in other parts of the program. Here is my conversion code:

PROCEDURE excelconv
oExcel = CREATEOBJECT("Excel.Application")
 xlworkbook = oExcel.Workbooks.Open(ALLTRIM(insert_config.location)+"\"+m_file)
 xlworkbook.SaveAs(ALLTRIM(insert_config.location)+"\"+LEFT(ALLTRIM(m_file),LEN(ALLTRIM(m_file))-4)+".xls",43)
 *RELEASE oExcel
xlworkbook.saved = .t.
xlworkbook.close
oExcel.quit
RETURN

This has worked for clients that have 2003 and 2007 Excel.

I just upgraded to 2010 and was runing a test and got this error on the "saveas" line:

OLE IDispatch exception code 0 from Microsoft Excel: Unable to get the SaveAs property of the workbook class..

I attempted to change the file format to -4143 instead on 43. The program processed the SaveAs OK, but the file gave me an "Invalid Microsoft Excel File format" when I attempted to append.

I appreciate your help on this. Thanks
ASKER CERTIFIED SOLUTION
Avatar of shahzadbux
shahzadbux
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Olaf Doschke
Olaf Doschke
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick100Lynne

ASKER

Shahzadbux,
I changed 43 to xlExcel9795. I got:

Variable 'XLEXCEL9795' is not found.
I personally use CSV files which are far much less headaches. With Office 2007 and now 2010, automation has been a headache.
I went back to using 39 for the file format and it worked. There was a excel file that didn't like 39 so I went to 43, but I'll have to revisit that when that file comes back.
xlExcel9795 is a constant which only is valid within Excel, not in foxpro, unless you define such a constant or variable.

It is 43 and if you don't define a constant in foxpro you can use 43, but -4143 is no replacement, it's xlWorkbookNormal in regard to Excels SaveAs() method.

Referring to http://fox.wikis.com/wc.dll?Wiki~ExcelConstants the value 39 you finally used refers to xlExcle5 or xlExcel7, both are 39:

...
#DEFINE xlExcel5 39
#DEFINE xlExcel7 39
...

Makes me wonder if and why Excel2010 does not support xlExcel9795 = 43 any more but does support Excel5/7 file types.

Bye, Olaf.