Read Office 2007 w/out 2007 installed

[stanl]

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

[stanl]

If anyone cares, OpenSchema(20) from a connection object got what I needed. I'm still a little confused in that Microsoft states its 2007 Compatibility Pack extends the 2003 OpenXML() method to accept xml files in an .xlsx package, but all I get is garbage. Stan

[mfaina]

Hi Stan:
Is "Microsoft.ACE.OLEDB.12.0' provider is not registered on the local.." an OLEDB exception? I installed what I need to get rid of it - just wonder if I can check this somehow.
Thanks, Mihail

[stanl]

Hi Stan:
Is "Microsoft.ACE.OLEDB.12.0' provider is not registered on the local.." an OLEDB exception? I installed what I need to get rid of it - just wonder if I can check this somehow.
Thanks, Mihail

If I understand your question, the ACE Provider is a separate download [part of the Office 2007 Compatibility Pack]; if it is installed properly you should be able to search for

Microsoft.ACE.OLEDB.12.0

in the registry. Stan

[mfaina]

I was just wondering if you can catch the excpetion for it - found that is not an OleDBException is an InvalidOperationException. Thanks!
Mihail

[stanl]

I was just wondering if you can catch the excpetion for it - found that is not an OleDBException is an InvalidOperationException. Thanks!
Mihail

I just wrote a function IsInReg(key) to return a boolean. Stan