[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Deleting Excel worksheets?

Posted on 1999-12-16
3
Medium Priority
?
145 Views
Last Modified: 2010-05-02
From within VB does anyone have any idea how to enumerate through all worksheets in the currently open workbook from the second sheet to the last sheet and delete them all (except the first one)

Either counting them all, selecting them en masse and then deleting them or deleting them individually are both perfectly acceptable...

The worksheets.count method tells you how many sheets are in the current workbook...

Thanks
0
Comment
Question by:derek_noonan
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
ture earned 400 total points
ID: 2286198
derek noonan,

From VB, you must set a reference to the MS Excel Object Library. Then you will be able to use code like this:

Sub DeleteSheets()
  dim xl as new excel.application
  dim wb as excel.workbook
  set wb=xl.workbooks.open("c:\test\test.xls"
  xl.application.displayalerts=false
  do while wb.worksheets.count > 1
    wb.worksheets(2).delete
  loop
  xl.application.displayalerts=true
  wb.save
  wb.close
  xl.quit
  set wb=nothing
  set xl=nothing
End Sub

Ture Magnusson
Karlstad, Sweden
0
 
LVL 22

Expert Comment

by:ture
ID: 2286203
sorry... I missed a closing parenthesis on the line where the workbook is opened.
0
 

Author Comment

by:derek_noonan
ID: 2286268
Thanks for the quick response...

Haven't tried it yet but it looks good and I can tweak it some more anyway

BFN

Derek
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

613 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