Excel Create Table Activity - "Range"

Oh gotcha.

Do you know how many columns you want for the table?

Don’t know how many columns or how many rows in the table, this varies

Do you have data in the Excel file that you can use a Read Range on? If you get the data table of the data, then you can use its row and column count to generate the range.

How do I efficiently convert the number of columns into the last column index? e.g. AC1, BX1 etc

Also I was hoping if there was a solution like one where I just select the top left cell and it automatically detects the entire range of data…

Right, so if it’s just until Z, then you can simply use Convert.ToChar(dt1.Columns.Count+64) to change it to a letter.

However, if you want to go passed that, you will need to do some calculations with loops and modulo.
Here is one of my old solutions. Let me know if you can’t get that working:

1 Like

If you want to use the Select Range, then you would need to calculate the range from the data table. Another thing you can do though, is use keystrokes in TypeInto to perform the select all data… Ctrl+a


Ok I used Select Range and it picked up a bunch of stuff as extra data that I do not need… possibly some extra formatting, whitespace, etc… the range extended way beyond the table itself… how do I remove the extra data?

The Select Range activity will require the exact range I believe. You might have an easier time selecting the range with data by using TypeInto with Ctrl+a ( "[d(rctrl)]a[u(rctrl)]" )

I should also mention that using the Invoke VBA activity is an option if you know a little vba syntax, which works inside an Excel Scope.

Sorry, i used Read Range, specified “A1” and it picked up the extra data

The Read Range should only pick up the data. Did you use the Workbook Read Range or the Excel Read Range?

I used Excel Read Range from the App Integration menu

Can you try something? Open the Excel file and highlight all the empty rows below the data (highlight row below last row, then press Ctrl+down). With all the empty rows selected, go to the Home ribbon at top, and go to the Clear menu on the right side and choose “Clear All”.

Then, try the Read Range. I’m just wondering if there is extra hidden data that you don’t know about.

Would preferably not do this because the workflow is operated by a client and they are supposed to run this workflow against the data which they receive and I wouldn’t want to complicate things

You should probably create a copy as a sample file to develop with. I was just trying to rule out there there is actually data that you don’t know about on the file, but understand if you don’t want to mess with it. If you create a copy, then there won’t be any issues.

I’m also currently not on my machine with Studio, so I can’t really mess with things on here. If you want you can also provide some screenshots of your activities, and maybe I can help identify any other problems.

If not today, I can get back to you tomorrow again.

The Ctrl+A method works to select the range of data, how do I extract the range string from here?

For the Ctrl+a method to work, you would need to probably create the table also with keystrokes. I looked it up on Google and it says Ctrl+T or Ctrl+L.

I’m not quite sure how to get the range of a selection, unless you use Invoke VBA. But, like I said, you can create a table with keystrokes too.

If I create the table via keystroke, how do I access the table variable to perform tasks like sort table in UiPath? The table name appears to be dynamically generated by Excel

First, I’ll let you know that you can get to the Table Name in the ribbon, because a Table adds a Design tab at the top, and you can use Alt key combo to the textbox. Then, you can use Get Text or Ctrl+c with Get Clipboard. For the Alt key combo, press the Alt key and follow the Characters that pop up on the ribbon. - you are essentially simulating how you would it manually with keystrokes.

Second, getting the Read Range to work properly would still be the ideal method, because it would be more reliable by using activities. So you might revisit that as well.


Could you please explain how to use row and column count to generate the range. Thanks
I am writing a DataTable to Excel with Write Range and than

I am trying to use create table activity and after that create pivot table activity.

For create table activity it needs range that can change on every run.