?
Solved

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

Posted on 2011-04-28
7
Medium Priority
?
581 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?
0
Comment
Question by:jmcclosk
  • 4
  • 3
7 Comments
 
LVL 14

Accepted Solution

by:
sungenwang earned 500 total points
ID: 35484397
DO this:

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

Author Comment

by:jmcclosk
ID: 35485229
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
 
LVL 14

Expert Comment

by:sungenwang
ID: 35485308
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:jmcclosk
ID: 35491504
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
 
LVL 14

Expert Comment

by:sungenwang
ID: 35492332
Sorry... I thought you're in Excel VBA. For Access, you should use:

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

sew
0
 

Assisted Solution

by:jmcclosk
jmcclosk earned 0 total points
ID: 35492375
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
 

Author Closing Comment

by:jmcclosk
ID: 35687915
I could not have figured it out with sungenwang putting me on the right track. Thank you for the assistance!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question