Delete sheet Misery

[Wapug]

I am trying to perform a variety of things from a VBA module in Access to open an excel workbook and delete a sheet and it doesn't seem to be working. I am using Access and Excel 2013, and am trying to delete a sheet in a workbook. The module runs and gives me the process complete but when I open the spreadsheet the worksheet labeled INVENTORY, is still there. What am I doing wrong here?

Private Sub Command0_Click()
Dim xl As Object
Dim wb As Excel.Workbook
Dim Sht As Excel.Worksheet
Set xl = CreateObject("excel.Application")
Set wb = xl.Workbooks.Open("C:\Users\bacon\Desktop\test1.xlsx")
For Each Sht In wb.Worksheets
If Sht.Name = "INVENTORY" Then
wb.Worksheets("INVENTORY").Select
xl.ActiveSheet.Delete
End If
Next Sht
wb.Save
wb.Close
xl.Quit
MsgBox "Process complete!"
End Sub
:banghead:

 

[isladogs]

Should it be

wb.ActiveSheet.Delete

 

[Wapug]

Should it be

wb.ActiveSheet.Delete

I appreciate your quick response and I applied your suggested fix to no avail. With the fix you provided I still get the Successful msgbox bu the sheet is not deleted.

 

[Gasman]

Walk through the code with F8
I've just run the code below on a New workbook and it works fine.?

Sub DeleteSheet()
Dim xl As Object
Dim wb As Excel.Workbook
Dim Sht As Excel.Worksheet
Set xl = CreateObject("excel.Application")
Set wb = xl.Workbooks.Open("C:\Temp\deletesheet.xlsx")
For Each Sht In wb.Worksheets
    If Sht.Name = "Sheet2" Then
        wb.Worksheets(Sht.Name).Select
        xl.ActiveSheet.Delete
    End If
Next Sht
wb.Save
wb.Close
xl.Quit
MsgBox "Process complete!"
End Sub

 

[Beetle]

In addition to the other suggestions, you may need to temporarily turn off alerts in the workbook. Example;

    Dim XL As Object
 
    Set XL = CreateObject("Excel.Application")
 
    With XL
        .Workbooks.Open ("C:\Users\sbailey\Documents\Book1.xlsx")
        [COLOR="Red"].DisplayAlerts = False[/COLOR]
        .ActiveWorkbook.Worksheets("Test2").Delete
        [COLOR="red"].DisplayAlerts = True[/COLOR]
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    End With

    Set XL = Nothing

 

[Wapug]

In addition to the other suggestions, you may need to temporarily turn off alerts in the workbook. Example;

    Dim XL As Object
 
    Set XL = CreateObject("Excel.Application")
 
    With XL
        .Workbooks.Open ("C:\Users\sbailey\Documents\Book1.xlsx")
        [COLOR=red].DisplayAlerts = False[/COLOR]
        .ActiveWorkbook.Worksheets("Test2").Delete
        [COLOR=red].DisplayAlerts = True[/COLOR]
        .ActiveWorkbook.Save
        .ActiveWorkbook.Close
        .Quit
    End With

    Set XL = Nothing

I applied your suggestions to my code and it seems to e working but I am trying to now export my access query to the same workbook, and its giving me an error.

Private Sub Command0_Click()
Dim xl As Object
Set xl = CreateObject("excel.Application")
With xl
.Workbooks.Open ("C:\Users\bacon\Desktop\test1.xlsx")
.DisplayAlerts = False
.ActiveWorkbook.Worksheets("INVENTORY").Delete
.ActiveWorkbook.Worksheets("STUFF").Delete
.ActiveWorkbook.Save
.DisplayAlerts = True
.ActiveWorkbook.Close
.Quit
End With
Set xl = Nothing
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "INVENTORY", ActiveWorkbook
End Sub

 

[Gasman]

Would really help to state what the error is?

Is it on the last line?
Is the excel file really called Activeworkbook?, shouldn't that be the name of the excel file?

 

[Wapug]

Would really help to state what the error is?

Is it on the last line?
Is the excel file really called Activeworkbook?, shouldn't that be the name of the excel file?

I figured out the problem. slight bit of stupidity on my part. Thanks for your help!!