CODEWITHSUNDEEP
excelms-accessvbams-access-2010
Ryan_W4588
Ryan_W4588

Reputation: 668

Delete Worksheet from Access 2010 VBA

I have a form that exports and edits excel files for users. I have an issue when trying to have my code delete an existing worksheet from my Access 2010 VBA code.

My Code:

Private Sub Command0_Click()

Dim xl As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

Set xl = CreateObject("Excel.Application")
Set wb = xl.Workbooks.Open("C:\Users\Me\Desktop\Document.xlsx")

For Each sht In wb.Worksheets
  If sht.Name = "DeleteSheet" Then
    wb.Worksheets("DeleteSheet").Delete
  End If
Next sht

wb.Save
wb.Close
xl.Quit

End Sub

When I run the code, there is no error. However, the sheet does not get deleted. I know that sht.Name does read the sheet name "DeleteSheet", allowing the if statement to run. So, I believe it comes down to either the saving method or this line: wb.Worksheets("DeleteSheet").Delete. TIA!

Upvotes: 1

Views: 6610

Answers (3)

Monica Jones
Monica Jones

Reputation: 1

If this is the only worksheet in the workbook then Excel won't allow you to delete it. Try adding a blank tab at the end as a buffer in your file, then hide the blank tab afterwards.

Upvotes: 0

DanielG
DanielG

Reputation: 1675

Can you try something like this, Ryan?

Dim xl As Object
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet

Set xl = CreateObject("Excel.Application")
xl.Application.DisplayAlerts=False
Set wb = xl.Workbooks.Open("C:\Users\Me\Desktop\Document.xlsx")

For Each sht In wb.Worksheets
  If sht.Name = "DeleteSheet" Then
    wb.Worksheets("DeleteSheet").Select
    xl.ActiveSheet.Delete
  End If
Next sht

wb.Save
wb.Close
xl.Quit

Upvotes: 0

DanielG
DanielG

Reputation: 1675

Can you delete the worksheet directly, i.e.

Instead of

wb.Worksheets("DeleteSheet").Delete

Use

sht.Delete

Upvotes: 0

Related Questions