MS Access 2010 中两个打开的电子表格之间动态 VLookup 的 VBA 代码

VBA Code for Dynamic VLookup Between Two Open Spreadsheets from MS Access 2010

提问by CSharp821

I've taken a different approach to a work project and I'm running into a wall. I've Google'd everything that I can think to Google and searched multiple forums before coming back to S.O. to ask for more help. I have a form in Access that let's users enter a customer/division combination, checks to make sure that there is an existing file path for that customer, then opens excel template files and saves them to the correct folder with a customer specific file name. This all seems to be working fine. Here's the part that has me completely stumped. The next part of this would be to open two of the excel files assigning, the Workbooks as variables xlWB1 and xlWB2 and the Worksheets as xlWS1 and xlWS2(Sheet1). I need to start in xlWB1.xlWS1.(cell D2) and do a VLookup on the value (item number) of that cell against the values of the cells in the range xlWB2.xlWS2.Range(D2:D1937). My hope was to count the total number of rows in each worksheet before starting the VLookup so that I could assign that value to a variable and use that variable to define the bottom of the range. I'm going to apologize in advance if the answer to this is something simple. I've never tried to perform any operations in Excel from Access using VBA, so I'm also struggling with the syntax. Please let me know if my question isn't clear or if there is any additional information that you need. I've pasted my starting code below.

我对一个工作项目采取了不同的方法，但我正在撞墙。在回到 SO 寻求更多帮助之前，我已经谷歌了我能想到的一切，并搜索了多个论坛。我在 Access 中有一个表单，让用户输入客户/部门组合，检查以确保该客户存在现有文件路径，然后打开 excel 模板文件并将它们保存到正确的文件夹中，并使用客户特定的文件名。这一切似乎都运行良好。这是让我完全难倒的部分。下一步是打开两个分配的 excel 文件，工作簿作为变量 xlWB1 和 xlWB2，工作表作为 xlWS1 和 xlWS2(Sheet1)。我需要从 xlWB1.xlWS1 开始。（单元格 D2）并对该单元格的值（项目编号）与范围 xlWB2.xlWS2.Range(D2:D1937) 中单元格的值进行 VLookup。我希望在开始 VLookup 之前计算每个工作表中的总行数，以便我可以将该值分配给一个变量并使用该变量来定义范围的底部。如果这个问题的答案很简单，我会提前道歉。我从未尝试过使用 VBA 从 Access 在 Excel 中执行任何操作，所以我也在语法上苦苦挣扎。如果我的问题不清楚或者您需要任何其他信息，请告诉我。我在下面粘贴了我的起始代码。我希望在开始 VLookup 之前计算每个工作表中的总行数，以便我可以将该值分配给一个变量并使用该变量来定义范围的底部。如果这个问题的答案很简单，我会提前道歉。我从未尝试过使用 VBA 从 Access 在 Excel 中执行任何操作，所以我也在语法上苦苦挣扎。如果我的问题不清楚或者您需要任何其他信息，请告诉我。我在下面粘贴了我的起始代码。我希望在开始 VLookup 之前计算每个工作表中的总行数，以便我可以将该值分配给一个变量并使用该变量来定义范围的底部。如果这个问题的答案很简单，我会提前道歉。我从未尝试过使用 VBA 从 Access 在 Excel 中执行任何操作，所以我也在语法上苦苦挣扎。如果我的问题不清楚或者您需要任何其他信息，请告诉我。我在下面粘贴了我的起始代码。如果我的问题不清楚或者您需要任何其他信息，请告诉我。我在下面粘贴了我的起始代码。如果我的问题不清楚或者您需要任何其他信息，请告诉我。我在下面粘贴了我的起始代码。

UPDATED CODE IN CASE ANYONE ELSE NEEDS TO USE IT! THANK YOU ALL FOR THE HELP!!

更新代码，以防其他人需要使用它！谢谢大家的帮助！！

