UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Vba Code For Dynamic Vlookup Between Two Open Spreadsheets From Ms Access 2010, Office 2010    
 
   
Csharp821
post Mar 14 2012, 01:11 PM
Post #1

UtterAccess Addict
Posts: 113
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 U.A. 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.


CODE
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 xlApp2 As Excel.Application
Dim xlWB2 As Excel.Workbook
Dim xlWS2 As Excel.Worksheet
Dim xlRng2 As Excel.Range
Dim rCount2 As Long
  
Sub modExcel_SixMonth()
  
  Set xlApp = CreateObject("Excel.Application")
  Set xlWB = xlApp.Workbooks.Open("C:\Documents and Settings\Chris\Desktop\TestDir\acct 900860 Kentucky RSTS.xlsx")
  Set xlWS = xlWB.Sheets(1)
        xlApp.Visible = True
  rCount = ((xlWS.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count) - 1)   ' rCount: RSTS Row Count
        Debug.Print "rCount : " & rCount
        
  Set xlApp2 = CreateObject("Excel.Application")
  Set xlWB2 = xlApp2.Workbooks.Open("C:\Documents and Settings\Chris\Desktop\TestDir\acct 900860 six months.xlsx")
  Set xlWS2 = xlWB2.Sheets(1)
        xlApp2.Visible = True
  rCount2 = ((xlWS2.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count) - 1)   ' rCount2: 6 Months Row Count
        Debug.Print "rCount2 : " & rCount2

    Set xlWB = Excel.ActiveWorkbook
    
    With xlWS
        .Range("D2").Formula = "=VLOOKUP(C2,[ & xlWB2 & ][& xlWS2 &]!$D$2:$D$1937,1,FALSE)"
     '   ActiveWorkbook.Close
        
        
    End With


    
    Set xlWS = Nothing
    Set xlWB = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    
    Set xlWS2 = Nothing
    Set xlWB2 = Nothing
    xlApp2.Quit
    Set xlApp2 = Nothing
  
End Sub
Go to the top of the page
 
+
arnelgp
post Mar 15 2012, 04:21 AM
Post #2

UtterAccess Ruler
Posts: 1,090
Are you going to paste the VLookup formula starting with D2 down to D[rCount]?
Go to the top of the page
 
+
Csharp821
post Mar 15 2012, 10:03 AM
Post #3

UtterAccess Addict
Posts: 113
ArnelGP, that was what I am trying to do. I had to change my code slightly to get it to return a value. Here's what I have now which returns a value to the correct cell.

CODE
ub 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
Go to the top of the page
 
+
arnelgp
post Mar 15 2012, 11:38 AM
Post #4

UtterAccess Ruler
Posts: 1,090
here is your code:
CODE
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

    ' arnel gp
    Dim i As Long
    Dim xlSheetName As String
    Dim bolIsExcelRunning As Boolean

    On Error Resume Next
    ' test if excel is already running
    Set xlApp = GetObject(, "Excel.Application")
    On Error GoTo 0
    If Err.Number <> 0 Then
        ' Excel is not running
        ' so create an object
        Set xlApp = CreateObject("Excel.Application")
    Else
        ' flag. excel is already running before we execute this code
        bolIsExcelRunning = True
    End If
    
    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)

    'arnel gp
    xlSheetName = xlWS2.Name

    ' 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

    ' close it, had served its purpose
    ' arnel gp
    xlWB2.Close
    Set xlWB2 = Nothing

    ' arnel gp
    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
    Set xlWS = Nothing
    xlWB.Close
    Set xlWB = Nothing
    If Not bolIsExcelRunning Then
        ' it is safe to close excel
        xlApp.Quit
    End If
    Set xlApp = Nothing

End Sub


This post has been edited by arnelgp: Mar 15 2012, 11:41 AM
Go to the top of the page
 
+
Csharp821
post Mar 15 2012, 12:12 PM
Post #5

UtterAccess Addict
Posts: 113
Arnel, Sir, you are a GENIUS!!! Thank you so much for the help!! I'm going to have to print out your code and go line by line to try to understand it, but it works perfectly!! Dude, thank you!
Go to the top of the page
 
+
« Next Oldest · Access Automation · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 18th June 2013 - 04:19 PM