Unable to find sheetname - VB run-time error '9'

Help
1

Hi All,

I have UI Path opening the first workbook and check for a “Reconciliation template” worksheet in the excel file.
If the worksheet exist then the robot will work on it but if not it will close the excel file.

However, i am receiving a VB run-time error ‘9’ when an excel file does not contain the “Reconciliation template” and stops the robot.

Does anyone know if there is a work around to avoid this error?

image
image589×626 12.6 KB

2 Likes
2

Hi.

Have you tried using a WorkBook application variable in the Output property of the Excel Scope? You can then use that variable to get the sheets, like
wb.GetSheets.Contains("Reconciliation template")

If that doesn’t help then can you upload an example of a file that works and an example of a file that doesn’t work?

thanks.

3

In the IF activity, are the sequences handled properly for true and false conditions.

Because , when the sheet is found, you are printing “No template”.
When the sheet is not found, you are printing “Exists”.

Please check.

4

Hi Clayton,
I have attempted to use CurrentWorkbook.GetSheets.Contains(“Reconciliations template”) condition but if the sheetname does not exists, it provides the same run-time error.

I wanted to test the following method:

  1. Get worksheet names
  2. Set these names in a list
  3. Use the IF activity with the condition “if list contains reconciliation template”

But I am unsure how to convert the GetSheets to a list of strings.

5

Hi KarthikByggari,

The assignment for TRUE and FALSE are correct but I did place the wrong messages in the write line activity.

6

Hi,

Can you point the activity where exactly the error is throwing.
I do not think the error is occurring in the IF condition. Because it is just a List.Contains(“”) which returns True or False.

7

Hi KarthikByggari,

It occurs just after the IF activity for any workbooks that does not contain the “Reconciliation template”.
If the workbook does not contain the sheet, it provides a Visual Basic run-time error as it does not exist.

8

Is it possible to upload the xaml file?
The reason is you are trying to access the sheet which is not available in the workbook somewhere in the workflow.

9

@chend Check attached zip file meets your requirement

DynamicSheetExl.zip (16.8 KB)

1 Like
10

Hi Indra,

I only require the bot to confirm if the sheet exists within the file to move to the next stage.

This is an awesome start as it did not incur the run-time error.
Would it be possible to output the data into a list of string and confirm if the “reconciliation template” exists in the string list?