Sub modExcel_SixMonth()

    Const WB_PATH As String = "\FMI-FS\Users\sharp-c\Desktop\TestDir\"

    Dim xlApp As Excel.Application

    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet
    Dim xlRng As Excel.Range
    Dim rCount As Long

    Dim xlWB2 As Excel.Workbook
    Dim xlWS2 As Excel.Worksheet
    Dim rCount2 As Long
    Dim sFormula As String

    Dim i As Long
    Dim xlSheetName As String
    Dim bolIsExcelRunning As Boolean

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
        Set xlApp = CreateObject("Excel.Application")
    Else
        bolIsExcelRunning = True
    End If

    xlApp.Visible = False

    Set xlWB = xlApp.Workbooks.Open(WB_PATH & "acct 900860 Kentucky RSTS.xlsx")
    Set xlWS = xlWB.Sheets(1)

    Set xlWB2 = xlApp.Workbooks.Open(WB_PATH & "acct 900860 six months.xlsx")
    Set xlWS2 = xlWB2.Sheets(1)

    xlSheetName = xlWS2.Name

    ' rCount: RSTS Row Count
    rCount = xlWS.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    Debug.Print "rCount : " & rCount

    ' rCount2: 6 Months Row Count
    rCount2 = xlWS2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
    Debug.Print "rCount2 : " & rCount2

    xlWS.Activate

    With xlWS
        For i = 2 To rCount

            sFormula = "=VLOOKUP(C" & i & ", '" & WB_PATH & "[" & "acct 900860 six months.xlsx" & "]" & _
                       xlSheetName & "'!$D:$D$" & rCount2 & ", 1, 0)"

            Debug.Print sFormula
            .Range("D" & i).Formula = sFormula
            DoEvents
        Next
    End With

    xlWB.Save

    xlWB2.Close False                       'Closes WB Without Saving Changes
    Set xlWB2 = Nothing

    Set xlWS = Nothing
    xlWB.Close
    Set xlWB = Nothing

    If Not bolIsExcelRunning Then
    xlApp.Quit
    End If

    Set xlApp = Nothing

End Sub

回答by Tim Williams

I think this is maybe closer to what you need. Only need a single instance of excel for both workbooks...

我认为这可能更接近您的需要。两个工作簿只需要一个 excel 实例...

Sub modExcel_SixMonth()

Const WB_PATH As String = "C:\Documents and Settings\Chris\Desktop\TestDir\"

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim xlRng As Excel.Range
Dim rCount As Long

Dim xlWB2 As Excel.Workbook
Dim xlWS2 As Excel.Worksheet
Dim xlRng2 As Excel.Range
Dim rCount2 As Long
Dim sFormula As String

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True

    Set xlWB = xlApp.Workbooks.Open(WB_PATH & "acct 900860 Kentucky RSTS.xlsx")
    Set xlWS = xlWB.Sheets(1)

    Set xlWB2 = xlApp.Workbooks.Open(WB_PATH & "acct 900860 six months.xlsx")
    Set xlWS2 = xlWB2.Sheets(1)

    ' rCount: RSTS Row Count
    rCount = xlWS.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
    Debug.Print "rCount : " & rCount

    ' rCount2: 6 Months Row Count
    rCount2 = xlWS2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count - 1
    Debug.Print "rCount2 : " & rCount2

    sFormula = "=VLOOKUP(C2," & xlWS2.Range("D2:D1937").Address(True, True, , True) & _
                ",1,FALSE)"

   Debug.Print sFormula
   With xlWS
       .Range("D2").Formula = sFormula
   End With

End Sub

回答by mkingston

Have you tried using the same application object? I believe this was a comment on this question earlier.

您是否尝试过使用相同的应用程序对象？我相信这是之前对这个问题的评论。

Additionally, if this doesn't work, you could use the find method of the range object. I.e.

此外，如果这不起作用，您可以使用 range 对象的 find 方法。IE

XLWB2.Range("Your range here").find(XLWB1.Range( _
    "Cell containing value you're looking for").Value,lookat:=xlwhole)