Excel Task Takes Too Long to Run – Need Help Optimizing Workflow

[Mohamad_Musthak]

Hi UiPath Community,

I’ve built a workflow in UiPath where I:

1. Read data from an Excel file
2. Filter rows where Posting Key = “40”
3. Write filtered results back to the same sheet (e.g., “SABB”)
4. Add a “Total” row at the end of the Amount in local currency column

The logic works fine, but the workflow takes a long time to run, especially as the number of rows grows. It sometimes freezes or delays heavily when writing the data or calculating the total.

Here’s what I’m currently doing:

• Reading Excel using Read Range
• Filtering with a For Each Row and If condition
• Summing with a loop
• Writing each row in the output sheet
• Adding total with another Append range

What I Need Help With:

• How can I speed this up to (5 to 10 minites)?
• Should I use Filter DataTable or LINQ instead of looping?
• What’s the fastest way to write data and totals to Excel?
  in the excel file have 17500 row

Thanks in advance for your help!
– Musthak

[ashokkarale]

@Mohamad_Musthak,

Try this approach.

1. Don’t read data from an Excel file. Just Open the excel file using Excel Process Scope and Use Excel File activity
2. Filter rows where Posting Key Not equal to “40” using Filter activity to apply filter on excel file.
3. Use Delete Rows activity to delete the filtered unwanted rows.
4. Now you will have the required data where Posting Key = “40”. Add a “Total” row at the end of the Amount in local currency column

[Mohamad_Musthak]

i already try this bro
but still take more than 2 to 3 hours

[ashokkarale]

@Mohamad_Musthak,

Try running the bot with Profile Execution enabled and observe which activities are taking how much time to find the pain points.

[Mohamad_Musthak]

how to enable this bro

[ashokkarale]

@Mohamad_Musthak,

Just click on it once and it should look like this one

[Anil_G (Anil Gorthi)]

@Mohamad_Musthak

In excel process scope..select process mode as automation,kill when session exists,disable macros this will make sure it is faster

Cheers

[Mohamad_Musthak]

still same problem bro

[husone (Son Huynh)]

@Mohamad_Musthak You can write VBA code and use Activities - Invoke VBA activity. It does not require an .xlsm Excel file.

[sonaliaggarwal47 (Sonali Aggarwal)]

Hi @Mohamad_Musthak

Could you please try with the extension method or LINQ mentioned in the below doc? These both are faster. Try with both one by one and see which one gives you faster response.

Four Ways to Filter Data Tables in UiPath Studio | UiPath

Also linking 1 forum post that shares LINQ syntax for such scenario.

Filtering data table more quickly

Hi, I read in an excel sheet that is very large and i want to filter the rows based on a string (filteredValue). This method works but takes several minutes to execute due to the size of the data table i presume. is there a faster way to do this? New to UiPath so thanks for the help [image]

Hope this helps.

Regards
Sonali

[husone (Son Huynh)]

@Mohamad_Musthak it takes 2 seconds with 20000 rows.

Code.zip (246.5 KB)

[Mohamad_Musthak]

Error bro
Invoke VBA: Programmatic access to Visual Basic Project is not trusted

[husone (Son Huynh)]

Make sure you enable macros in your Excel settings.

[AkshaySandhu (AkshaySingh Sandhu)]

@Mohamad_Musthak

• Writing each row in the output sheet

this step here is what causing high execution time. Not sure why you are writing for each Row . Imagine opening excel, writing some data, saving, closing it and doing it for 17500 times

When you are in loop update your data in datatable itself and use Add Data Row to add the Total amount and only once write it back to excel file. You might need to clear the Sheet before writing the data, if possible create a new file.

for below: Are you summing the numbers in loop itself?

@Mohamad_Musthak

• Summing with a loop

[Ravikirank_Mca (Ravikiran Kandikonda)]

Zip & Email Code

