Delete method of Worksheet class failed

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Delete method of Worksheet class failed

Post by gtonkin »

Had a template that I needed to run TM1 Print Report on, but kept failing with "Print Report-Delete method of Worksheet class failed" - turned out to be a sheet that was flagged as Visible:=2-xlSheetVeryHidden.

Changed to visible and deleted in my case-all fine now.
Seemed fine if set to 0-xlSheetHidden but Print Report does not like xlSheetVeryHidden.
ngphong
Posts: 7
Joined: Thu Jun 22, 2017 5:17 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 365

Re: Delete method of Worksheet class failed

Post by ngphong »

Thank you so much for this postings. It's very helpful. It worked for me when I changed the Visible to "0-xlSheetHidden" on the "Cognos_Office_Connection_Cache" which was not visible. It only shows on the VBA window of Developer menu.
Wim Gielis
MVP
Posts: 3099
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Delete method of Worksheet class failed

Post by Wim Gielis »

Here is a VBA solution. I give it to my customers from a button in the Excel QAT (Quick Access Toolbar). Then it's right at their fingertips.

Code: Select all

Sub Delete_hidden_sheet()

	'Wim Gielis
	'October 2022

	'delete a specific worksheet that would otherwise make the TM1 Report Manager (TM1 Perspectives) choke and raise an error

    Dim ws As Worksheet

    'continue without stopping when errors are encountered
    On Error Resume Next
    
    Set ws = ActiveWorkbook.Worksheets("Cognos_Office_Connection_Cache")
    
    If Not ws Is Nothing Then
    
        'make the sheet visible
        ws.Visible = xlSheetVisible

        'delete the sheet without alerts
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    
    End If

    On Error GoTo 0

End Sub
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
MVP
Posts: 3099
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Delete method of Worksheet class failed

Post by Wim Gielis »

Or, if you prefer a more condensed notation:

Code: Select all

Sub Delete_hidden_sheet()

    On Error Resume Next
    With ActiveWorkbook.Worksheets("Cognos_Office_Connection_Cache")
        .Visible = -1
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    On Error GoTo 0

End Sub
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply