Delete sheet Misery (1 Viewer)

Wapug · May 24, 2018

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 · May 24, 2018

Should it be

Code:

wb.ActiveSheet.Delete

Wapug · May 24, 2018

ridders said:
Should it be

Code:

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 · May 24, 2018

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

Code:

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 · May 24, 2018

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

Code:

    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 · May 24, 2018

Beetle said:

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

Code:

    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 · May 24, 2018

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 · May 24, 2018

Gasman said:
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!!

Delete sheet Misery (1 Viewer)

Wapug

Registered User.

isladogs

MVP / VIP

Wapug

Registered User.

Gasman

Enthusiastic Amateur

Beetle

Duly Registered Boozer

Wapug

Registered User.

Gasman

Enthusiastic Amateur

Wapug

Registered User.

Similar threads

Users who are viewing this thread