VBA code动态VLOOKUP之间的两个开放源代码$ P $从MS Access 2010 padsheets [英] VBA Code for Dynamic VLookup Between Two Open Spreadsheets from MS Access 2010

问题描述

我已经采取了不同的方式，以工作项目，我遇到了一堵墙。我回来SO之前已经Google'd一切，我能想到谷歌和搜索多个论坛要求更多的帮助。我在访问它可以让用户输入客户/格组合的形式，进行检查，以确保有该客户现有的文件路径，然后打开Excel模板文件并保存到相应的文件夹与客户特定的文件名。这一切似乎是工作的罚款。下面是有我彻底难倒了一部分。这样做的下一部分将打开两个Excel文件分配的，工作簿作为变量xlWB1和xlWB2和工作表作为xlWS1和xlWS2（工作表Sheet1）。我需要开始，对所述细胞在范围xlWB2.xlWS2.Range（D2：D1937）的值的单元格中xlWB1.xlWS1（小区D2）和一个做VLOOKUP上的值（项目数）。我的希望是开始VLOOKUP，这样我可以的值赋给一个变量，并使用该变量来定义范围内的底部之前，指望在每个工作表行的总数。我会提前道歉，如果这个问题的答案是简单的东西。我从来没有试图从使用VBA进行访问Excel中的任何操作，所以我也跟语法挣扎。请让我知道，如果我的问题不明确或者是否有你需要的任何其他信息。我贴我下面的出发code。

修订code如果任何人需要使用它！谢谢大家的帮助！

 子modExcel_SixMonth（）

    常量WB_PATH的String =\\ FMI-FS \用户\锐C：\桌面\ TESTDIR \

    昏暗xlApp作为Excel.Application

    昏暗xlWB作为Excel.Workbook
    昏暗xlWS作为Excel.Worksheet
    昏暗xlRng作为Excel.Range
    昏暗RCOUNT只要

    昏暗xlWB2作为Excel.Workbook
    昏暗xlWS2作为Excel.Worksheet
    昏暗rCount2只要
    昏暗sFormula作为字符串

    昏暗我只要
    昏暗xlSheetName作为字符串
    昏暗bolIsExcelRunning由于布尔

    在错误恢复下一页
    设置xlApp = GetObject的（Excel.Application）
    如果Err.Number的＆LT;＆GT; 0然后
        设置xlApp =的CreateObject（Excel.Application）
    其他
        bolIsExcelRunning = TRUE
    结束如果

    xlApp.Visible = FALSE

    设置xlWB = xlApp.Workbooks.Open（WB_PATH＆安培;科目900860肯塔基RSTS.xlsx）
    集xlWS = xlWB.Sheets（1）

    设置xlWB2 = xlApp.Workbooks.Open（WB_PATH＆安培;会计900860 6 months.xlsx）
    设置xlWS2 = xlWB2.Sheets（1）

    xlSheetName = xlWS2.Name

    RCOUNT：RSTS行数
    RCOUNT = xlWS.Range（A：A）。Cells.SpecialCells（xlCellTypeConstants）.Count之间
    Debug.PrintRCOUNT：＆放大器; RCOUNT

    rCount2：6个月行数
    rCount2 = xlWS2.Range（A：A）。Cells.SpecialCells（xlCellTypeConstants）.Count之间
    Debug.PrintrCount2：与＆amp; rCount2

    xlWS.Activate

    随着xlWS
        对于i = 2到RCOUNT

            sFormula == VLOOKUP（℃与＆amp; I＆安培;，与＆amp;＆WB_PATH安培;[＆安培;的acct 900860 6 months.xlsx与＆amp;]与＆amp; _
                       xlSheetName和放大器; '$ D $ 2：！$ D $＆放大器; rCount2和放大器; ，1，0）

            Debug.Print sFormula
            .Range（D＆安培;ⅰ）.Formula = sFormula
            的DoEvents
        下一个
    结束与

    xlWB.Save

    xlWB2.Close假关闭白平衡不保存更改
    设置xlWB2 =什么

    设置xlWS =什么
    xlWB.Close
    设置xlWB =什么

    如果不bolIsExcelRunning然后
    xlApp.Quit
    结束如果

    设置xlApp =什么

结束小组
 

解决方案

我想，这也许是更接近你所需要的。只需要Excel中的一个实例为工作簿...

 子modExcel_SixMonth（）

常量WB_PATH的String =C：\ Documents和Settings \克里斯\桌面\ TESTDIR \

昏暗xlApp作为Excel.Application
昏暗xlWB作为Excel.Workbook
昏暗xlWS作为Excel.Worksheet
昏暗xlRng作为Excel.Range
昏暗RCOUNT只要

昏暗xlWB2作为Excel.Workbook
昏暗xlWS2作为Excel.Worksheet
昏暗xlRng2作为Excel.Range
昏暗rCount2只要
昏暗sFormula作为字符串

    设置xlApp =的CreateObject（Excel.Application）
    xlApp.Visible = TRUE

    设置xlWB = xlApp.Workbooks.Open（WB_PATH＆安培;科目900860肯塔基RSTS.xlsx）
    集xlWS = xlWB.Sheets（1）

    设置xlWB2 = xlApp.Workbooks.Open（WB_PATH＆安培;会计900860 6 months.xlsx）
    设置xlWS2 = xlWB2.Sheets（1）

    RCOUNT：RSTS行数
    。RCOUNT = xlWS.Range（A：A）Cells.SpecialCells（xlCellTypeConstants）.Count之间 -  1
    Debug.PrintRCOUNT：＆放大器; RCOUNT

    rCount2：6个月行数
    。rCount2 = xlWS2.Range（A：A）Cells.SpecialCells（xlCellTypeConstants）.Count之间 -  1
    Debug.PrintrCount2：与＆amp; rCount2

    sFormula == VLOOKUP（C2，＆放大器; xlWS2.Range（D2：D1937），地址（不错，不错，真）及_
                ，1，FALSE）

   Debug.Print sFormula
   随着xlWS
       .Range（D2）。式= sFormula
   结束与

结束小组
 

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.

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$2:$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

解决方案

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

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

这篇关于VBA code动态VLOOKUP之间的两个开放源代码$ P $从MS Access 2010 padsheets的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！