PowerBASIC Peer Support Forums - Excel API statement for opening Excel 2007 formatted file in Excel 2003

PowerBASIC Peer Support Forums (http://www.powerbasic.com/support/pbforums/index.php)

- Programming Microsoft Office (http://www.powerbasic.com/support/pbforums/forumdisplay.php?f=43)

- - Excel API statement for opening Excel 2007 formatted file in Excel 2003 (http://www.powerbasic.com/support/pbforums/showthread.php?t=50000)

Excel API statement for opening Excel 2007 formatted file in Excel 2003

Please ignore the first post in this thread. I am adding this note, rather than deleting or changing the post, so that the subsequent replies in this thread still have context.

However, the problem I reported turned out to be bogus. The code I presented in the original post works properly to open xlsx files on Excel 2003 with the 2007 compatibility pack.

It turned out that my program was reusing a portion of some older code (which was written prior to my need to support xlsx); that older code was manipulating the file prior to attempting to open it in Excel. It was those manipulations on the xlsx file that made the file unopenable in Excel. (Since my program manipulated the temporary file copy, the original file was intact and always worked during my manual attempts to open it.)

--------------
I am using Excel 2003 with the 2007 compatibility pack, so I am able to open .xlsx files by double-clicking on the file in Explorer or through the file / open menu. When I manually open files, they automatically go through the converter.

However, this conversion does not automatically happen when I open an .xlsx file through COM automation.

See the excerpt from my code below. The code opens the file successfully in Excel 2003 if the vInFile variable is an xls file, but Excel generates a "this file is not in a recognizable format" error dialog box if vInFile is a xlsx.

What should I change in my code to allow it to open and convert a xlsx file?

By the way, I'm using the Excel include file that comes packaged with PowerBASIC 9 in
...\PBWin90\Samples\Com\Excel\excel.inc

Code:

DIM oExcelApp       AS Int__Application

DIM vInFile             AS VARIANT

DIM vExcelWorkbook      AS VARIANT

 

' Open an instance of EXCEL

oExcelApp = ANYCOM $PROGID_Application

OBJECT LET oExcelApp.Visible = vTrue

OBJECT CALL   oExcelApp.WorkBooks.Open(Filename = vInFile, UpdateLinks=vFalse, ReadOnly=vFalse) TO vExcelWorkBook

PS - Actually, the whole purpose of this program is to automate opening an xlsx and doing a "save as" xls to convert the file format (for a different program that does not have a driver for utilizing xlsx files). Are there any other Excel API calls that would allow me to convert/save the file without even opening it?

Those MS-Office "compatibility packs" are very limited in what they offer. I would be surprised if there were anything resembling a full API which would allow you open Excel-2007 format using Excel 2003. I did some searching on MSDN this AM and could not find anything re an "object model" for that.... but you know, I have that pack installed here, let me see if I can find something with the PB COM browser in the "Registered" things... nah, I can't find anything.

But to deal with your challenge...

Quote:

Actually, the whole purpose of this program is to automate opening an xlsx and doing a "save as" xls to convert the file format

...

I am using Excel 2003 with the 2007 compatibility pack..

Seems to me the solution has to be, "Upgrade to Excel 2007 on the computer which is expected to support both 2003 and 2007 format."

"Save as" for Excel 2007 @ http://msdn.microsoft.com/en-us/libr...ffice.12).aspx.... gives you a link to the available "save as" formats here : http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

... so I know that will work.

BY THE WAY... did you even TRY the OLEDB via ADO method I suggested?

Let me see if that works on MY Excel 2003 system with compatibility pack against a *.XLSX file. I will get back to you.

MCM

Hmmm... I got the same errors as I found here:

http://forums.asp.net/t/1169005.aspx/1

Code:

ADO Demo begins at 09:45:15 on 03-31-2012

Connection string 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Edi Scope Document.xlsx;Extended Properties="Excel 10.0;HDR=Yes;IMEX=1";'

Query:



  Select * from [EDI Transactions]



AdoExecuteSelect returns -2147352567 ERROR

ERROR MESSAGE:open method error: Could not find installable ISAM.

** END OF REPORT **

Code:

ADO Demo begins at 09:44:27 on 03-31-2012

Connection string 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Edi Scope Document.xlsx;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";'

Query:



  Select * from [EDI Transactions]



AdoExecuteSelect returns -2147352567 ERROR

ERROR MESSAGE:open method error: External table is not in the expected format.

** END OF REPORT **

Code:

ADO Demo begins at 09:51:29 on 03-31-2012

Connection string 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Edi Scope Document.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";'

Query:



  Select * from [EDI Transactions]



AdoExecuteSelect returns -2147352567 ERROR

ERROR MESSAGE:open method error: DispInfo Code '-2146824582'

DispInfo Context '1240655'

DispInfo Description 'Provider cannot be found. It may not be properly installed.'

DispInfo Help(file) 'C:\WINDOWS\HELP\ADO270.CHM'

DispInfo Source'ADODB.Connection'

End of IDISPINFO error messages for DISP_E_EXCEPTION

** END OF REPORT **

Maybe it's time to upgrade that Excel, huh?

I suppose I could try it on Laptop_1... that has Excel 2007 installed. Not today, it's not that important to me.

MCM

Thanks Mike. My problem is solved. I edited the original post to include the necessary corrections; since there is some misleading information in that post, I did not want any new readers to have to get this far in the thread before seeing the correction.

Quote:

MCM: Did you even TRY the OLEDB via ADO method I suggested?

Thanks, but that approach was unnecessary for the immediate problem and not suitable for my more generic concern. In the same thread where you made that suggestion (which was to workaround the problem I was having with the Excel ODBC driver via SQL), we actually discovered that the driver was mapping the period to the hash symbol in field names, and that portion of code is working now that I included this mapping. For the more general problem of my desire to find a better way to read Excel files without needing to worry about oddities in Excel files I may receive in the future, Edwin pointed out that "Excel via ADO can have a problem on the first row", and I do not want to trade one type of unexpected problem for a different type.

Quote:

MCM: Maybe it's time to upgrade that Excel,

Not quite. I have office 2003 and office 2010 running on my personally owned PC. But at work, IT will only one instance to be installed. There is some 2003 functionality critical for my needs that was removed in office 2007/2010. I muddled along with office 2007 for two months, trying all the third-party add-ons I could find, before giving up and rolling back to office 2003. For the rare occasions that the 2007 compatibility pack is not sufficient, I can run a corporate Citrix version of 2007. [Before someone asks what functionality was removed, I'll answer that question. It has to do with the reduced customizability of the user interface. If someone takes full advantage of the customizability available in 2003 versus the customizations available in 2010, 2003 can be tuned to to that particular persons usage pattern to allow the user to do the same operations with fewer mouse clicks and keystrokes. This is critical to me due to repetitive stress injuries.]

Ok, so I'm lazy. I did not remove all the extra code that I had in the program
for other purposes and add all the neat comments. But this works with
PB CC v6.03 and Excel 2003 with 2007 compatibility pack under XP.

The attached zip has Excl9.bas & Excl9.exe & Excelcc6.inc ( too lazy to
figure out the new configuration of COM browser ... just fixed whatever the
compiler complained about in the Excel.inc from CC v6 samples ).

Brings up a File picker dialog for the spreadsheet. It will open an XLSX file.
Reads all the data into arrays and writes them to a text file. Only 1 sheet
shows up. 2 rows and a bunch of data.

It's kinda late Saturday nite and I've been up since 3am.

Have fun.

Updated zip file in previous message. Slight change to INC name.
Removed most of unused variables.
Generated new Excel INC file using my version of Excel.

PB, Nice going with saving the COM Browser settings in the output file!!:goldcup: