How to dynamically add data to excel

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


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.

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:

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.

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?

@Bonnero @rkg.abhishek Maybe, this solution will be helpful in this case?

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

1 Like

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:

1 Like

@Bonnero I’m glad it helped

@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]

Hi, your information on this topic helped a lot, however the problem I am having is that it only works for the first set of data that i am pasting, the second set of data is being pasted after 11 rows.

As you can see 1-4 was there, the code pasted 5-9 as expected, then the second set of data it paste the info 11 rows below. Do you perhaps know why?

Can you share your recording to have a look?

Its not allowing me to upload the file, however these are the screen shots.

In order to upload the file, you need to zip it. Please also share the Excel files you use.

when i made this change to the expression value, i got the desired results, however I am not sure if it will work if i have more(Tables) data to be added.

will send the zip to you in a moment.

1 Like (1.3 KB)
Combine.xlsx (7.9 KB)
D1.xlsx (8.8 KB)
D2.xlsx (9.3 KB)

The excel sheet named combined( has 4 rows of data).
Excel D1 and D2 have data that needs to be pasted into excel sheet named combine.

this is just a test sample, my actual work entails working with thousands of entries on separate excel sheets.

I think what you need to achieve can be done easier if you use a For Each loop.
Save paths to Excel files from which you need to get data in a list variable, and for each:

  • read the data into a table1
  • open file “Combine”, read existing data into table2, calculate the number of rows, insert table1 starting from the number of rows + 1.
    In each loop, it will insert the data 1 row after the previous table.

Here is a sample recording (907 Bytes)

Thank you so much, however it fails to execute step 2, I always had a problem when i put my file path in a list, that is why i simply open by browsing my file location.

How does the filepath look in the List? Does it have quotes?

yes it does.

If you delete the quotes at the beginning and the end of the file path, the action should work correctly.