Delete table rows on table that wasn’t created in the flow

(1) Share

Report

Posted on by kschuster1

Need a flow to automate weekly Excel file cleansing. The files have Tables with temporary data that is replaced each week with new data. The tables feed charts and pivots on other sheets. On Friday the tables are cleaned and on Monday new data is inserted.

This flow is to perform the Friday cleaning, which includes a cell update and deleting table rows. On some Data Tables all rows must be deleted, while also keeping the headers and table structure. Other Tables only require some rows be deleted, based upon a date in one of the columns.

Because the tables were not created within the flow, I don’t have an output variable to reference which table to delete rows on.

The cell update in flow lines 1-7 work perfectl. I cannot figure out how to delete table rows. The actions attempting to get a Data Table aren’t picking up the correct variable. I tried using the Table Name from my Excel file, but none of the delete actions recognize it.

The attached flow shows all the actions I tried. How do I set a pre-existing table as a variable to be used in next steps? Which actions are best used in Desktop for getting the variable and deleting table rows? Is it best done in a cloud flow?

Any help is appreciated.

Delete table rows...

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Michael E. Gernaey 53,969 Moderator on at

Like (2)

Report
Copy link

Link copied!

Hi,

to get better answers, this should have gone to the Desktop version, not the cloud version of flows.

I cannot move it, but hopefully someone will see it :-)

Was this reply helpful? Yes No
kschuster1 28 on at

Like (1)

Report
Copy link

Link copied!

Thank you, FLMike. Looks like i posted this to the incorrect forum. I'll consider this solved and post it elsewhere.

Was this reply helpful? Yes No
Suggested answer

Nived_Nambiar 18,136 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @ kschuster1

Try this flow design

The above piece of code deletes all the rows from table named Table1 which is present in Sheet1

The above piece of code delete the rows whose date column has value >25-06-2024

Excel file used for this

Table1

Table2

Note- please make sure that date column format is of text type else this method/code may not work sometimes due to datetime format issues.

Code can be found below

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\Nived\\Downloads\\sampledata.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance # deleting entire data from the table Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet1''' Excel.GetTableRange Instance: ExcelInstance TableName: $'''Table1''' IsPivot: False FirstColumnIndex=> FirstColumnIndex FirstRowIndex=> FirstRowIndex LastColumnIndex=> LastColumnIndex LastRowIndex=> LastRowIndex Excel.DeleteCells.DeleteCells Instance: ExcelInstance StartColumn: FirstColumnIndex StartRow: FirstRowIndex + 1 EndColumn: LastColumnIndex EndRow: LastRowIndex ShiftCellsDirection: Excel.ShiftCellsDirection.Up Excel.SaveExcel.Save Instance: ExcelInstance # delete based on dates Excel.SetActiveWorksheet.ActivateWorksheetByName Instance: ExcelInstance Name: $'''Sheet2''' Excel.GetTableRange Instance: ExcelInstance TableName: $'''Table2''' IsPivot: False FirstColumnIndex=> FirstColumnIndex FirstRowIndex=> FirstRowIndex LastColumnIndex=> LastColumnIndex LastRowIndex=> LastRowIndex Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: FirstColumnIndex StartRow: FirstRowIndex EndColumn: LastColumnIndex EndRow: LastRowIndex ReadAsText: True FirstLineIsHeader: True RangeValue=> ExcelData Variables.FilterDataTable DataTable: ExcelData FilterParameters: { ^['RULE', 'COLUMN', 'OPERATOR', 'VALUE'], [$'''''', 0, $'''GreaterThan''', $'''25-06-2024'''] } FilteredDataTable=> FilteredDataTable LOOP FOREACH row IN FilteredDataTable Excel.FindAndReplace.FindSingle Instance: ExcelInstance TextToFind: row[0] MatchCase: False MatchEntireCellContents: True SearchBy: Excel.SearchOrder.Columns FoundColumnIndex=> FoundColumnIndex FoundRowIndex=> FoundRowIndex Excel.DeleteRow Instance: ExcelInstance Index: FoundRowIndex Excel.SaveExcel.Save Instance: ExcelInstance END Excel.CloseExcel.Close Instance: ExcelInstance

Hope it helps !

Thanks & Regards,
Nived N
LinkedIn: Nived N's LinkedIn
YouTube: Nived N's YouTube Channel
Blog: Nived Nambiar's Blogs
Found my answer helpful? Please consider marking it as the solution!
Your appreciation keeps me motivated. Thank you!

Was this reply helpful? Yes No