Invoke code to get hidden sheets in an excel file

Help Activities
, ,
1

Hello everyone,

I am trying to automate the process of detecting hidden tabs in all excel files in a folder and populate the hidden sheet names in an excel table.

However, I am not familiar on how to write an invoke code. Would anyone mind guiding me on the codes that I can key in to run the intended outcome?

Thank you in advance!

2

Hi,

You can use this to identify the sheet names (hidden sheets as well).
Instead of logging the sheet names, you can pass an out argument of sheet names and store it in excel table.

ExcelSheetNames.zip (8.2 KB)

Thanks

3

Thanks Manzar for your kind guidance.

I just tried to apply the codes which you shared to me, but i got the below error:

Private: Invoke code: Exception has been thrown by the target of an invocation.

Also, can you advice which part of the codes should I change if I wish to get the names for only Hidden Sheets?

Thank you.

4

Hi @WS_Chai

There is a working solution on my earlier post.

Hope this will be helpful. Thank you.

5

Here is the sample for getting sheet names from an excel file.

Code:
hide sheet.xaml (6.9 KB)
Test.xlsx (10.9 KB)

Thank you.

6

Please check you have changed the value in arguments.

image
image877×142 7.34 KB

I have changed the code a bit to get only the hidden sheets.

image
image751×502 20.3 KB

Here is the code:
Test.xlsx (8.4 KB)
Main.xaml (5.1 KB)

Hope this will be helpful. Thank you.

7

Thanks Robin for the clear replies along with the samples.

When I apply the the codes that you amended slightly, there is still an error which I am unsure of how to resolve it:

Error ERROR Validation Error No compiled code to run
error BC30456: ‘XISheetVisibility’ is not a member of ‘Microsoft.Office.Interop.Excel’. At line 8

Snipaste_2021-12-22_10-06-56
Snipaste_2021-12-22_10-06-56750×500 20.2 KB

Snipaste_2021-12-22_10-06-25
Snipaste_2021-12-22_10-06-251451×173 11.3 KB

I have also inputted a value for the argument, which is linking to a variable that I have created with the file path details.
Would you mind advising what went wrong or what I have to correct from the above?

Snipaste_2021-12-22_10-08-09
Snipaste_2021-12-22_10-08-091827×153 14.9 KB

Snipaste_2021-12-22_10-07-24
Snipaste_2021-12-22_10-07-24870×248 8.14 KB

8

Hi,

If(sheet.visible = False) means that it is hidden use like this and see if you are getting corre t result. Thanks.

9

Hi Kirankumar,

I changed and this error appeared:

Validation Error No compiled code to run
error BC30512: Option Strict On disallows implicit conversions from ‘Boolean’ to ‘Integer’. At line 8

10

ok below code is working fine for me.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(“Your excel path”)
For Each sheet As Microsoft.Office.Interop.Excel.Worksheet In wb1.Sheets
If(sheet.Visible=Microsoft.Office.Interop.Excel.XlSheetVisibility.xlSheetHidden)
Console.WriteLine(sheet.Name)
End If
Next
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect

i think you have to import Microsoft.office.interop.excel package in your manage packages section. please try and let us know. thanks

or type cast like below.

Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb1 As Microsoft.Office.Interop.Excel.Workbook
Dim ws1 As Microsoft.Office.Interop.Excel.Worksheet
excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb1 = excel.Workbooks.Open(“C://kiran”)
For Each sheet As Microsoft.Office.Interop.Excel.Worksheet In wb1.Sheets
If(CType((sheet.Visible),Boolean) =False)
Console.WriteLine(sheet.Name)
End If
Next
wb1.Save
wb1.Close
excel.Quit
ws1 = Nothing
wb1 = Nothing
excel = Nothing
GC.Collect

11

Hi @WS_Chai

The code is working fine.

image
image1175×631 23 KB

Kindly ensure the below namespace is imported to the projects.

image

Code:
Test.xlsx (8.4 KB)
Main.xaml (5.2 KB)

Please let me know if you have any other concerns. Thank you.

1 Like