Need to Vlookup Two Excel With VBA Code & invoke in Uipath

Help Studio
, ,
2

Hi @Balaharish_T ,

Sub VLOOKUP_Between_Excel_Files()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim i As Long, j As Long
    Dim lookupValue As Variant, result As Variant
    
    ' Open both workbooks
    Set wb1 = Workbooks.Open("Path_to_Excel_1.xlsx") ' Replace with your file path
    Set wb2 = Workbooks.Open("Path_to_Excel_2.xlsx") ' Replace with your file path
    
    ' Set worksheets
    Set ws1 = wb1.Sheets("Sheet1") ' Replace with your sheet name
    Set ws2 = wb2.Sheets("Sheet1") ' Replace with your sheet name
    
    ' Find the last rows in each sheet
    lastRow1 = ws1.Cells(ws1.Rows.Count, "F").End(xlUp).Row ' Column 6 in Excel 1
    lastRow2 = ws2.Cells(ws2.Rows.Count, "L").End(xlUp).Row ' Column 12 in Excel 2
    
    ' Loop through each value in Column 6 of Excel 1
    For i = 2 To lastRow1 ' Assuming headers are in row 1
        lookupValue = ws1.Cells(i, 6).Value ' Column 6 in Excel 1
        result = Application.VLookup(lookupValue, ws2.Range("L2:P" & lastRow2), 5, False) ' Column 12 in Excel 2 and get Column 16 value
        
        ' Write the result to Column 15 in Excel 1
        If Not IsError(result) Then
            ws1.Cells(i, 15).Value = result ' Column 15 in Excel 1
        Else
            ws1.Cells(i, 15).Value = "Not Found" ' Or any value you want for not found cases
        End If
    Next i
    
    ' Save and close workbooks
    wb1.Close SaveChanges:=True
    wb2.Close SaveChanges:=True
End Sub

Next, to invoke this VBA code in UiPath, you can use the “Execute Macro” activity. Here’s an example:

  1. Drag and drop an “Excel Application Scope” activity in your UiPath sequence.
  2. Use the “Execute Macro” activity inside the scope.
  3. Configure the “Execute Macro” activity by specifying the Excel file path, macro name (in this case, it’s “VLOOKUP_Between_Excel_Files”), and other necessary parameters.

Ensure that you have enabled macro settings in Excel for this code to run without issues.

Regards,
Vinit Mhatre