Accessing Excel workbook as database schema (ADOX).
Another question posted onto MSDN VBA asked the question, I want to retrieve (using ADO) Excel worksheet name and total count of sheets. Very straight to the point ! Well since I'd solved the other problem I wondered if this was possible and it is!
To do this in VBA you need to reference the ADO Ext for DDL and Security objects. Once you do that you can basically get information about the sheets in the Excel workbook as you would tables in a database. Here's some code.... again the workbook must be saved for this to work.
Public Sub GetWorkbooksSchema()
'the name of the workbook, the workbook must be saved prior to accessing it using ADO
Dim sWorkbook As String
sWorkbook = "E:\Knowledge Base\Forum Solutions\Office Apps\Excel Schema\Test Sheet.xls"
'connection object needed to connect to Excel/Access
Dim adoConnection As New ADODB.Connection
'connection string to Excel/Access
adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbook & _
";Extended Properties=""Excel 8.0;HDR=Yes;"""
'open the connection
adoConnection.Open
'treat the workbook as a database catalog, the catalog at the connection
Dim adoWbkAsDatabase As New ADOX.Catalog
adoWbkAsDatabase.ActiveConnection = adoConnection
'get the catalogs tables, which in this case is the worksheets
Dim adoTables As ADOX.Tables
Set adoTables = adoWbkAsDatabase.Tables
'display a count of the worksheets
MsgBox adoTables.Count
'for each table/sheet in the catalog/workbook display the name
Dim adoTable As ADOX.Table
For Each adoTable In adoTables
MsgBox adoTable.Name
Next
'close the connection
adoConnection.Close
'tidy up
Set adoConnection = Nothing
Set adoWbkAsDatabase = Nothing
Set adoTables = Nothing
Set adoTable = Nothing
End Sub
The code comments should cover what's going on. Need to point out thought that it's not possible to modify the Excel file using this approach for example you can't append a table/sheet to the workbook throught the Tables collection. It looks like read only access to information. Also Excel doesn't have the concept of keys and indexes so your very limited in that respect.
If you want to add or delete sheets and so on you can use SQL DDL statements instead like the CREATE TABLE SQL statement.
Dim adoCommand As New ADODB.Command
adoCommand.ActiveConnection = adoConnection
adoCommand.CommandType = adCmdText
'creating a table/sheet using the CREATE TABLE command must specify field(s) in the sheet/table
'there must be a header row in the newly created sheet
adoCommand.CommandText = "CREATE TABLE [Sheet5] ( ID int )"
Just like to highlight that you can take advantage of the fact that Excel can be treated as a database in ADO.NET too. The advantage of using these approaches is that your not coupling your application to Excel as you do using automation.