The aim is to copy and paste a phone number into an Output sheet. I need a switch to check between mobile, home and work number to see which one has been supplied, prioritizing the mobile number, and then the home number, with work last.
Within my for each row loop, I currently am using the expression (isEmpty=") with Type argument Boolean, then using True and False for the different paths. Issue is the cells aren’t being checked to see if they are empty, as the work numbers are the only numbers being posted, even with the empty cells.
I am new to using switch, so anything would be helpful.
So it means work numbers will have both work and blank right???
Yes, however before it even gets to work numbers it should be prioritizing mobile number column. For some reason it isn’t checking if the mobile number column (K) is empty or not and just copy and pasting the entire work number column.
Here is how the spreadsheet looks:
The order should look like this:
But comes out like this:
Just tried changing the expression to check to isEmpty.ToString.Length = 0 . Still got the same incorrect result.
Can you try to set breakpoint at the switch activity and run debug mode?
Then can you check content of IsEmpty at locals panel when it stops there?
I ran like you said, what exactly am I looking for? Haven’t used debug mode before.
We can check content of each variable at Locals panel as the following if it stops at breakpoint.
Thanks, it says isEmpty has a value of “”. Is there a better way to check if a cell is empty? or is there another issue.
Thank you for checking. There might be other cause.
if possible, can you share your workflow and data? It’s no problem if dummy data.
Here you go, thank you for the help. If you need me to explain why I did anything, let me know.
New Copy of Text spreadsheet example.xls (28 KB)
Bulk Text main.xaml (13.0 KB)
We need to use ReadCell activity instead of ReadCellFormula activity because ReadCellFormula returns empty if there is no formula (even there is data).
Then you need to change type of
isEmpty from String to GenericValue, (and might be modift from
In addition initial value of counter should be 1.
The Issue I was having with Read Cell activity was that I only have access to the read cell activity that includes the workbook path, so I couldn’t have it inside the excel application scope as the file would be already open. I have seen people use one that doesn’t have the workbook path included but I have updated everything and made sure I have the correct packages installed but seem to still not have that version. Should I just use the read cells outside of the excel application scope then?
We can use ReadCell activity under App Integration - Excel as the following. Can you check this?
So I followed everything you said, I checked the debugger, isEmpty now has the value of Null. But when it gets to the switch, I get an error about object reference not set to an instance of an object. When it ran it worked perfectly for 3 rows of the For Each loop, then I got the error.
Can you try the following condition?
isEmpty is Nothing OrElse isEmpty.ToString=“”
Thank you so much, it worked perfectly!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.