Solved - Excel Spreadsheet Loop followed up with Flow Decision


#1

Hi All

Wonder if someone could assist me or provide some knowledge as I have been trying to get this right alone for the past week but I still cannot get it to work, despite the tutorials/guides.

Basically I have sequence set up whereby values are read from a spreadsheet column by column and then at the end of the sequence, my variable for the row is increased by 1 and then pointed back to the start of the flow.

This means it is working fine, and running through the whole sheet properly and doing everything perfectly.

Except when I wish to end the process, I would like some button presses and a message pop-up to say it is done.

The Row number is increased by one and then looped back to the start of the sequence:

I would like a flow decision to sit inside the loop so that if there are any rows left, the sequence begins again, however if there are no more rows, then the process ends with the OK button presses and the pop up message.

Any assistance would be greatly appreciated.

Thanks

Scott.


#2

Hello,

This is what I did to perform a loop and end when data is finished…

- Assign the DataRow using the RowNumber in the DataTable or Array/List at the start of the loop, which gives you an error when the RowNumber is higher than the number of rows in the DataTable or Array/List.

-Place the Assign in a Try/Catch, then when the error is thrown assign a boolean variable in the Catch that tells your Workflow that it’s done.

- Followed by the Decision that exits your loop.

Hope that makes sense. Thanks.


#3

Hey,

one option would be to use a flow decision that asks if the row item, that you’re reading, is blank then move on and do something else, otherwise loop round. of course this doesn’t work if it is possible for that cell to be blank in the middle of your data set so you may want to look at how many cells it definitely requires to be blank before it is considered complete.

in pseudo code this could be:

Cellvalue = readcell(“A1”).tostring

if cellvalue="" then EXIT
else
Loop round.

Hope that makes sense. and ClaytonM’s version would also work I just offer an alternative. :slight_smile:


#4

Perfect, thanks guys, I’ll give it a go.


#5

Hi Jake

Struggling to figure out what the actual code inside the flow decision would be, as the RowNumber is an integer.

Do I need to use another Assign value to assign the row number to a different variable as a string?

As the flow decision code I want to use is expecting a string value to read as nothing.


#6

Hi Searl,

You have two sort of options with regards to excel. if you don’t need to write back to the sheet and it is a csv you can use “Read CSV” to a data table and then “use a for each row in data table” to loop through and complete your flow. This will automatically finish once it reaches the end of the data table.

However, if you need to extract an excel and write back or it is not a csv then this method will work (I part described it above)

The main flow looks like this and uses a Boolean as the flow decision called bEnd.

this reads the excel sheet and then asks if the significant variables are nothing using "isnothing(VariableName) as part of an If. If they are then we should assume we are at the end of the report and cancel out by setting bEnd to true. otherwise we have report data and can then carry on with our process flow and loop around.

Hope that makes sense :slight_smile:


#7

Hi Jake

That’s been a great help. Thanks a lot.

For reference for any future queries/forum searches:

I’ve now put an if statement in my first read cell sequence after it reads the cell value, which looks at the the cell being nothing (SuppCode Is Nothing) and assigns either a true or false to a boolean variable LoopEnd.

I’ve directly followed that up with a flow decision which has the condition LoopEnd = True. The false value feeds back into my original sequence to continue the loop of the spreadsheet and the true value goes into a separate sequence of button clicks and lastly a message dialogue saying complete.