Can't delete sheet

Question

I'm trying to open a workbook and delete a sheet from it, but it runs the code without errors, and the sheet is still there... I'm able to modify it, as I changed formulas to values on another sheet. First of all - Yes, I know the "i" variable is set to do 1 iteration. Somehow, now when I open the workbook it says it's locked by me - which I don't even know how to do.

So...how can I unlock it? When I go to File-->Info-->Permissions it says 'Anyone can copy, change and modify any part of this workbook.... I can delete the sheet manually as well...

Here's the code:

Sub Change()

Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Sheets("FileSearch Results")

Dim rng As Range
Set rng = ws.UsedRange

Dim cPaths As Integer
cPaths = rng.Column

Dim i As Integer
i = rng.Row

Dim oExcel As Excel.Application
Set oExcel = New Excel.Application

Dim oWB As Workbook

Dim komm As Excel.Worksheet
Dim sh1 As Excel.Worksheet

Do While i < 2
    Dim pth As String
    pth = ws.Cells(i, cPaths)
    Set oWB = oExcel.Workbooks.Open(pth)

    Set sh1 = oWB.Worksheets("Sheet1")
    With sh1.UsedRange
        .Value = .Value
    End With

    Set komm = oWB.Worksheets("Kommentar")
    Application.DisplayAlerts = False
    komm.Delete
    Application.DisplayAlerts = True



    oWB.Close savechanges:=True
    i = i + 1
Loop
End Sub

Any ideas?

Answer 1

For anyone running into this in the future (like myself), the actual problem is with the mix up in scope when calling Application.DisplayAlerts.

komm is a sheet in oWB, which exists in the new instance of excel oExcel.

Application is a completely different instance and therefor has no effect.

Since the code isn't actually disabling the prompt in the correct instance of excel (oExcel) and it is presumably not visible, the code will just ignore the command and move on.

The simple fix is to use oExcel instead of Application:

Set komm = oWB.Worksheets("Kommentar")
oExcel.DisplayAlerts = False
komm.Delete
oExcel.DisplayAlerts = True

Answer 2

Sub Change()

Dim wb As Excel.Workbook
Set wb = ActiveWorkbook 'ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Sheets("FileSearch Results")

Dim rng As Range
Set rng = ws.UsedRange

Dim cPaths As Integer
cPaths = rng.Column

Dim i As Integer
i = rng.row

'Dim oExcel As Excel.Application ***CHANGED***
'Set oExcel = New Excel.Application ***CHANGED***

'Dim oWB As Workbook ***CHANGED***

Dim komm As Excel.Worksheet
Dim sh1 As Excel.Worksheet

Do While i < 2
    Dim pth As String
    pth = ws.Cells(i, cPaths)
    'Set oWB = oExcel.Workbooks.Open(pth) ***CHANGED***

    Workbooks.Open (pth) '***ADDED***

    Set sh1 = ActiveWorkbook.Worksheets("Sheet1") 'oWB.Worksheets("Sheet1") ***CHANGED***
    With sh1.UsedRange
        .Value = .Value
    End With

    Set komm = ActiveWorkbook.Worksheets("Kommentar") 'oWB.Worksheets("Kommentar") ***CHANGED***
    Application.DisplayAlerts = False
    komm.Delete
    Application.DisplayAlerts = True

    ActiveWorkbook.Close savechanges:=True 'oWB.Close savechanges:=True ***CHANGED***
    i = i + 1

Loop

End Sub

This now opens the workbook and deletes the sheet in the foreground rather than invoking a new instance of Excel and deleting the sheet in the background. This is why the file stays locked, as the new instance which isn't closed by the code, still holds it.