Using Access 2003 VBA Scripting to delete a worksheet tab in Excel workbook

This is a follow up question to one I asked earlier this morning about creating multi-tabbed Excel spreadsheets from an Access VBA Script.  I now have a need to systematically delete tabs in a workbook.  I was able to determine from the help I got earlier that I can use the command:

.Worksheets("TabName").Delete

to delete a specific tab.  However, if there is data in the worksheet, I receive the following message from Excel that has to be manually responded to:

"Data may exist in the sheet(s) selected for deletion.  To permanently delete the data, press Delete."

Is there any way to override this message automatically and force the deletion of the worksheet even if there is data on it so the end user will not see this message and have to respond?
jmccloskAsked:
Who is Participating?
 
sungenwangCommented:
DO this:

Application.DisplayAlerts = False
.Worksheets("TabName").Delete
Application.DisplayAlerts = True
0
 
jmccloskAuthor Commented:
I receive an error when trying this stating: Compile error:  Method or data member not found.  On this error, it highlights this part of the statement:

.DisplayAlerts =
0
 
sungenwangCommented:
Just tested it and the only thing different was removing the period (.) before Worksheet("TabName").delete. It deleted the tab without any pop ups.

Application.DisplayAlerts = False
Worksheets("TabName").delete
Application.DisplayAlerts = True
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jmccloskAuthor Commented:
It still does not work.  It is not the "Worksheets" line it is having trouble with.  It is the "DisplayAlerts" line it does not like.  Is there maybe some kind of reference I am missing?
0
 
sungenwangCommented:
Sorry... I thought you're in Excel VBA. For Access, you should use:

.DisplayAlerts = False
.Worksheets("TabName").Delete
.DisplayAlerts = True

sew
0
 
jmccloskAuthor Commented:
Actually, I figured it out.  It is this:

Excel.Application.DisplayAlerts = False
.Worksheets("TabName").Select
.Worksheets("TabName").delete
Excel.Application.DisplayAlerts = True

It works like a charm.  Thanks.
0
 
jmccloskAuthor Commented:
I could not have figured it out with sungenwang putting me on the right track. Thank you for the assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.