We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

jmcclosk
jmcclosk asked
on
Medium Priority
593 Views
Last Modified: 2012-06-27
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?
Comment
Watch Question

Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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 =
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

Author

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?
Sorry... I thought you're in Excel VBA. For Access, you should use:

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

sew
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
I could not have figured it out with sungenwang putting me on the right track. Thank you for the assistance!
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.