Dynamically linking to an Excel sheet

[HartJF]

I have a portion of an application that asks the user to select a file (.txt, .csv, or .xls_), establishes a link to that file, and adds records to a table using a query that includes a batch identifier.

My difficulty is with connecting to an .xls_ file. I am able to derive the Connect property successfully. The SourceTableName property requires either a sheet or a range name. The sheet is always the first in the workbook (usually the only sheet), but its name can vary.

I don't need the entire spreadsheet loaded into the database, so I didn't want to use TransferSpreadsheet. I didn't want to create an Excel object to get Sheets(0).Name because the overhead seemed too great.

Have I any other alternative? Thank you in advance for your insight.

Here is the existing code:

Sub sbrConnectNewLink(strLinkedTable As String, strNewFile As String)
    Dim str As String
    Dim tdfNew As TableDef
    With CurrentDb
        str = .TableDefs(strLinkedTable).Connect
        Set tdfNew = .CreateTableDef(strLinkedTable)
        With tdfNew
            .Connect = Left$(str, InStrRev(str, "\")) & strNewFile
            .SourceTableName = [***???***]
        End With
        DoCmd.DeleteObject acTable, strLinkedTable
        .TableDefs.Append tdfNew
    End With
    Set tdfNew = Nothing
End Sub