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

Help Studio
, ,
1

Hi ,
I have two Excel = Excel 1 & Excel 2 with 10k Data’s
In Excel 1 has 14 Columns & Excel 2 has 30 Columns
Vlookup Column 6 in Excel 1 with column 12 in Excel 2 & get Column 16 value from excel 2
Then Write it in Column15 of Excel 1

For this I need VBA code for Vlookup above requirement
And
kindly give me how to invoke VBA in UiPath with this VBA Code Example

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

3

Hi @Vinit_Mhatre ,

Thanks ,

I have edited to my requirement

I need what we enter in macro name & Macro Parameters in Properties

4

Hi @Vinit_Mhatre

Thanks for the Code

Give me What parameter want to enter in properties
And
I getting Error Attached Screenshot pls look & give solution

Capture
Capture955×433 25.1 KB

5

Thanks @Vinit_Mhatre

Above VBA code it’s worked , thank u so much

Another one requested pls guide me for this also

I have a excel contain 30 columns with 5lac data’s

In that Column 7 has date

Required - To filter that column with previous months

I need VBA code for filter column with date

Please provide VBA code for this above request

6

Hi @Balaharish_T ,

Sub FilterPreviousMonthData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim filterDate As Date
    Dim startDate As Date, endDate As Date
    
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Find the last row in Column 7
    lastRow = ws.Cells(ws.Rows.Count, 7).End(xlUp).Row ' Column 7
    
    ' Get the first day of the previous month
    filterDate = DateSerial(Year(Date), Month(Date) - 1, 1)
    
    ' Calculate the start and end dates for the previous month
    startDate = DateSerial(Year(filterDate), Month(filterDate), 1)
    endDate = DateSerial(Year(filterDate), Month(filterDate) + 1, 0)
    
    ' Apply filter for the previous month's data in Column 7
    ws.Range("A1").AutoFilter Field:=7, Operator:=xlFilterValues, _
        Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate
End Sub

Adjust the “Sheet1” name to match your sheet name and make sure the column index (7 in this case) corresponds to the column that contains the dates.

Regards,
Vinit Mhatre

7

Hi @Vinit_Mhatre

Thanks for your support , I need one Clarification

In that column contains data from 2021 , If above code will work for this ?

8

Absolutely, the code provided can filter data from any year as long as the date is formatted properly in the Excel column. If your date column contains data from 2021, the code to filter the previous month’s data will still work correctly.

Mark as solution if your issue got resolved

Thanks,
Vinit Mhatre

9

Thanks @Vinit_Mhatre

Above both VBA code is working for both scenarios , Thank you very much

1 Like
10

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.