Q: Use approximation for sheetname in DoCmd.TransferSpreadsheet I am trying to use DoCmd.TransferSpreadsheet to import an Excel document with multiple sheets into an Access table (Table1), and to do so you must specify them as part of the "Range" parameter of DoCmd.TransferSpreadsheet.  However, these sheet names are not consistent across time, often containing some timestamp or other string that is not replicable.
Sheets within ExcelDoc.xlsx:

*

*20210519172848_DataTable_ABC

*20210519172921_FinancialTable_DEF

There is usually a consistent string within the sheet name.  e.g. "DataTable" and "FinancialTable".  I would like to specify the sheet name using these keywords, in a similar way that you can use the wildcard (*) to specify an Excel document using a keyword via the Dir() function.  However, this does not work:
Private Sub Project1()
    Importer("DataTable", "C:\Users\Myself\Documents\ExcelDoc.xlsx")
End Sub

Public Function Importer(Keyword As String, FPath As String)
    DoCmd.TransferSpreadsheet TransferType:=acImport, SpreadsheetType:=acSpreadsheetTypeExcel12Xml, TableName:="Table1", FileName:=FPath, HasFieldNames:=True, **Range:="*" & Keyword & "*"
End Function

It thinks I'm looking for the sheet name *DataTable*.  I have seen some suggestions say that adding an "!" onto the end is necessary, but that seems to yield an name validity error because of an invalid character.  Any suggestions on how I can achieve this?

A: This is what worked for me:

Public Function SheetFinder(Keyword As String, FPath As String) As String
    Dim oConn
    Dim oRs
    Set oConn = New ADODB.Connection
    Dim sConn
    Dim SheetName As String
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FPath & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""
    oConn.Open sConn

    Set oRs = oConn.OpenSchema(adSchemaTables)
    Do While Not oRs.EOF
        SheetName = oRs.Fields("table_name").Value
        If InStr(SheetName, Keyword) Then
            SheetFinder = CStr(SheetName)
            oConn.Close
            Exit Function
        Else:
            oRs.MoveNext
        End If
    Loop
End Function


