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?
LVL 7
cmrobertsonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What provider are you using for the connection?

You need to use Microsoft.ACE.OLEDB.12.0 for xlsx files, which should also work with xls files.
cmrobertsonAuthor Commented:
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"
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

NorieAnalyst Assistant Commented:
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.
cmrobertsonAuthor Commented:
I did downoad the ACE software and I thought installed it but I am still getting the "Provider not found" error
NorieAnalyst Assistant Commented:
Can you post what you used for the connection string?
cmrobertsonAuthor Commented:
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"";"
NorieAnalyst Assistant Commented:
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

cmrobertsonAuthor Commented:
sorry that was a pasting error I have what you do
cmrobertsonAuthor Commented:
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?
NorieAnalyst Assistant Commented:
How is it failing exactly?
cmrobertsonAuthor Commented:
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 $
NorieAnalyst Assistant Commented:
Does it work without the criteria?

Also, is f22 definitely the column name?
cmrobertsonAuthor Commented:
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.
cmrobertsonAuthor Commented:
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"
NorieAnalyst Assistant Commented:
You can't just change a file extension like that.

That could be what's causing the problem - the file extension is telling the code that the file is a particular format but it isn't really that format.

Can you open the downloaded file in Excel?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmrobertsonAuthor Commented:
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
NorieAnalyst Assistant Commented:
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?
cmrobertsonAuthor Commented:
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;
}
NorieAnalyst Assistant Commented:
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.
cmrobertsonAuthor Commented:
excellent thanks, I'm pretty sure the input file is corrupted somehow between 97-2003 and 2007 up
KyleSWCommented:
That file isn't open xml, it's an HTML table that someone's renamed as .xls, since Excel will generally open renamed html documents and put the contents in the right place, it's an easy (and common) way of shoving data into an Excel format from a Web server.

Why not keep it as 2003, open it with excel then re-save it from excel as a 2003 document? My guess is that some of the html has changed and Excel doesn't really like it. Saving it from excel might get rid of the nastiness and you'll be able to open it with your Jet provider

As an aside, you can't open an open xml document in Notepad since they're a collection of documents in a zip file. To view their contents, you change the extension to .zip and then you can view individual documents within the file with Notepad

To try and see if there's anything visibly wrong with the source, try changing the extension to .html open it in yourr browser and see if there are any obvious errors that might be causing issues.
cmrobertsonAuthor Commented:
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?
KyleSWCommented:
You can open it with Excel then use Excel to save it as a valid xls/xlsx file, yes
cmrobertsonAuthor Commented:
I can only manipulate the file with vb6 code, I cannot open it in excel
KyleSWCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.