How to tell my bot to STOP when it finishes the last tab in a workbook?

Help
1

I currently have a bot that is moving through excel tabs for formatting purposes and I’m having trouble of how to tell the bot that there are no more tabs in the workbook that need to be formatted and for the bot to end.

I don’t know how I would implement this but thought is there a way for the user to say how many tabs are in the workbook and then for the bot to repeat a sequence that many times? Or somehow tell the bot once it gets to a tab titled “END” to end the bot?

Any help is appreciated.

2

@Kylealden15,
You will get the number of sheets in the workbook using Get excel sheets activity and they are stored in a list type variable.

Then looping through all of them using for each activity, you can do all your tasks inside that. After all the sheets are done, then it will automatically stop the flow

Hope this helps :slight_smile:

2 Likes
3

This makes perfect sense! I’ll give this a try and hopefully it works!

Appreciate the help!

4

@HareeshMR
I’m having a little trouble in the “For Each” Activity. The error I am getting is "Source: For Each. Message: Object reference not set to an instance of an object. and Exception Type : System.NullReferenceException. "

in the “For Each” properties i have my “workbooksheets” variable (set as IEnumerable) but I’m not sure what the Type Argument I should be using. I have it set to IEnumerable right now when I got that error.

Any additional advice? Thanks!

Kyle

5

Can you post the screenshot of the workflow @Kylealden15, both for each and get workbook sheets activity also

6

@Kylealden15 If you are using the Get Workbook Sheets activity to get your “workbooksheets” variable, then you should change the TypeArgument to string in the For Each activity

7

@HareeshMR, see below for screen shots (had to do it in seperate posts)
image

@Dave I changed the TypeArgument to string and still got the same error

Thanks for the help!!!

8

image
image1500×382 21 KB

9

Can you show screenshots of the get workbook sheets activity properties, the variable pane, and the for each activity properties?

Or if possible, it’d be even easier/better to just upload the .xaml

10

Fine
hope these steps would help you resolve this
–use a excel application scope and pass the file path as input
–while from that excel application scope get the output from property WORKBOOK with a variable named wrk_output by pressing ctrl+k
–now inside the scope use a assign activity like this
arr_sheetname = wrk_output.GetSheets

where arr_sheetname is a array string variable that will store the sheetnames in the workbook as a array

–now while still being inside the excel application scope use a for each activity and change the type argument as string in the property panel of for each activity
–inside the loop we can use read range activity or any activity we want
if we use READ RANGE ACTIVITY mention the sheet name as item as that is the variable that has the sheetname from the for each loop

hope this would helpyou
simple isnt it
Cheers @Kylealden15

11

Palaniyappan,

I am trying this now. This bot would be used by multiple users for different excel files so I’m currently using the “Select Folder” activity and saving the output to a variable. For some reason it’s not letting me have that folder path variable as the excel sheet to run the bot on.

Any thoughts?

1 Like
12

we can try with this expression
arr_folderpath = Directory.GetDirectory(“path as string”)
this gives us the sub folder name of a folder where arr_folderpath is a string array variable

arr_filepath = Directory.GetFiles(“yourfolderpath”,“*.xlsx”)
this expression in a assign activity gives us a array of file path of excel file alone in the specified folder path which can be passed to a for each loop and use item variable from for each activity as a input to EXCEL APPLICATION SCOPE activity as it holds the file path of the excel from the array

Cheers @Kylealden15

13

@Palaniyappan

So I think I’m getting there. Currently my bot is here… Main.xaml (12.1 KB)

When it runs now it does the actions 3 times (there are 3 tabs in the workbook) but it doesn’t move to each tab to perform the actions. Any thoughts / help here?