错误91（对象未设置）当关闭的工作簿

Question

探数据我有两个工作簿，打电话给他们第一册和第二册。 Book1已打开，我正试图从已关闭的Book2中将数据存入Book1。 Book1包含列出Book2中相应元素的列。例如，Book1有一列数字对应于Book2中的另一个数字列表。我正在尝试使用VBA从Book2中的行中获取数据，并将匹配的数字与Book1中的数据进行匹配。

现在，我有以下MWE产生错误91（对象未设置），当我尝试执行代码：因为它是

Dim path As String, book As String, sheet As String 
Dim targetRng As Range, sourceRng As Range 

path = Left(Cells(2, 1).Value, InStrRev(Cells(2, 1).Value, "\")) 
book = Dir(Cells(2, 1).Value) 
sheet = Cells(2, 2).Value 
Set targetRng = Range("A1").CurrentRegion 
Set targetRng = targetRng.Offset(1, 0).Resize(targetRng.Rows.Count - 1) 'Ignore header row 
For i = 1 To targetRng.Rows.Count 
    Set sourceRng = "'" & path & "[" & book & "]" & sheet & "'!" & Range("A:A").Find(targetRng.Cells(i, 1).Value) 'Error is here 
    targetRng.Cells(i, 2).Value = ExecuteExcel4Macro("'" & path & "[" & book & "]" & sheet & "'!" & Cells(sourceRng.Row, 2).Address) 
    ' 
    'Do some other stuff 
    ' 
Next i 

Answer 1

我不愿意提供使用ADO答案一个广泛的话题。但我会为您提供两个如何使用ADO查询Excel文件的示例。

在左边，我们有，我们将查询[Employees.xlsx]源文件。在我们正在更新的工作簿的右侧[Sample.xlsm]。在我的例子中，我们使用了展位工作簿的[Sheet1]。请注意，ADO将使用列标题作为字段名称（id，first_name，last_name ...等）。

在例1中我们查询[Employees.xlsx]加载从[Employees.xlsx]！[工作表Sheet1]所有记录到记录EmployeeData。接下来，我们遍历[Sample.xlsm]！[Sheet1]的id列并设置记录集EmployeeData.Filter = "id=" & Cells(x, 1)的过滤器。然后记录字段的值复制到相应的单元格.Cells(x, 2) = EmployeeData.Fields("first_name")

Sub Example1() 

    Dim lastRow As Long, x As Long 

    Const adOpenKeyset = 1 
    Const adLockOptimistic = 3 
    Dim conn 
    Dim EmployeeData 

    Set conn = CreateObject("ADODB.Connection") 
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=I:\stackoverflow\Employees.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 
    conn.Open 

    On Error GoTo CloseConnection 

    Set EmployeeData = CreateObject("ADODB.Recordset") 

    With EmployeeData 
     .ActiveConnection = conn 
     .CursorType = adOpenKeyset 
     .LockType = adLockOptimistic 
     .Source = "Select * FROM [Sheet1$]" 
     .Open 
     On Error GoTo CloseRecordset 

    End With 

    With Worksheets("Sheet1") 
     lastRow = .Range("A" & Rows.Count).End(xlUp).Row 
     For x = 2 To lastRow 

      EmployeeData.Filter = "id=" & Cells(x, 1) 
      If Not (EmployeeData.BOF And EmployeeData.EOF) Then 
       .Cells(x, 2) = EmployeeData.Fields("first_name") 
       .Cells(x, 3) = EmployeeData.Fields("last_name") 
       .Cells(x, 4) = EmployeeData.Fields("email") 
       .Cells(x, 5) = EmployeeData.Fields("gender") 
       .Cells(x, 6) = EmployeeData.Fields("ip_address") 
      End If 

     Next 
    End With 
CloseRecordset: 

    EmployeeData.Close 
    Set EmployeeData = Nothing 

CloseConnection: 
    conn.Close 
    Set conn = Nothing 
End Sub 

在例题我们LEFT JOIN [Sample.xlsm]！[工作表Sheet1]与[Employees.xlsx]！[Sheet1中。通过这种方式，我们使用CopyFromRecordset为我们更新数据。
Sheet1.Range("A2").CopyFromRecordset EmployeeData。

Sub Example2() 

    Dim lastRow As Long, x As Long 

    Const adOpenKeyset = 1 
    Const adLockOptimistic = 3 
    Dim conn 
    Dim EmployeeData 

    Set conn = CreateObject("ADODB.Connection") 
    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";" 
    conn.Open 

    On Error GoTo CloseConnection 

    Set EmployeeData = CreateObject("ADODB.Recordset") 

    With EmployeeData 
     .ActiveConnection = conn 
     .CursorType = adOpenKeyset 
     .LockType = adLockOptimistic 

     .Source = "SELECT table1.id, table2.[first_name], table2.[last_name], table2.[email], table2.[gender], table2.[ip_address] FROM ([Sheet1$] table1 LEFT JOIN `I:\stackoverflow\Employees.xlsx`.`Sheet1$` table2 ON table2.[id]=table1.[id])" 
     .Open 
     On Error GoTo CloseRecordset 


    End With 

    Sheet1.Range("A2").CopyFromRecordset EmployeeData 

CloseRecordset: 

    EmployeeData.Close 
    Set EmployeeData = Nothing 

CloseConnection: 
    conn.Close 
    Set conn = Nothing 
End Sub 

注： CopyFromRecordset将替换受影响的列中的所有数据。出于这个原因，建议使用示例1，直到您开始编写查询为止。