I have two questions here,
1)I wanted to use VBA code to reduce execution time for large data?
2)I want to merge two files using which have same row data?
Hi @shama93
=> Use two read range workbook activities to read the two excel files and store it in two datatable. Let’s call it as dtFile1 and dtFile2.
=> Use Assign activity to create a new Datatable. Create a new Datatable variable called mergeDt
- Assign → mergeDt =
Use the below vb code to merge the datatable which have same row data.
Dim mergedDt As New DataTable()
If dtFile1.Rows.Count > 0 AndAlso dtFile2.Rows.Count > 0 Then
mergedDt = dtFile1.Clone() ' Cloning the structure of dtFile1
Dim keyColumn As String = "YourCommonKeyColumnNameHere" ' Replace with the actual common key column name
For Each row1 As DataRow In dtFile1.Rows
Dim key As Object = row1(keyColumn)
Dim row2 As DataRow = dtFile2.Select(keyColumn & " = '" & key.ToString() & "'")(0)
Dim newRow As DataRow = mergedDt.Rows.Add()
' Merge data from the two rows into the new merged row
For Each col As DataColumn In dtFile1.Columns
newRow(col.ColumnName) = row1(col.ColumnName)
Next
For Each col As DataColumn In dtFile2.Columns
If Not mergedDt.Columns.Contains(col.ColumnName) Then
mergedDt.Columns.Add(col.ColumnName)
End If
newRow(col.ColumnName) = row2(col.ColumnName)
Next
Next
Else
End If
=> Use write range workbook activity to write the mergeDt to excel.
Hope it helps!!
Try this macro…save it as text file and which ever is you based file …use excel scope on it and pass the second excel as argument
Sub MergeDataFromSecondExcel(filePath As String)
Dim srcWorkbook As Workbook
Dim destWorkbook As Workbook
Dim srcWorksheet As Worksheet
Dim destWorksheet As Worksheet
Dim lastRow As Long
Dim i As Long
Set srcWorkbook = Workbooks.Open(filePath)
Set destWorkbook = ThisWorkbook
Set srcWorksheet = srcWorkbook.Worksheets(1)
Set destWorksheet = destWorkbook.Worksheets(1)
lastRow = destWorksheet.Cells(destWorksheet.Rows.Count, "A").End(xlUp).Row
For i = 1 To srcWorksheet.UsedRange.Rows.Count
srcWorksheet.Rows(i).Copy destWorksheet.Rows(lastRow + i)
Next i
srcWorkbook.Close False
End Sub
Assumption is you have only one sheet else we can add sheetnames also here to read
Hope this helps
Cheers
Is there a way to reduce the execution time as my UiPath code is taking 1.5 hours.
Is there a way to reduce the execution time as my UiPath code is taking more than an hour to run the code.
this vb code can reduce I believe…
another way can be to use copy/paste range instead of using loop or so
And the time depends on what code you are using how big file is and all…without knowing what you are doing it is difficult to tell what can be done better.If possible please show the code what you are doing and all
cheers
Max of time will be reduced by the vb code only. The activities exception takes much time than the vb code.
Time taking is also depend on the Excel data if it has large data it takes longer time to execute.
Hope you understand!!
I don’t see any code snippet.
