Using modern excel and getting error "Object reference not set to an instance of an object" after final iteration

[chris.dease]

I’m using Modern excel activities and the automation is working as expected except I’m getting error “Object reference not set to an instance of an object” on the last iteration of the excel file when i would just expect the automation to end. see the below snippet of my automation. as you can tell in the output, it’s iterating correctly (only 2 rows on the spreadsheet), but when it should end, it appears to be trying to iterate over something and getting this error. Do i have any logic misplaced? On properties panel for “for each excel row” i have empty row behavior set to stop so i’m expecting it to stop and not get this error.

[jose.ordonez1 (Jose Ordonez)]

Hi @chris.dease
That means that the str_User2 variable is not initialized. Please check in the Immediate pannel what is the value for CurrentRow(User 2).ToString.Trim or inside the loop use a write line activity like this CurrentRow(User 2).ToString.Trim to check what is the value that you are trying to assing to the str_User2 variable.

Hope it helps you!

[ashokkarale]

@chris.dease,

It might be happening due to additional blank row at the end of the rows.

Solution:

Before assigning str_User2 value, check current row ("column") have any data or not. If no data, use Break activity to break the loop safely without error.

Thanks,
Ashok

[chris.dease]

i used the conditional statement:
(CurrentRow(“USER 2”).ToString.Trim = String.Empty) or (CurrentRow(“USER 2”).ToString.Trim = “”)

and i got the same error, this time at the beginning of this “if” statement, not at the “assign” level. I’m sure i’m doing something incorrect, but can’t determine what it is:

here’s a snippet of what my spreadsheet looks like if that helps as well. i’m getting the info from Column N:

[chris.dease]

also added a write line activity above the conditional and got the error at that level:

[ashokkarale]

@chris.dease,

Check this way.

String.IsNullOrEmpty(CurrentRow.ToString.Trim)

Thanks,
Ashok

[chris.dease]

This appears to somehow resolved itself. I’m not sure if this has anything to do with it, but I’m uploading this excel file from a sharepoint site and saving it to my onedrive. I’m not sure if the automation was running too fast and interfering with the spreadsheet updating on my onedrive. I changed by uploading to local C drive and the issue did not happen again.