代码之家 › 专栏 › 技术社区 › Teddy Hanna

使用ADODB时,如何让excel自动读取目标文件表1的表名

adodb vba excel

Teddy Hanna · 技术社区 · 8 月前

我正试图使用ADODB自动从关闭的工作簿上的特定单元格中读取信息,并将该信息放入主工作表中。

已关闭的工作簿都只有一个工作表,但它们都有不同的名称。

如何使宏自动知道工作表的名称?

I.E.来源=“选择*来源[ XXX $A1:A1]'XXX是宏自动找到的目标工作表的名称

Sub ImportDataFromClosedSheet() 
    Dim cn As ADODB.Connection 
    Dim rs As ADODB.RecordsetSet    
    
    'This paragraph selects a target file
    Dim file As FileDialog
    Dim sItem As String
    Dim GetFile As String

    Set file = Application.FileDialog(msoFileDialogFilePicker) 
    With file 
        .Title = "Select a File" 
        .AllowMultiSelect = False 
        '.InitialFileName = strPath 
        If .Show <> -1 Then GoTo NextCode 
        sItem = .SelectedItems(1) 
    End With

NextCode: 
    GetFile = sItem 
    Set file = Nothing    
    
    cn = New ADODB.Connection
    
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & GetFile & ";" & _
        "Extended Properties='Excel 12.0 Xml;HDR=No';"
    
    cn.Open
    
        Set rs = New ADODB.Recordset
    
        rs.ActiveConnection = cn
        rs.Source = "SELECT * FROM [XXX$J14:J14]"
        rs.Open
    
        Sheet1.Range("A1").CopyFromRecordset rs
       
        rs.Close
    
    cn.Close

End Sub

3 回复 | 直到 8 月前

FunThomas 8 月前

如果不在SQL语句中指定表名,ADODB驱动程序将使用第一张表。当您声明文件中始终只有一张表时,只需使用

 rs.Source = "SELECT * FROM [J14:J14]"

但是,如果您真的想获取工作表名称,请使用ADODB openShema 使用QueryType adSchemaTables 。以下函数将返回一个数组,其中包含打开连接的所有表名——在Excel中,“table”表示工作表。

Function getTableNames(cn As ADODB.Connection)
    Dim rs As ADODB.Recordset
    Set rs = cn.OpenSchema(adSchemaTables)

    Dim data
    data = rs.GetRows
        
    ReDim tableNames(LBound(data, 2) To UBound(data, 2))
    Dim row As Long
    For row = LBound(data, 2) To UBound(data, 2)
        tableNames(row) = data(2, row)
    Next
    getTableNames = tableNames
End Function

现在,你的代码可能看起来像这样:

cn.Open
Dim sheetNames
sheetNames = getTableNames(cn)
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn

rs.Source = "SELECT * FROM [" & sheetNames(0) & "J14:J14]"
rs.Open
Sheet1.Range("A1").CopyFromRecordset rs
rs.Close
cn.Close

Tim Williams 8 月前

一个快速的例子使用 OpenSchema :

Sub TestSchema()

    Dim oConn As New ADODB.Connection
    Dim oRS As New ADODB.Recordset, strPathtoTextFile, n

    strPathtoTextFile = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    
    oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=""Excel 12.0;HDR=YES;MaxScanRows=1000;IMEX=1"";Data Source=" & strPathtoTextFile
    
    Set oRS = oConn.OpenSchema(adSchemaTables)
    
    ToSheet Sheet5.Range("A1"), oRS
    
End Sub

'write field names and data from `rs` to a worksheet starting at `rng`
Sub ToSheet(rng, rs)
    Dim f, i
    i = 0
    rng.Resize(1000, 200).ClearContents '<<< adjust or comment out...
    For Each f In rs.Fields
        rng.Offset(0, i).Value = f.Name
        i = i + 1
    Next f
    rng.Offset(1, 0).CopyFromRecordset rs
End Sub

工作表名称(附加 $ )在现场 TABLE_NAME

Parfait 8 月前

如果SQL仅用于提取静态数据而不进行逻辑计算或连接到其他工作表,请避免在Excel中使用ADODB API。

相反,考虑使用 Excel Object Model 在那里你可以简单地打电话 Workbooks.Open 和 Worksheets.Range ,以便直接实施。

Dim wb As Workbook

' OPEN WORKBOOK
Set wb = Workbooks.Open(GetFile)
    
' RETRIEVE RANGE VALUE
With wb.Worksheets(1)
  ThisWorkbook.Worksheets(1).Range("A1").Value = .Range("J14").Value
End With

' CLOSE WORKBOOK
wb.Close False

上面的值可以扩展到一个单元格范围之外。