Working with Excel in RPA

Microsoft Excel is one of the most common data sources in business automation. Companies store transaction records, employee data, vendor lists, customer information, and reports in Excel spreadsheets. Almost every RPA project involves reading from or writing to Excel at some point. Learning to work with Excel in RPA is a fundamental skill.

Two Approaches to Excel Automation in UiPath

Approach 1: Excel Application Scope (With Excel Open)

This approach opens the actual Excel application on screen and works with it visually — the same way a human would. It requires Microsoft Excel to be installed on the bot machine. This gives access to all Excel features including macros, charts, and formatting.

Approach 2: Workbook Activities (Without Excel Open)

This approach reads and writes Excel files in the background without opening the Excel application at all. It is faster, runs on machines without Excel installed, but supports fewer features. Best for bulk data operations that do not need formatting or macros.

Comparison Table

Common Excel Activities in UiPath

Read Range

Read Range reads all data from a specified sheet (or a specific cell range) and loads it into a DataTable variable in memory. This is the most common starting point for processing Excel data.

 Read Range
 Workbook Path: "C:\Data\Invoices.xlsx"
 Sheet Name: "January"
 Range: "" (empty = read entire sheet)
 Output: dt_invoices (DataTable variable)

 Result: dt_invoices now contains all 200 rows from January sheet

Write Range

Write Range writes a DataTable from memory back to an Excel sheet. Use it to output results after processing.

 Write Range
 Workbook Path: "C:\Data\Results.xlsx"
 Sheet Name: "Processed"
 Starting Cell: "A1"
 DataTable: dt_results

 Result: All rows from dt_results are written to Results.xlsx

Read Cell / Write Cell

These activities work on a single cell. Read Cell retrieves the value in one specific cell. Write Cell puts a value into one specific cell.

 Read Cell
 Cell: "B2"
 Output: vendorName (String variable)

 Write Cell
 Cell: "E5"
 Value: "POSTED"   → writes the word "POSTED" into cell E5

Append Range

Append Range adds new rows to the bottom of an existing sheet without overwriting existing data. Use it for logging — add one result row each time the bot processes a transaction.

 Append Range
 Sheet: "AuditLog"
 DataTable: dt_logRow
 (dt_logRow contains one new row: Invoice No. | Status | Timestamp)

 Each time the bot processes an invoice, a new row is appended.

For Each Row In DataTable (Loop Through Excel Data)

After reading an Excel file into a DataTable, use For Each Row to process each row one by one.

 Read Range → dt_invoices

 FOR EACH row IN dt_invoices:
   invoiceNo  = row("Invoice No").ToString()
   vendorName = row("Vendor").ToString()
   amount     = CDbl(row("Amount"))
   dueDate    = CDate(row("Due Date"))

   → Process this invoice in SAP
   → Log result

Filter Data Table

Filter Data Table reduces a DataTable to only the rows matching a condition. Use it to separate records before processing — for example, only process invoices where the status column is "NEW".

 Filter Data Table
 Input: dt_invoices
 Condition: Status = "NEW"
 Output: dt_newInvoices

 dt_newInvoices now contains only unprocessed invoices.

Full Excel Workflow Example: Invoice Processing

 STEP 1: Read Excel file into DataTable
 ─────────────────────────────────────
 Read Range ("InvoiceList.xlsx", "Sheet1") → dt_allInvoices

 STEP 2: Filter for unprocessed invoices
 ─────────────────────────────────────────
 Filter dt_allInvoices where Status = "PENDING"
 → dt_pendingInvoices

 STEP 3: Loop through each pending invoice
 ──────────────────────────────────────────
 FOR EACH row IN dt_pendingInvoices:
   Read: invoiceNo, vendor, amount, date

   Call: PostToSAP(invoiceNo, vendor, amount, date)
   → Returns: sapDocNo, postStatus

 STEP 4: Update Excel with results
 ──────────────────────────────────
   Find original row in dt_allInvoices where Invoice No = invoiceNo
   Write Cell: Status = postStatus
   Write Cell: SAP Doc No = sapDocNo

 STEP 5: Save and log
 ─────────────────────
 Append result row to "AuditLog" sheet
 Save Workbook

Excel Tips for RPA

• Always use Add Headers = True in Read Range so column names are accessible by name instead of index.
• Convert cell values to the correct type before using them: .ToString() for text, CInt() for integers, CDbl() for decimals.
• Close the Excel Application Scope properly to release the file after the bot is done — otherwise the file stays locked.
• Use Append Range for logging rather than Write Range, which overwrites existing data.
• Test with a small sample file (5–10 rows) before running on the full dataset.

Summary

Excel is one of the most common data sources in RPA workflows. The key activities are Read Range (load all data into memory), Write Range (output results), Read Cell / Write Cell (single cell operations), and Append Range (add rows without overwriting). After reading data into a DataTable, use For Each Row to loop through each record. Filter Data Table narrows the dataset to relevant rows before processing. Proper data type handling and file management prevent the most common Excel-related bot errors.