Link to home
Start Free TrialLog in
Avatar of cmrobertson
cmrobertsonFlag for United States of America

asked on

VB6 read .xlsx file using ADO

I have an Excel file that I download and cannot control its format (it is saving as xls, but I have confirmed its really an xlsx and renamed it) I need to open it and query some records. My ADO open that works on an xls file does not work here. How can I read this file programmatically in vb6 and perform a query to get only the records I need?
SOLUTION
Avatar of Norie
Norie

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 cmrobertson

ASKER

I am not sure how to go about setting up the Microsoft ACE OLEDB I have looked in the Data Source settings and do not see it as an option, if I just use it in the connect statement I get an error. "Provider cannot be found"
Avatar of Norie
Norie

How did you use it in the connect statement?

What provider were you using?

If it was JET then you should just be able to replace it.
I did downoad the ACE software and I thought installed it but I am still getting the "Provider not found" error
Can you post what you used for the connection string?
cnn.open "Provider=MProvider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=B:\Projects\reports\Interface Report.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Where does =MProvider come from?

I've never seen that in a connection string

Try removing that.
cnn.open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=B:\Projects\reports\Interface Report.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 

Open in new window

sorry that was a pasting error I have what you do
wow! I think I messed it up in my project too, I got by the connection (I think) it's failing opening the sheet into the recordset. Does opening the file with ACE change that aspect? Should I open a new question for that?
How is it failing exactly?
it says the access database engine cannot find the object, I am trying to select by column from the sheet name "InterfaceReport" being the sheet name and column 22 is how I am looking to filter the results.
Set rst = cnn.execute("SELECT * FROM [InterfaceReport$] where f22 = 'Assigned'")
This format worked when I was reading an xls. I tried without the $
Does it work without the criteria?

Also, is f22 definitely the column name?
Without the criterea it has the exact same error, I think the hint is in "access database engine" I wonder if its not retaining its sheet properties. The only exampli I saw created a named selection, I do not have that as an option.
I don't think I'm actually getting the file to open at all (I had the .xls extension wrong too so once I corrected it I am back to the original error I have had trying to open the file) "External Table not in the expected format" The file downloads as an .xls and i cant open it with jet - so I read it into notepad ++ and it is indeed xml so I changed the extension name to .xlsx. Either way I am getting the "not in expected format" message.

I have it right below the file itself and the connection statement are both .xlsx

cnn.open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=B:\Projects\\reports\InterfaceReport.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

and as I said I am getting "External Table not in the expected format"
ASKER CERTIFIED SOLUTION
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
With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0;"
    .ConnectionString = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};" & _
    "DBQ=" & sFileName & "; ReadOnly=False; IMEX=1;"
    .open
End With

This returns cannot find Installable ISAM
Why have you added in 'Driver={Microsoft Excel Driver...}'?

That's for an ODBC connection.

Can you attach the file, before and after you've changed the file extension?
Unfortunately I cannot attach the file (sensitive data, not mine to share). I download this file from an intranet site and last week it opened without issue using the following string.
cnn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\reports\testfile.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Set rst = cnn.execute("SELECT * FROM [Sheet1$]")
I can create a file and open it with this connection as well (Excel 2003).
The site defaults the filename extension to .xls, but then I started getting the initial error that it wasn't in an expected format. Noone is able to shed light on what may have changed from the download perspective. A note on the site indicates it should be an Office 2007 or better. However the file downloader says its a 97-2003 file and defaults the .xls extension.
Peplexed hat led me to opening the file in Notepad++ to see it was in OpenXML format, which in turn led me to try changing the extension at download time. I can read either extension from Notepad++ with same resilts.
If I try to open the file as downladed with the .xls extension in excel 2007 I get the following error:
"the specified file is in a different format than the specified extension. Verify that the file is not corrupted and is from a trusted source before opening the file." but it opens.
If I try to open it with the .xlsx extension, it says its not valid and does not open. But I can open it with the changed extension on 2003
Here is some of the header info from notepad ++
<!DOCTYPE html
      PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en-US"><head><title>Interface Report </title>
<style type="text/css">
<!--/* <![CDATA[ */
body {
  font: .67em small-caps normal normal/normal verdana,arial,sans-serif;
}

/* Default table. */
table {
  border-collapse: collapse;
  border: thin solid #111111;
  padding: 1px 4px;
  background-color: white;
  margin: 0;
}
I had a similar problem when opening a downloaded file.

To fix it I had to change a setting in Excel.

I can't recall straight off what setting it was but I'll check it out later.
excellent thanks, I'm pretty sure the input file is corrupted somehow between 97-2003 and 2007 up
SOLUTION
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
I changed the name of the file ext to .html and that did in fact open up in the browser without issue. I am trying to get the developers of the file to at least put it back into the .xls format wich i could read in Jet but that is mostly out of my control. Can I somehow open this html file and force a save to the xls or xlsx format?
You can open it with Excel then use Excel to save it as a valid xls/xlsx file, yes
I can only manipulate the file with vb6 code, I cannot open it in excel
Can't you parse the html? You could work with it in arrays, stick it in a database and use that as your source or even put the HTML table into a record set object