Power Platform Community Forum Thread Details

In Excel there are many empty cells in a Column, PAD should identify the empty cells in that column and fill the cellls with a particular text HELLO

Initially,

Output should be,

I have implemented in this way, but the text is not written in the empty cells.

Any help regarding this, would be helpful

Categories:

Power Automate Desktop

In your write to excel action you need to provide cell coordinates (row and column) but you are providing value that you read from excel.

So in other words you tell PAD to write "Hello" to cell in row "null" and column "null".

So you need to track the index of the value. You can do that using Loop instead of For each.

Copy below and paste to your flow.

Note how LoopIndex and ExcelData.RowsCount and try to understand why 😉

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\XXXXX\\Desktop\\test.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> ExcelData
LOOP LoopIndex FROM 0 TO ExcelData.RowsCount - 1 STEP 1
 IF IsEmpty(ExcelData[LoopIndex][0]) THEN
 Excel.WriteToExcel.WriteCell Instance: ExcelInstance Value: $'''Hello''' Column: $'''A''' Row: LoopIndex + 1
 END
END
Excel.CloseExcel.Close Instance: ExcelInstance

Then, once you learn how to automate this with loops, do it with way more efficient way 😉

Add Column1 and Column2 headers to your flow, change the Sheet name to "Sheet1" and run this:

SET Excel_File_Path TO $'''C:\\Users\\XXXX\\Desktop\\test.xlsx'''
Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Connection=> SQLConnection
Database.ExecuteSqlStatement.Execute Connection: SQLConnection Statement: $'''UPDATE [Sheet1$]
SET [Column1] = \"Hello\"
WHERE [COLUMN1] IS NULL''' Timeout: 30 Result=> QueryResult
Database.Close Connection: SQLConnection

And you might want to check this this:

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel