cmrobertson
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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"
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.
What provider were you using?
If it was JET then you should just be able to replace it.
ASKER
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?
ASKER
cnn.open "Provider=MProvider=Micros oft.ACE.OL EDB.12.0;" & _
"Data Source=B:\Projects\reports \Interface Report.xlsx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
"Data Source=B:\Projects\reports
"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.
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"";"
ASKER
sorry that was a pasting error I have what you do
ASKER
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?
ASKER
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 $
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?
Also, is f22 definitely the column name?
ASKER
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.
ASKER
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.OL EDB.12.0;" & _
"Data Source=B:\Projects\\report s\Interfac eReport.xl sx;" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
and as I said I am getting "External Table not in the expected format"
I have it right below the file itself and the connection statement are both .xlsx
cnn.open "Provider=Microsoft.ACE.OL
"Data Source=B:\Projects\\report
"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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
.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?
That's for an ODBC connection.
Can you attach the file, before and after you've changed the file extension?
ASKER
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.OL EDB.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;
}
cnn.open "Provider=Microsoft.Jet.OL
"Data Source=c:\reports\testfile
"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>
<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.
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.
ASKER
excellent thanks, I'm pretty sure the input file is corrupted somehow between 97-2003 and 2007 up
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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
http://www.connectionstrings.com/excel-2007