PowerBASIC Peer Support Forums
 

Go Back   PowerBASIC Peer Support Forums > User to user Discussions > Special Interest Groups > Programming Microsoft Office

Programming Microsoft Office User to user discussions about programming Microsoft Office applications.

Reply
 
Thread Tools Display Modes
  #1  
Old Mar 31st, 2012, 05:02 AM
S Stamp S Stamp is offline
Member
 
Join Date: Dec 2006
Location: New Jersey, USA
Posts: 192
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?

Last edited by S Stamp; Mar 31st, 2012 at 02:48 PM.
Reply With Quote
  #2  
Old Mar 31st, 2012, 11:33 AM
Michael Mattias Michael Mattias is offline
Member
 
Join Date: Aug 1998
Location: Racine WI USA
Posts: 37,269
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
Reply With Quote
  #3  
Old Mar 31st, 2012, 12:02 PM
Michael Mattias Michael Mattias is offline
Member
 
Join Date: Aug 1998
Location: Racine WI USA
Posts: 37,269
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
Reply With Quote
  #4  
Old Mar 31st, 2012, 02:51 PM
S Stamp S Stamp is offline
Member
 
Join Date: Dec 2006
Location: New Jersey, USA
Posts: 192
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.]
Reply With Quote
  #5  
Old Mar 31st, 2012, 10:27 PM
Paul D. Elliott Paul D. Elliott is offline
Member
 
Join Date: May 2002
Location: some hill in WV
Posts: 1,136
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.
Attached Files
File Type: zip excelrdr.zip (94.2 KB, 36 views)

Last edited by Paul D. Elliott; Apr 1st, 2012 at 10:39 AM. Reason: updated zip file
Reply With Quote
  #6  
Old Apr 1st, 2012, 10:43 AM
Paul D. Elliott Paul D. Elliott is offline
Member
 
Join Date: May 2002
Location: some hill in WV
Posts: 1,136
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!!
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 08:53 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 1999-2011 PowerBASIC, Inc. All Rights Reserved.
Error in my_thread_global_end(): 1 threads didn't exit