Excel Create Table Activity - "Range"

Help
21

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

22

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.

Regards.

23

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.

24

Sorry for late reply.

Simple way for your question would be:

  1. to Read the existing data in the spreadsheet; this will give you the data you can lookup.
  2. then use the Lookup Range activity under the provided Excel “Processing” activity package, so update this if needed.
  3. in the Lookup Range activity, you can look for the last value in the first column of the spreadsheet with dt1.Rows(dt1.Rows.Count-1)(0).ToString
  4. you can get the last value in the last row with dt1.Rows(dt1.Rows.Count-1)(dt1.Columns.Count-1).ToString

Alternatively, rather than the Lookup Range activity, you can just pull the cell value with calculating it based on row and column number.

  1. Read data in spreadsheet
  2. rowNumber = dt1.Rows.Count+2
  3. columnLetter = “A” … or for last column: columnLetter = Convert.ToChar(dt.Columns.Count+65).ToString
  4. if the column can go past Z, then you will need a more mathematical solution for the AA thru XF. Search the forums for that solution.

Regards.

25

Thanks @ClaytonM
Just had one question why the “+2” in the alternative method?

I am trying to create a pivot table so I need the headers. Should I use “+1” instead?

26

+2 is for the first row in the data, cause 1 for the header and 1 since it starts on 0.

Use +1 if you want to treat the first row as the header row.

27

Thanks @ClaytonM appreciate your help.

28

Excel has this interesting feature that you can test by pressing CTRL+End. CTRL+END should move cursor to cell that is at the last row with data and column that is last column with data. If your data is just a table then CTRL+END will go to the end of your table (bottom right corner).
Create new empty workbook. When pressing CTRL+END it should set cursor at A1 cell, as there is still no data. Then add some data let’s say in C4 cell. If you press CTRL+END again cursor moves to C4, which is expected behaviour. But if you delete contents of that cell, move cursor back to A1 and press CTRL+END it will still move to C4, as if there was still your data.
It also happens when there was some formatting in a cell.
I’m guessing that this behavious is a basis of Read Range activity when Range is not explicitly provided and can produce DataTable with additional rows/columns that are empty.
My recommendation is:

  • advice business to use new blank workbook when preparing their data
  • if it’s still happening filter DataTable from empty rows and columns
29

Hi @DEATHFISH ,

To know the last row and last column of the sheet

And then apply the “Format As Table”

Regards
Balamurugan.S