Excel In the Power Automate Desktop flow Read data with empty or without empty

[Ahmedmuz]

Hi Everyone,

In the Power Automate Desktop flow, after launching Excel, the automation needs to read data from columns A2 to H2. If data is found in these cells, the flow should proceed with the next steps. However, if any blank cells are encountered, the flow should move to the next loop. Could you please assist with this.

Best Regards 

Muzamil Ahmed

[Agnius · Accepted solution]

Well, once you have read your data from Excel, use a loop to iterate through each cell and check if the value of that cell is empty. If it is, use Next loop to skip the rest of the actions inside the loop.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz · Accepted solution]

HI Agnius,

Thanks a lot - If you could show a sample with snap how to use the loop iterate through each cell with one example it will very helpful, as i am a very new to all this ,, Appreciate your help 🙂

Thanks a million times:)

[Agnius · Accepted solution]

The following steps will do it, but depending on what you want to do with the values, you might want to use a different loop (if you need a loop index to write back to Excel):

Also, this assumes that you are only reading a single row (A2 through H2). That's why it's iterating through a single row, too (using %ExcelData[0]%). If you read more than that, you would need an extra loop and the flow would look like this:

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz]

Hi Agnius,

Thanks for the above snap shots, I tried but its not working the the close excel & its not going to next loop can you please check once and help where im doing error - attached is the snap shot for you,,

[Agnius]

Why are you closing Excel in the loop? You should only close it after you're completely done with what you want to do. And you only need to close it once - not within the loop.

[Ahmedmuz · Accepted solution]

Ok Sure, I want the open excel file should close if that is empty once it read then go to next loop or else it will be kept open so,, Can you help me how to do that ? 
Have attached snap shot's for your reference.

[Agnius · Accepted solution]

I do not fully understand your use case, but regardless of what you are trying to do, there are at least 2 things wrong with your flow:

• You use %CurrentItem% in both of your loops. You should change the name of the variable either in the parent loop for files or the nested loop for Excel rows.
• You have Close Excel in row 6 and 12. The action in row 6 will close the file before it stops using it. You are either missing some actions there, to skip to the next file after closing, or should not have the Close Excel action there.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz]

If you don't mind can you give a proper step snap shots as new it will help a lot for me please...?

[Agnius]

No, I cannot, because I don't understand what you are trying to do. Please provide a clear step-by-step instruction of what you want to achieve and then maybe I can come up with a suggestion for the flow.

[Ahmedmuz]

1.Opening the excel Files one by one using the for each loop after Get files in folders step.
2.If the file name contains 12345 it should not open the file and go to next Loop.
3.Other than 12345 file name contains it should open - which is No 6 Launch Excel.
4.No 7th Step is reading the excel file from A2 to H2 row.

5.Created one more For each loop steps 8 within in For each loop  - to perform- If the current excel file cells from A2 to H2 is empty then current excel file should close and go to next loop - to open next excel file.

Hope it helps you..

[Agnius · Accepted solution]

Okay, that clarifies some things.

The problem with your flow was that you were not really going to the next loop in the parent loop when a value was empty in one of those cells. Next loop only works within the scope of the loop that it is in, meaning it was simply skipping to the next cell, but not to the next file. And thus you were also skipping the Close Excel action, too.

Here's the approach I would take:

What this does is that if it finds an empty value in one of those cells, it exits the nested loop. And then it checks the latest cell value again outside the nested loop. It will still hold the last value at the time when the flow exited the nested loop. So, if it was empty there, it will also be empty after exiting the loop. It will then close Excel and then skip to the next file.

Here's a snippet you can copy and paste to PAD:

LOOP FOREACH CurrentItem IN Files
    IF Contains(CurrentItem.NameWithoutExtension, 12345, False) THEN
        NEXT LOOP
    END
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 2 EndColumn: $'''H''' EndRow: 2 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
    LOOP FOREACH CurrentValue IN ExcelData
        IF IsEmpty(CurrentValue) THEN
            EXIT LOOP
        END
    END
    IF IsEmpty(CurrentValue) THEN
        Excel.CloseExcel.Close Instance: ExcelInstance
        NEXT LOOP
    END
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: 11 Column: $'''A''' Row: 2
    Excel.SaveExcel.Save Instance: ExcelInstance
    Excel.CloseExcel.Close Instance: ExcelInstance
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz · Accepted solution]

Hi Agnius,

I tried the above one but its not working - Attached is the videos for your reference thought it will be better.

1.it's not preforming the step number 14 (Close excel & Next Loop) even the excel is empty its switching to Step no 17 and writing which should not do it.

