Hyperion: Smart View inserting hidden worksheets with long, random string names

[EverClear]

Hi there,
I am creating several Smart View template retrieves in Excel, using some mild VBA to retrieve data and metadata, and to spit out copies of certain tabs to new worksheets in the same workbook. The VBA includes a loop to go through each spreadsheet to retrieve the data.

Over time, the workbook accumulates some random worksheets sheets that are hidden. I was right-clicking to perform another action, and happened to notice the 'unhide' menu option was activated. I knew that I didn't create/store any hidden worksheets, so I selected unhide and found these random, junk sheets in my file. These sheets have really long, 31-character names (yup 31, not 32) that look like random alphanumeric characters. The spreadsheets are completely empty.

Has anyone else seen this? If so, does anyone have any ideas on how to keep this from happening? These hidden worksheets screw up the loop in my Smart View retrieve macro.

Thank you!!

 

[RickXL]

Hi,

I can't help directly. I don't even know if Hyperion will object if you remove them. However, macros like these could be useful. Please test before use on productive data!

Sub ListVisible()
    Dim ws As Worksheet
    
    For Each ws In Worksheets
        If ws.Visible Then
            Debug.Print ws.Name
        End If
    Next
    
End Sub
Sub DeleteHidden()
    Dim ws As Worksheet
    
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If Not ws.Visible Then ws.Delete
    Next
    Application.DisplayAlerts = True
    
End Sub

The first one can be adapted to process visible sheets only and skip the hidden ones.
The second macro will delete, without a prompt, all the hidden sheets.

 

[EverClear]

Thank you so much! I will try these.