Rick100Lynne
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.Applic ation")
xlworkbook = oExcel.Workbooks.Open(ALLT RIM(insert _config.lo cation)+"\ "+m_file)
xlworkbook.SaveAs(ALLTRIM( insert_con fig.locati on)+"\"+LE FT(ALLTRIM (m_file),L EN(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
PROCEDURE excelconv
oExcel = CREATEOBJECT("Excel.Applic
xlworkbook = oExcel.Workbooks.Open(ALLT
xlworkbook.SaveAs(ALLTRIM(
*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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I personally use CSV files which are far much less headaches. With Office 2007 and now 2010, automation has been a headache.
ASKER
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.
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.
ASKER
I changed 43 to xlExcel9795. I got:
Variable 'XLEXCEL9795' is not found.