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?
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 + "|"
'returnText = ""
If returnText = "" Then
returnText = "This EGA does not contain any hidden sheet"
GetHiddenSheet = returnText