title: VBA Code for Dynamic VLookup Between Two Open Spreadsheets from MS Access 2010
body: <p>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.  </p>
  
  <p>UPDATED CODE IN CASE ANYONE ELSE NEEDS TO USE IT!  THANK YOU ALL FOR THE HELP!!</p>
  
  <pre><code>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 &lt;&gt; 0 Then
          Set xlApp = CreateObject("Excel.Application")
      Else
          bolIsExcelRunning = True
      End If
  
      xlApp.Visible = False
  
      Set xlWB = xlApp.Workbooks.Open(WB_PATH &amp; "acct 900860 Kentucky RSTS.xlsx")
      Set xlWS = xlWB.Sheets(1)
  
      Set xlWB2 = xlApp.Workbooks.Open(WB_PATH &amp; "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 : " &amp; rCount
  
      ' rCount2: 6 Months Row Count
      rCount2 = xlWS2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
      Debug.Print "rCount2 : " &amp; rCount2
  
      xlWS.Activate
  
      With xlWS
          For i = 2 To rCount
  
              sFormula = "=VLOOKUP(C" &amp; i &amp; ", '" &amp; WB_PATH &amp; "[" &amp; "acct 900860 six months.xlsx" &amp; "]" &amp; _
                         xlSheetName &amp; "'!$D$2:$D$" &amp; rCount2 &amp; ", 1, 0)"
  
              Debug.Print sFormula
              .Range("D" &amp; 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
  </code></pre>