CODEWITHSUNDEEP
excelms-accessvbaoledbrecordset
Philippe Grondier
Philippe Grondier

Reputation: 11138

Using EXCEL as datasource through Microsoft OLE DB provider

We are frequently using some Excel files as a datasource for massive imports in our database. At the code level, we always refer to the corresponding data source as:

set rs = New ADODB.recordset
rs.open "SELECT * FROM [sheet1$]", myConnectionString, etc

Of course, this procedure only works when there's a sheet in the Excel file which is named [sheet1]. I'd like to add some sheet management code here, but without having to create an instance of the original Excel file, opening it, and so on (my users might get a file with a different sheet name, and might not have Excel installed).

Any idea?

Upvotes: 0

Views: 1484

Answers (1)

HansUp
HansUp

Reputation: 97100

You can open a recordset with the ADO OpenSchema method and then list the table (sheet) names in your workbook.

Public Sub SheetsInWorkbook()
    Dim strConnect As String
    Dim cn As Object
    Dim rs As Object
    Dim strPath As String

    strPath = CurrentProject.Path & Chr(92) & "temp.xls"
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
        & "Data Source='" & strPath & "';" _
        & "Extended Properties='Excel 8.0';"
    Set cn = CreateObject("ADODB.Connection")
    cn.ConnectionString = strConnect
    cn.Open
    Set rs = cn.OpenSchema(20) '20 = adSchemaTables '
    Debug.Print "TABLE_NAME"
    Do While Not rs.EOF
        Debug.Print rs!TABLE_NAME
        rs.MoveNext
    Loop

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

Upvotes: 2

Related Questions