How to dynamically add data to excel

excel
rpaexpress
how-to
set-range

#1

i can read all the 10 rows using mouse click to copy and paste for first page and can save it in excel at required cell i.e. A11.

now the problem arises when i have to add another set of data how can i determine it has to add at A12.
in the screen shot you can see i am able to save data till A12 n the page number can be dynamic whether one or more in the web portal

image


#2

If you put the set range action to “current-end” instead of “a2-end” it should work as the spreadsheet should save the last active cell as current. After the set action just move the active cell to beginning of row and below so the next row is active before the next set action is performed.


#3

How do you actually specify the address of the “current” cell?

When I type “current” into the ‘From Cell’ the loop code doesn’t run. I know the current cell is saved on exiting excel but unless I can specify it in the ‘from cell’ command with a variable - ie set variable “position” = current cell address (somehow) or if I can use a magic word like “CURRENT”

This code doesn’t work:


#4

Not sure if I understand the question. You can set any cell as current for example using the set active cell action.

Set active cell - coordinates - B2
sets B2 active and is your current cell as long as you don’t specify otherwise.


#5

Yes I didn’t explain very well. Here are the steps:

  1. Paste some values into a spreadsheet ending at (say) cell A7
  2. Set cell position “Cell Below” (cell A8)
  3. Close the excel sheet which now remembers that current position is A8
  4. Get some more data from another spreadsheet into a table variable
  5. Try to paste the data starting at cell A8 (“Cell Below”) using the “set range” action
  6. Fail! :persevere:

Set range needs to have either a variable with an address or a fixed value so either it’s “From cell” should be either hard coded A8 (obviously not working to add to a spreadsheet at the bottom line every time) or it needs to have a variable with the value A8 in it - ${paste_from_here} which somehow gets allocated the value A8.

So the question is: Just before the spreadsheet closes can I somehow assign ${paste_from_here} to the current cell address for use in the “set range” command?


#6

@Bonnero @rkg.abhishek Maybe, this solution will be helpful in this case? Issue in Set range with down key (Spreadsheet)

If the number of rows in the table is not known, you can calculate it using Expression action.


#7

Thanks Alesia

There is just one change to your code above to make it work - in the first “open spreadsheet” you should use Get Range rather than Set Range to load the excel sheet into the table. The workflow looks like this:

Open spreadsheet
Load into table
Count rows and columns
set the “next free row” to “number of rows” plus 1

Then if you want to paste in new data below the last data rows in a spreadsheet you can use this pointer as the starting place for either setting the current cell or pasting from a table.

Thanks a lot for the pointer on this - it has helped a lot with a few different issues I was having. :fist_right::fist_left:


#8

@Bonnero I’m glad it helped


#9

@Bonnero @timriewe @rkg.abhishek By the way, there is a feature request topic where you can vote for a future option for Excel to set range from the current cell Excel Set Range from current cell [Vote]