Get only Visible Excel Worksheet Names

[MarkWhitford]

I'm familiar with the "Get Worksheet Names" action of the MS Excel VBO in BP 6.5.1.

However this returns ALL Worksheet Names including Hidden.

I need a solution that returns ONLY Visible Worksheet Names in Excel Workbooks that have a number of hidden
Worksheets that contain irrelevant data.

The Workbooks are uploaded by third parties so I have no control over the format & content of them.

I don't have any knowledge of VB coding, so I'm unable to modify a copy of the "Get Worksheet Names" action to achieve what I need.

Any assistance would be appreciated, but a code snippet showing how to do this from the "Get Worksheet Names" action would be great.

Thanks
Mark

[mkumar407 · Accepted solution]

hi Mark,

Modify a copy of "Get Worksheet Names" and add below lines highlighted in bold:
For Each sheet as Object in sheets
If sheet.Visible = True
Worksheet_Names.Rows.Add(New Object() {sheet.Name})
End If
Next

this will help you to get only visible sheet names.

[MarkWhitford]

Thank you Manish, I will try this and post the result.

Thanks
Mark

[MarkWhitford]

Hi Manish,
I tried the suggested code in a copy of the "Get Worksheet Names" action to get only visible worksheets, as follows.

Dim wb As Object = GetWorkbook(Handle, WorkbookName)
Dim sheets as Object = wb.Sheets

Worksheet_Names = New DataTable()
Worksheet_Names.Columns.Add("Name", GetType(String))

If sheets IsNot Nothing Then
    For Each sheet as Object in sheets
        If sheet.Visible = True
            Worksheet_Names.Rows.Add(New Object() {sheet.Name})
        End If
    Next
End If

Unfortunately, this only returns the worksheet name "Sheet1", which is not present in the workbook I tested it on.

The workbook I opened through Process stages of "Create Instance" & "Open Workbook" contains the following sheet names.

• Version Control (Visible)
• TYPE2 <6ELB-02-00-MPS-002> (Visible)
• UFA<SAM> (Hidden)
• MT-LFN 5WES-65-17 (Hidden)
• type 3 MDU (Hidden)
• Table Input (Hidden)

There are no errors when I run the action, it just doesn't get the currently active workbook visible worksheet names, seems like it gets
a default workbook sheet name.

It is close it working if you could help a bit further, or if anyone else can contribute some suggestions ?

Thanks
Mark Whitford

[mkumar407]

Hi Mark,

Seems you have missed steps, as a result default xlsx Book1 is created by BP and output as "Sheet1" is shared.
Try with below steps in sequence to get expected output in collection:
1. MS Excel VBO::Create Instance
2. MS Excel VBO::Open Workbook
3. MS Excel VBO:: Copy of Get Worksheet Names  --> New Action to exclude hidden worksheet. Ensure Handle is same as output of create instance in step1
4. MS Excel VBO::Close Instance

Best!

[MarkWhitford]

Hi Manish,
You were 100% correct, I must have missed something when I first tested the solution.

I tested it again from the process and it worked perfectly !

Thanks for your help.

Best Regards
Mark Whitford

[mkumar407]

Great to know Mark! 
I am happy to help 😎