2. it should close the excel and go to next file but its not happening - Please help

Please help - enclosed is the video reference for your understanding.
 

[Agnius · Accepted solution]

I assumed that Read from Excel worksheet would result in a data row variable when you read a single row. But it seems like it results in a data table and thus when you loop through it, the %CurrentValue% contains a row with empty values, and thus it is not empty itself.

To fix this, an additional nested loop is needed. Your loop should look like this:

And here's a snippet for the full flow:

LOOP FOREACH CurrentItem IN Files
    IF Contains(CurrentItem.NameWithoutExtension, 12345, False) THEN
        NEXT LOOP
    END
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 2 EndColumn: $'''H''' EndRow: 2 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
    LOOP FOREACH CurrentRow IN ExcelData
        LOOP FOREACH CurrentValue IN CurrentRow
            IF IsEmpty(CurrentValue) THEN
                EXIT LOOP
            END
        END
        IF IsEmpty(CurrentValue) THEN
            EXIT LOOP
        END
    END
    IF IsEmpty(CurrentValue) THEN
        Excel.CloseExcel.Close Instance: ExcelInstance
        NEXT LOOP
    END
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: 11 Column: $'''A''' Row: 2
    Excel.SaveExcel.Save Instance: ExcelInstance
    Excel.CloseExcel.Close Instance: ExcelInstance
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz]

Hi Agnius, Thanks, its working, forget to ask on more thing & need help to add in this only .. 1.It have to read full for Ex- C column in excel file if the text contains any of these text (D11,D08,D07,ZA1,ZB2,ZC) then only it should go to "Write to excel sheet one Step or else it should close the excel then go to next one ? Please help to add this condition or step with the above one.

[Agnius · Accepted solution]

I am not sure I understand your question, but here's what I think should do it.

First of all, I've created a list for the values to check for:

Then I've added some additional data extraction from Excel to read column C:

And then another loop to check if the list we created initially contains each value in column C. If it does, then the loop exists. Then, after the loop, we check if the current item from column C (at the end of the loop) is contained in the list. If it isn't, then we close Excel and move on to the next file. Otherwise, the flow continues to write to Excel.

Here's a snippet for the full flow with these actions added:

SET List TO ['D11', 'D08', 'D07', 'ZA1', 'ZB2', 'ZC']
LOOP FOREACH CurrentItem IN Files
    IF Contains(CurrentItem.NameWithoutExtension, 12345, False) THEN
        NEXT LOOP
    END
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''A''' StartRow: 2 EndColumn: $'''H''' EndRow: 2 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
    Excel.GetFirstFreeRowOnColumn Instance: ExcelInstance Column: $'''C''' FirstFreeRowOnColumn=> FirstFreeRowOnColumn
    Excel.ReadFromExcel.ReadCells Instance: ExcelInstance StartColumn: $'''C''' StartRow: 1 EndColumn: $'''C''' EndRow: FirstFreeRowOnColumn - 1 ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelDataColumnC
    LOOP FOREACH CurrentRow IN ExcelData
        LOOP FOREACH CurrentValue IN CurrentRow
            IF IsEmpty(CurrentValue) THEN
                EXIT LOOP
            END
        END
        IF IsEmpty(CurrentValue) THEN
            EXIT LOOP
        END
    END
    IF IsEmpty(CurrentValue) THEN
        Excel.CloseExcel.Close Instance: ExcelInstance
        NEXT LOOP
    END
    LOOP FOREACH CurrentRow IN ExcelDataColumnC
        IF Contains(List, CurrentRow[0], False) THEN
            EXIT LOOP
        END
    END
    IF NotContains(List, CurrentRow[0], False) THEN
        Excel.CloseExcel.Close Instance: ExcelInstance
        NEXT LOOP
    END
    Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: 11 Column: $'''A''' Row: 2
    Excel.SaveExcel.Save Instance: ExcelInstance
    Excel.CloseExcel.Close Instance: ExcelInstance
END

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz]

Hi Agnius,

Thank you so much for the help its working now,, One more last help If you could help me with above code explanation especially on loop how its working n what its do's and what is zero in this does %CurrentRow[0]% ? 

It will help me to apply for other logic as well - A humble request thanks a lot 🙂

[Agnius · Accepted solution]

The second loop will iterate through the values in column C - the values extracted by the second Read from Excel worksheet action. While it is still only one column, PAD will by default return a data table with one column. This is why a %CurrentItem% in this loop will actually be a data row with a single column. And thus using %CurrentItem[0]% indicates that we are getting the actual value from the first column, instead of the entire row.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

[Ahmedmuz]

Thank you so much for help 🙂