If Hidden Sheet is Not Detected,

[WS_Chai]

Hello there,

I have added an invoke VBA code below to detect hidden sheets from a list of excel files and subsequently write into a data table using uipath activities.

However, by applying the current code, it will throw an error once it meets an excel file with no hidden sheets and the workflow stops. The error “Assign: Object reference not set to an instance of an object” appears.

May I ask how should i amend the invoke code or should I add any activity to resolve the error so that those excel files can be processed and indicate in writing (“No hidden sheets detected”) in the data table?

[jack.chan]

how are you calling this vba code? can you post screenshot of your xaml? or upload the xaml?

[Nithinkrishna]

Hey @WS_Chai

May I know the place of error ?

Invoke VBA ?

Thanks
#nK

[jack.chan]

hey @WS_Chai

its because returnText is NULL if no hidden sheets
you need to initialize returnText to “” first by adding returnText = “”

Dim returnText as string
returnText = ""

so now if theres no hidden sheets returnText will =“” instead of a NULL

[111617 (hwan)]

if ws.Visible = 0 → if ws.Visible is Nothing
change it to this

[WS_Chai]

Hey Jack,

Thanks for your rely.
Attached is the screenshot of my workflow.

Workflow.pdf (90.9 KB)

[jack.chan]

Hi @WS_Chai

please try my answer above

If Hidden Sheet is Not Detected,

hey @WS_Chai its because returnText is NULL if no hidden sheets you need to initialize returnText to “” first by adding returnText = “” Dim returnText as string returnText = "" so now if theres no hidden sheets returnText will =“” instead of a NULL

[WS_Chai]

Hi @jack.chan,

Can I confirm if you mean this?
Did I amend correctly?

[jack.chan]

yes please try that

[WS_Chai]

Hi Jack,

I have tried it, it works well for excel files with no hidden sheet now.

However, for those files with hidden sheets, the wordings of “no hidden sheet detected” appeared before the names of hidden tabs’.

Is there anything that I have done wrongly?

[jack.chan]

@WS_Chai sorry ignore my previous reply

change to this

Public Function GetHiddenSheet() As String
    Dim returnText as string
    returnText = ""
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Visible = 0 Then
            returnText = returnText + ws.Name + "|"
        Else
            'returnText = ""
        End If
    Next ws

     If returnText = "" Then
        returnText = "This EGA does not contain any hidden sheet"
     End If
    GetHiddenSheet = returnText
End Function

[WS_Chai]

@jack.chan ,

There is an error, do you know why?

[jack.chan]

@WS_Chai
can you go to task manager and kill all excel processes and then try again?

[WS_Chai]

Hey Jack,

It’s working fine now. Many thanks to your help