none
Delete method fails RRS feed

  • Question

  • I have the code below which runs in a Word-based VBA project, meaning an instance of Excel is created in Word and the procedure below is called within that context. The code is supposed to delete a worksheet if its only table is no longer required. The Worksheet.Delete method doesn't work. When the execution reaches that line VBA goes into an endless loop. The sheet isn't deleted. The expected alert isn't displayed. When Excel is abandoned via the Task Manager an "Automation error - Runtime error '-2147023170 (800706be)' message is displayed. When I click the Debug button .Delete is highlighted.

    Sub DeleteXlTable(Wb As Workbook, _
                      Frm As fTextLib)
        ' SSY 047 ++ 30 Dec 2018
        
        Dim LibWs As Worksheet
        Dim Rng As Excel.Range
    
    '    Application.DisplayAlerts = False
        Set LibWs = SetLibWs(Wb, Frm)
        With LibWs
            If .ListObjects.Count = 1 Then
                If Wb.Worksheets.Count = 1 Then
                    With .UsedRange
                        .Columns.Delete
                        .Rows.RowHeight = 12.75
                    End With
                    .Name = "Sheet1"
                Else
                    .Delete
                End If
            Else
                Set Rng = .ListObjects(Frm.CbxTbl.Text).Range
                Do While Rng.Row > NwsFirstLibRow
                    If Not .Cells(Rng.Row - 1, NwsKey).ListObject Is Nothing Then Exit Do
                    Set Rng = Rng.Offset(-1).Resize(Rng.Rows.Count + 1)
                Loop
                Rng.Rows.EntireRow.Delete
            End If
        End With
        Application.DisplayAlerts = True
    

    I tried specifying the deletion as Wb.Worksheets(LibWs.Name).Delete with the same result. I inserted code to delete the ListObject first, to no avail (there is more sheet content). I tried similar code in Excel. It works without a problem even if the deleted sheet is the active one. I didn't try saving the workbook before deletion because the list of what I might try seems endless. Any ideas?

    Sunday, December 30, 2018 11:34 AM

Answers

  • I found the answer:-

    Application.DisplayAlerts refers to the Word application. Therefore it can't have an effect on Excel. However, it also isn't just an extra of convenience in this case. Therefore removing it doesn't solve the problem. Replacing the line with XL.DisplayAlerts = False cured the error. XL is the Excel application object in which Wb is open. I suppose it might also have been addressed as Wb.Parent.DisplayAlerts = False.

    • Marked as answer by SSY Sisyphus Monday, December 31, 2018 12:52 AM
    Monday, December 31, 2018 12:52 AM