’ API declarations for Shell32 for zipping
Private Declare PtrSafe Function ShellExecute Lib “shell32.dll” Alias “ShellExecuteA” _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

’ Function to zip folder with password
Public Sub ZipFolderWithPassword_U()
Dim sourceFolder As String
Dim zipFilePath As String
Dim password As String
Dim shellApp As Object
Dim sourceItems As Object
Dim zipFile As Object
'Dim fso As Object
Dim subFolder As Object
Dim folderCollection As Object
Dim i As Long
Dim contract_keys As String
Dim mailTempath As String
Dim wb1 As Worksheet

'On Error GoTo ErrorHandler

Set wb1 = ThisWorkbook.Sheets(“Tracker”)
TemplatePath = wb1.Range(“U10”).Value 'Word template
SavePath = wb1.Range(“U12”).Value 'Output folder
Wb3Path = wb1.Range(“U18”).Value '3rd workbook file path
startNum = wb1.Range(“U16”).Value
endNum = wb1.Range(“U17”).Value
defCC = wb1.Range(“U24”).Value
mailTempath = wb1.Range(“U25”).Value
password = “Mizuho2025"”"
OutputZipPath = wb1.Range(“U26”).Value
sourceFolder = ThisWorkbook.Sheets(“Tracker”).Range(“U12”).Value
’ Create FileSystemObject
'Set fso = CreateObject(“Scripting.FileSystemObject”)

Dim contract_To As String
Dim contract_CC As String

Set wb3 = Workbooks.Open(Wb3Path)

lastRow = wb1.Cells(Rows.Count, “A”).End(xlUp).Row

For i = startNum To endNum

If Not wb1.Rows(i).Hidden Then
    'Read contract values
    wb1.Cells(i, "W").Value = "Row Not Hidden"
    
    contract_keys = wb1.Cells(i, "A").Value
    contract_name = wb1.Cells(i, "B").Value
    contract_To = ""
    ''Load To address from Workbook 3
    lastwb3row = wb3.Sheets("Sheet1").Range("A65536").End(xlUp).Row
    For Each cell In wb3.Sheets("Sheet1").Range("A2:A" & lastwb3row)
    '''Check Below Wb3 A Value = Wb1  A Value
        If Not wb3.Sheets("Sheet1").Rows(cell.Row).Hidden Then
            If cell.Value = contract_keys Then
                 contract_To = cell.Offset(0, 8).Value
                 'contract_CC = cell.Offset(0, 11).Value
                 Exit For
             End If
       End If
    Next cell
    
    If contract_To = "" Then
        wb1.Cells(i, "W").Value = wb1.Cells(i, "W").Value & ", No Match record found in Wb3"
    Else
        wb1.Cells(i, "W").Value = wb1.Cells(i, "W").Value & ", " & contract_To & " Match record found in Wb3"
    
        ''How it needs to save as Ouput Path
        zipFilePath = OutputZipPath & "\" & contract_keys & ".zip"
        '''Password protected zip file creation
        CreatePasswordProtectedZipWith7Zip SavePath & "\" & contract_keys, zipFilePath, password, i, wb1
        ''Save an email in Drafts folder(Or any given folder)
        Application.Wait Now + TimeValue("00:00:03")
        wb1.Cells(i, "W").Value = wb1.Cells(i, "W").Value & ", " & " Email Initiating..."
    
        CreateEmailWithAttachment zipFilePath, contract_To, contract_CC & ";" & defCC, contract_keys, mailTempath, i, wb1
        'Application.Wait Now + TimeValue("00:00:10")
    End If
Else
    wb1.Cells(i, "W").Value = "Hidden row, Skipping next..."
End If
On Error Resume Next
Application.StatusBar = wb1.Cells(i, "W").Value & i + 1 & " running...."
On Error GoTo 0

’ Application.Wait Now + TimeValue(“00:00:10”)
Next i
Application.StatusBar = False
MsgBox “Done”
Exit Sub
ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
End Sub

’ Check if 7-Zip is available
Private Function Is7ZipAvailable() As Boolean
Dim sevenZipPath As String

’ Check common 7-Zip installation paths
sevenZipPath = “C:\Program Files\7-Zip\7z.exe”“”
If Dir(sevenZipPath) <> “” Then
Is7ZipAvailable = True
Exit Function
End If

sevenZipPath = “C:\Program Files (x86)\7-Zip\7z.exe”“”
If Dir(sevenZipPath) <> “” Then
Is7ZipAvailable = True
Exit Function
End If

Is7ZipAvailable = False
End Function

’ Create password-protected zip using 7-Zip
Private Sub CreatePasswordProtectedZipWith7Zip(ByVal sourceFolder As String, _
ByVal zipFilePath As String, _
ByVal password As String, Optional i As Long, Optional wb1 As Worksheet)
Dim sevenZipPath As String
Dim cmd As String
Dim result As Long

’ Wait for process to complete
zipExe = “”“C:\Program Files\7-Zip\7z.exe”“”
zipFile = “”“” & zipFilePath & “”“”
sourceFolder = “”“” & sourceFolder & “”“”
password = “-p” & password & “”

wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & “, " & " Zipping Initiating..”

cmd = zipExe & " a -tzip " & zipFile & " " & sourceFolder & " -r " & password

Shell cmd, vbNormalFocus
’ Wait for process to complete
Application.Wait Now + TimeValue(“00:00:03”)

For waitTime = 1 To 5
If Dir(zipFilePath) = “” Then
wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & “, Waiting " & waitTime * 10 & " more seconds”
Application.Wait Now + TimeValue(“00:00:10”)
Else
Exit For
End If
Next waitTime
If Dir(zipFilePath) = “” Then
wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & ", " & " Zipping Failed at " & zipFile
Else
wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & ", " & " Zipping Successful " & zipFile
End If
End Sub

’ Create and display email with attachment
Public Sub CreateEmailWithAttachment(Optional filePath As String = “”, Optional ToAdr As String, Optional ccAdr As String, Optional contract_keys As String, Optional mailTempath As String, Optional i As Long, Optional wb1 As Worksheet)

Dim outlookApp As Object
Dim outlookMail As Object
Dim zipFilePath As String

Dim ns As Object
Dim destFolder As Object
Dim folderPath As String
Dim parts As Variant
Dim f As Object

On Error GoTo EmailErrorHandler

’ Create Outlook application
Set outlookApp = CreateObject(“Outlook.Application”)
TemplatePath = mailTempath
Set outlookMail = outlookApp.CreateItemFromTemplate(TemplatePath)
msgSavePath = ThisWorkbook.Sheets(“Tracker”).Range(“U28”).Value & "" & contract_keys & “.msg”
With outlookMail
.to = ToAdr
.cc = ccAdr
.Subject = contract_keys & “-” & “Request for identity verification documents for MGeB sub-users with payment authority”
.Attachments.Add filePath
.SaveAs msgSavePath, 3
'.Display
.Save
End With

wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & " ,Email Prepared "

folderPath = Trim(wb1.Range(“U27”).Value)

’ Remove leading “\” if present
If Left(folderPath, 2) = “\” Then folderPath = Mid(folderPath, 3)

’ Split Outlook folder path
parts = Split(folderPath, "")

Set ns = outlookApp.GetNamespace(“MAPI”)
Set destFolder = ns.Folders(parts(0)) ’ First part = mailbox name

’ Loop remaining folder levels (supports sub-folders)
Dim x As Long
For x = 1 To UBound(parts)
Set destFolder = destFolder.Folders(parts(x))
Next x

’ Move the displayed mail
outlookMail.Move destFolder
wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & " ,Email Moved to " & destFolder

Set outlookMail = Nothing
Set outlookApp = Nothing
Exit Sub

EmailErrorHandler:
Set outlookMail = Nothing
Set outlookApp = Nothing
wb1.Cells(i, “W”).Value = wb1.Cells(i, “W”).Value & " Error " & Err.Description

End Sub

’ Main procedure that does everything
Sub MainProcedure()
ZipFolderWithPassword
End Sub

---

Word file generation code

Sub GenerateWordFiles()

'Declare Word application and document objects
Dim wdApp As Object              'Word application instance
Dim wdDoc As Object              'Word document instance

'Paths for template, output folder, and 2nd workbook
Dim TemplatePath As String
Dim SavePath As String
Dim Wb2Path As String

'Excel variables
Dim lastRow As Long, i As Long
Dim contract_keys As String, contract_name As String

'Word Table variables
Dim tbl1 As Object, tbl2 As Object
Dim wb2 As Workbook
Dim wsCP As Worksheet            'Connected Parties sheet
Dim CP_LastRow As Long          'Last row of CP output
Dim CP_Row As Long              'Loop counter
Dim tbl1_Row As Long, tbl2_Row As Long

'Read paths from Tracker sheet
''Change below ThisWorkbook.Sheets("Tracker") with your Workbook1 sheet name
'''Change H2,J3,J4 to your path addresses
Set wb1 = ThisWorkbook.Sheets("Tracker")
TemplatePath = wb1.Range("U10").Value     'Word template
SavePath = wb1.Range("U12").Value         'Output folder
Wb2Path = wb1.Range("U14").Value       '2nd workbook file path
startNum = wb1.Range("U16").Value ''Which record onwards you want to run
endNum = wb1.Range("U17").Value   ''Until which record you want to execute

'Create output folder if not exists
If Dir(SavePath, vbDirectory) = "" Then MkDir SavePath

'Get last row in column A of wb1
lastRow = wb1.Cells(Rows.Count, "A").End(xlUp).Row

'Create Word application
Set wdApp = CreateObject("Word.Application")
wdApp.Visible = True                      'Show Word

'Open the second workbook containing the macro
Set wb2 = Workbooks.Open(Wb2Path)

'Loop through input rows starting from value in Tracker A1
For i = startNum To endNum ' lastRow
    If Not wb1.Rows(i).Hidden Then
        'Read contract values
        wb1.Cells(i, "V").Value = "Row Visible"
        contract_keys = wb1.Cells(i, "A").Value
        contract_name = wb1.Cells(i, "B").Value
        
        FileName = SavePath & "\" & contract_keys
        'Create subfolder if missing
        If Dir(FileName, vbDirectory) = "" Then MkDir FileName
        
        FileName = FileName & "\" & contract_keys & ".docx"
        
        If Dir(FileName) = "" Then
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & " ,No Word File, Creating new..."
            'Send contract key into wb2
            ''Change sheet name where it want to place the contract_keys, mention that sheet name
            ''If you want to place it in multiple places, copy the same below line code extra and
            ''change the sheet name and range below.
            wb2.Sheets("Connected Parties Check").Range("D2").Value = contract_keys
            
            'Run macro in second workbook
            Application.Run "'" & wb2.Name & "'!PopulateAndSortCPsDetails"
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & ",Wb2 Macro Successfull"
            'Set reference to CP output sheet after macro runs
            ''If the output needs to pull from different sheet then change the name below
            ''If the output wants to pull from multiple sheet replace the same line
            ''again below with different Set Variable
            Set wsCP = wb2.Sheets("Connected Parties Check")
            
            'Open the Word template for this contract
            Set wdDoc = wdApp.Documents.Open(TemplatePath)
            
            'Replace the tag with contract name
            ReplaceTag wdDoc, "<<Contract_Name>>", contract_name
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & contract_name & " entered in Word Successfull"
            
            
            '------------------ TABLE PROCESSING BEGINS ----------------------------
            
            'Get references to Table 1 and Table 2
            Set tbl1 = wdDoc.Tables(1)
            Set tbl2 = wdDoc.Tables(2)
            
            'Find last row of data from A11 downward from wb2
            CP_LastRow = wsCP.Cells(Rows.Count, "M").End(xlUp).Row
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & CP_LastRow - 10 & " records found after Wb2 Macro"
            
            
            '-----------------------------------------------------
            ' TABLE 1 ? Insert/Delete rows based on CP output
            '-----------------------------------------------------
            
            'Ensure table has correct number of rows
            AdjustWordTableRows tbl1, CP_LastRow - 10   'Subtract 10 because data starts at row 11
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & CP_LastRow - 10 & " rows adjusted in table1 Word file"
            
            'Fill Table 1 rows
            tbl1_Row = 2   'Assuming row 1 is header
            
            For CP_Row = 11 To CP_LastRow
                tbl1.cell(tbl1_Row, 1).Range.Text = wsCP.Cells(CP_Row, "F").Value
                tbl1_Row = tbl1_Row + 1
            Next CP_Row
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & " table1 User Name completed in Word file"
            
            '-----------------------------------------------------
            ' TABLE 2 ? Only authorised rows (Column M)
            '-----------------------------------------------------
            
            'Clear all existing data rows in Table 2 (except header)
            AdjustWordTableRows tbl2, 0
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & " Word table2 adjusted"
            
            tbl2_Row = 2
            
            For CP_Row = 11 To CP_LastRow
                
                If wsCP.Cells(CP_Row, "M").Value = "AuthorisedSignatory" Then
                
                    'Add new row in Table 2
                    tbl2.Rows.Add
                    
                    'Write Column L value into Table 2
                    tbl2.cell(tbl2_Row, 1).Range.Text = wsCP.Cells(CP_Row, "L").Value
                    
                    tbl2_Row = tbl2_Row + 1
                End If
            
            Next CP_Row
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & " Word 2 table Filled"
                
            'Save Word file
            wdDoc.SaveAs2 FileName
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & "," & FileName & " File Saved"
            
            'Close Word file
            wdDoc.Close False
        Else
            wb1.Cells(i, "V").Value = wb1.Cells(i, "V").Value & ", " & "File already exists, Skip next"
        End If
    Else
        wb1.Cells(i, "V").Value = "Row not Visible, Skipping this Record"
    End If
Next i

'Quit Word application
wdApp.Quit

MsgBox "All Word files generated successfully!", vbInformation

End Sub

Sub ReplaceTag(doc As Object, findText As String, replaceText As String)
With doc.Content.Find
.Text = findText
.Replacement.Text = replaceText
.Forward = True
.Wrap = 1
.Execute Replace:=2
End With
End Sub

Sub AdjustWordTableRows(tbl As Object, requiredRows As Long)

Dim currentRows As Long
currentRows = tbl.Rows.Count - 1          'Minus header

'Add missing rows
While currentRows < requiredRows
    tbl.Rows.Add
    currentRows = currentRows + 1
Wend

'Remove extra rows
While currentRows > requiredRows And requiredRows >= 0
    tbl.Rows(tbl.Rows.Count).Delete
    currentRows = currentRows - 1
Wend

End Sub

[MohammedShabbir (shabbir.qs)]

Just use linq to do your job, you don’t have to write back to excel for every row. It will definitely take a lot of time if you write for every row.
Share a sample or Screenshot of your file ..
Will help you with the linq.

[Maheep_Tiwari]

The delay happens because you are looping row by row and writing to Excel multiple times. For 17500 rows, process everything in memory. Read the sheet once into a DataTable, use Filter DataTable or LINQ to filter Posting Key = “40”, and calculate the total using LINQ. Then clear the target range and write the filtered DataTable to Excel in a single Write Range, followed by one write for the total row. Avoid writing inside loops and prefer Workbook activities.