Create a separate workbook for each unique bill number,

Help StudioX
,
1

I have an Excel file containing bill data. I need to use UiPath to automate the following process:

  1. Split the data: For each unique bill number found in column E, create a separate Excel workbook.
  2. Naming Convention: Name each new workbook using the format “Bill_[bill_number]_[timestamp].xlsx”.
  3. Worksheet Naming: Name the worksheet within each new workbook “Bill_[bill_number]”.
  4. Special Character Handling: Ensure bill numbers with special characters are processed to create valid filenames.
  5. Performance: Optimize the process by disabling screen updating during execution.
  6. Error Handling & Feedback: Implement robust error handling and provide clear user feedback, including a count of successfully created workbooks.
  7. Data Transfer: Copy the header row (A1:G1) and all rows associated with each unique bill number (columns A:G) from the original file into the corresponding new workbook.

I have working VBA code that achieves this, but I need to implement this functionality in UiPath. Can you provide guidance or a UiPath workflow example that replicates the desired behavior?

DO_Mass_VBA.xlsx (17.9 KB)

ExcelFile
ExcelFile488×346 10.3 KB

The image shows a list of Excel files with names containing "Bill" followed by unique numerical identifiers and timestamps, organized within a folder called "DO_Mass_VBA". (Captioned by AI)
The image shows a list of Excel files with names containing "Bill" followed by unique numerical identifiers and timestamps, organized within a folder called "DO_Mass_VBA". (Captioned by AI)352×759 6.16 KB

VBA.txt (2.6 KB)

2

it is about groping data on column “E”
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum

in above options we present a Non-LINQ and a LINQ approach

3

Hi @CHINH_TRANTAN

Can you try this

This image shows an automation workflow that reads data from an Excel sheet, groups it, and writes each group to a separate workbook. (Captioned by AI)
This image shows an automation workflow that reads data from an Excel sheet, groups it, and writes each group to a separate workbook. (Captioned by AI)1164×564 30.7 KB

For Each :

dt_Input.AsEnumerable().GroupBy(Function(x) x("Bill").ToString()).Select(Function(x) x.CopyToDataTable()).ToList()

Write Range Workbook  : 

"Data\"+"Bill_"+currentDataTable.Rows(0)("Bill").ToString+"_"+DateTime.Now.ToString("yyyyMMdd")+"_"+DateTime.Now.ToString("hhmmss")+".xlsx"

currentDataTable.Rows(0)("Bill").ToString

currentDataTable

Output:

A folder containing multiple Microsoft Excel files with similar naming conventions and modification dates on March 11, 2025. (Captioned by AI)
A folder containing multiple Microsoft Excel files with similar naming conventions and modification dates on March 11, 2025. (Captioned by AI)1137×827 37.8 KB

Regards,

1 Like
4

Dear Irtetala,

Thank you so much for your support!

Your idea was exactly what I needed to fix my problem. I really appreciate it.

1 Like
5

@CHINH_TRANTAN

Glad it helped!! Can you please mark it as solution to close the thread.

Regards,

6

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