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 (m开发者_如何转开发y users might get a file with a different sheet name, and might not have Excel installed).

Any idea?

---

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