topic Re: Get only Visible Excel Worksheet Names in Product Forum

Get only Visible Excel Worksheet Names

MarkWhitford — Thu, 24 Jun 2021 03:41:34 GMT

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

Re: Get only Visible Excel Worksheet Names

mkumar407 — Thu, 24 Jun 2021 07:08:51 GMT

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.

Re: Get only Visible Excel Worksheet Names

MarkWhitford — Thu, 24 Jun 2021 07:36:15 GMT

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

Thanks
Mark

Re: Get only Visible Excel Worksheet Names

MarkWhitford — Mon, 28 Jun 2021 03:33:05 GMT

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

Re: Get only Visible Excel Worksheet Names

mkumar407 — Mon, 28 Jun 2021 04:07:02 GMT

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!

Re: Get only Visible Excel Worksheet Names

MarkWhitford — Mon, 28 Jun 2021 23:35:46 GMT

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

Re: Get only Visible Excel Worksheet Names

mkumar407 — Tue, 29 Jun 2021 02:57:30 GMT

Great to know Mark!
I am happy to help 😎