Solved

Can we do Excel Manipulations (Row move, copy, delete) with Tosca?

  • 13 October 2023
  • 1 reply
  • 93 views

Userlevel 3
Badge

Excel Manipulations (Row move, copy, delete)

Hi, I would like to use Tosca to do some excel manipulations, in essence building a kind of archiving routine.
See attached excel file as example.

We have a list of rows in 1 excel tab "MAIN".  This list is populated by manual/automated processes outside of TOSCA.

We would like to build a TOSCA routine which is

  • reading this "MAIN" tab
  • identifying items/rows which are older then 30 days (based on the values in Column D)
  • then copies those rows to tab "ARCHIVE" at the first available next row
  • then deletes the row in the "MAIN" tab

 

Is this something which can be done?  and if so, how can we do this?  

  • copying excel rows
  • deleting excel rows

 

Thanks,

[Originally asked and solved on Support Hub]

icon

Best answer by Community Administrator 13 October 2023, 14:33

View original

1 reply

Userlevel 4
Badge +2

Below modules should be added first.

1. TBox open Excel workbook

2. TBox define excel range

3. TBox range manipulation

4. TBox close workbook.

 

Flow : 

  1. Create 2 folders, 1 for tab "Main" and other for tab "Archive" (to move rows from Tab Main to Tab Archive)
  2. Get the RowCount first from the Tab "Main", Get the RowCount from the tab "Archive"
  3. Set RowCount (Tab "Main") in repetition property for 1st folder.
  4. Get the count of all rows which has date older than 30 days ( Use DATE function) ({DATE[][-30d][]}) *Set DATE format as you need )
  5. The tricky part is to copy the values in a single rows, 1 by 1 setting in 3 buffers for Col A, B & C. (You might need to run one more repetition inside the 2nd folder).  
  6. For another tab "Archive", get the row count and set row count on repetition for 2nd folder
  7. The buffer values can be copied in the same order for Tab "Archive"

Alternative flow- 

  1. Create a macro in the same excel which does the same task.
  2. Run the macro through Tosca and save excel. (You should be using TBox Open Excel workbook, TBox Run Macro & Tbox Close Excel Workbook)

 

Reply