Best Approach to Interact With Excel

What is the best approach for working with Excel in UiPath? In VBA you create an instance of Excel, Workbook, Worksheets, Sheets, Ranges, etc. then loop through the collection. UiPath seem to want to return the data in the file into a datatable, which requires you to work with the data outside of Excel. UiPath seems to assume you know the layout of the file.

For my current project I really need to work with the data directly in Excel since the files aren’t uniform in the number of sheets and structure of the data. I need to loop through the sheets and data within each sheet to find what I am looking for and update the data accordingly.

Is this approach plausible in UiPath?

Yes ofc, you have activities that work directly with cells, i will also remeber you that everything you do in VBA, you can do in uipath as well, also in vb.net which will be similar to vba…

I am getting stuck at the point of creating a worksheet instance so I can loop through the rows and columns of the worksheet and create a range based on the CurrentRegion.

  1. I am using the “Excel Application Scope” to read the file, which returns a WorkbookApplication (wrkApp)
  2. “Get Workbook Sheets” to return an array strings that contain the name of the Sheets (arryShts)
  3. “For Each” to loop through the array to find the name of the sheet I am looking for. The sheets have variations in the name.
  4. Assign the name of the sheet I want to wrkSheetNM
  5. Next Step?
  • wrkApp.Sheets(wrkSheetNM) to assign it to a WorkSheet variable does not work

  • Dim wrkSht as Microsoft.Office.Interop.Excel.Worksheet in “Invoke Code” errors as:

    “Error ERROR Validation Error No compiled code to run
    error BC31539: Cannot find the interop type that matches the embedded type ‘Microsoft.Office.Interop.Excel.Worksheet’. Are you missing an assembly reference? At line 1”

I am an importing the namespace Microsoft.Office.Interop.Excel

https://www.dotnetperls.com/excel-vbnet
but if you are going down this road, i would do inside an Invoke Code activity instead…

1 Like

Thank you

2 Likes

I did have to add the following to the xaml file:

Microsoft.Office.Interop.Excel

Based on the following post:

1 Like

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