stanl
03-05-2007, 06:34 AM
I haven't found much about this, so if it has been covered, my apologies. I haven't purchased/installed Office 2007 yet, but have both the Compatibility Pack and the OLEDB driver [ Microsoft.ACE.OLEDB.12.0 ] installed.
The compatibility Pack will not let you open .xlsx, but I have used both an unzip dll, LogParser, and the driver to get a sense of how data is stored in an .xlsx
I have 1 anomaly so far. If I open an .xls file using the Jet 4.0 Provider and ADOX, a list of 'tables' will include both a list of all named ranges + a list of worksheets, i.e. Sheet1$. If I examine a 2007 .xlsx file with Winzip, I will see included files like Sheet1.xml, sheet2.xml, but ADOX and the ACE Provider does not register these as Tables.
Sub xlsxadox()
cXLS = "c:\temp\test.xlsx"
cConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & cXLS & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO"";"
Set oCAT = CreateObject("ADOX.CATALOG")
oCAT.ActiveConnection = cConn
n = oCAT.Tables.Count
cTables = ""
For i = 0 To (n - 1)
cTables = cTables & oCAT.Tables(i).Name & vbCrLf
Next
Set oCAT = 0
MsgBox cTables
End Sub
I have a friend on another forum who is installing 2007 today and will save a 2003 .xls to both a 2007 .xls and .xlsx so I can test whether the ACE Provider lists sheet names for the .xls - perhaps someone here knows the reason for the anomaly.
P.S. - does anyone know if there is an API out to interpret the xml packed in an .xlsx?
TIA Stan
The compatibility Pack will not let you open .xlsx, but I have used both an unzip dll, LogParser, and the driver to get a sense of how data is stored in an .xlsx
I have 1 anomaly so far. If I open an .xls file using the Jet 4.0 Provider and ADOX, a list of 'tables' will include both a list of all named ranges + a list of worksheets, i.e. Sheet1$. If I examine a 2007 .xlsx file with Winzip, I will see included files like Sheet1.xml, sheet2.xml, but ADOX and the ACE Provider does not register these as Tables.
Sub xlsxadox()
cXLS = "c:\temp\test.xlsx"
cConn = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" & cXLS & ";Extended Properties=""Excel 12.0;IMEX=1;HDR=NO"";"
Set oCAT = CreateObject("ADOX.CATALOG")
oCAT.ActiveConnection = cConn
n = oCAT.Tables.Count
cTables = ""
For i = 0 To (n - 1)
cTables = cTables & oCAT.Tables(i).Name & vbCrLf
Next
Set oCAT = 0
MsgBox cTables
End Sub
I have a friend on another forum who is installing 2007 today and will save a 2003 .xls to both a 2007 .xls and .xlsx so I can test whether the ACE Provider lists sheet names for the .xls - perhaps someone here knows the reason for the anomaly.
P.S. - does anyone know if there is an API out to interpret the xml packed in an .xlsx?
TIA Stan