Monthly Close Workbook and Regional Distribution
At the close of each month, a controller opens Sales_Close.xlsx: 14 worksheets, about 120,000 rows total, several formulas, and one sheet locked by a colleague during business hours. The bot must refresh a pivot-style summary, save a copy, and email a filtered extract to 38 regional managers. Each manager should receive only their region's rows plus a PDF summary. The mailbox currently holds 400 unread messages and the SMTP relay limits each message to 10 MB. The bot runs on a server without Microsoft Excel installed on one of its two robots.
1. Recommend an Excel automation approach that works on both robots. Address formulas, pivots, and the robot without Excel.
Read the worked answer
The deciding constraint is that one robot has no Excel installed. Workbook activities can read and write .xlsx without Excel, but they do not recalculate formulas or refresh pivots. If the summary depends on live formulas or a pivot cache, Workbook activities will deliver stale or missing values. So either install Excel on the second robot and standardize on Excel Application Scope or Use Excel File, or move the summary calculation into UiPath DataTables and write values only.
The second option is more portable but requires reproducing formula logic and testing it against the controller's numbers. Do not let one robot use Interop and the other use Workbook silently: the same job would produce different outputs. Choose one path, document it, and add a reconciliation check on total sales and row counts before sending.
2. Design the distribution to 38 managers within the 10 MB message limit, and handle the locked workbook.
Read the worked answer
Send one message per manager in a loop, not 38 manual sends. Build a region-filtered DataTable, write it to a CSV or XLSX in a per-run temp folder, generate the PDF summary, and attach both. Check the combined attachment size before sending; if it exceeds 10 MB, send a compressed file or a secure link, or split the export by sub-region. Do not embed 120,000 rows in the body. Use a queue or retry scope per recipient so one bad address does not stop all 38; log message ID and recipient.
For the locked workbook, retry opening with a bounded wait, for example 5 attempts over 15 minutes. If it is still locked, do not process a stale copy silently; notify the controller and pause the job. After opening, force recalculation and verify key totals before distribution.