vba - 在 DoCmd.TransferSpreadsheet 中对 sheetname 使用近似值

Question

我正在尝试使用 DoCmd.TransferSpreadsheet 将具有多个工作表的 Excel 文档导入 Access 表（Table1），为此，您必须将它们指定为 DoCmd.TransferSpreadsheet 的“范围”参数的一部分。但是，这些工作表名称在时间上不一致，通常包含一些时间戳或其他不可复制的字符串。

ExcelDoc.xlsx中的工作表：

• 20210519172848_DataTable_ABC
• 20210519172921_FinancialTable_DEF

工作表名称中通常有一个一致的字符串。例如“数据表”和“财务表”。我想使用这些关键字指定工作表名称，就像您可以使用通配符 (*) 通过 Dir() 函数使用关键字指定 Excel 文档一样。但是，这不起作用：

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

它认为我正在寻找工作表名称 *DataTable*。我看到一些建议说添加“！” 最后是必要的，但这似乎会因为无效字符而产生名称有效性错误。关于如何实现这一目标的任何建议？

标签： vbams-access

Answer 1

这对我有用：

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