selectively delete sheets Access VBA

[Pat]

I need to delete certain sheets before adding sheets to populate them.
Has anyone got some code to delete certain sheets in a Excel sheet file.
Do I need to step thru the sheets in reverse order?
Any help will be much appreciated.

[HansV]

What are the criteria for deleting sheets? Do you have a list of names, or ...?

[Pat]

Sorry i was not specific enough.
I have a list of names of sheets i do NOT want to delete.

[HansV]

Let's say you have a variable objXL that is the Excel application and a variable objWbk that is the Excel workbook. I'll assume that these have already been set.
We'll specify the list of sheets not to be deleted as a string delimited by slashes because / is not allowed in an Excel worksheet name.
Then loop through the sheets (indeed backwards) and check if the sheet name enclosed in slashes occurs in the string. If not, delete the sheet.

Code: Select all

  Dim i As Long
  Const strSheets = "/ThisSheet/ThatSheet/OtherSheet/"
  objXL.DisplayAlerts = False
  For i = objWbk.Worksheets.Count To 1 Step -1
    If InStr(strSheets, "/" & objWbk.Worksheets(i).Name & "/") = 0 Then
      objWbk.Worksheets(i).Delete
    End If
  Next i
  objXL.DisplayAlerts = True

[Pat]

Thank you hans i will try that at work inabout an hour.

[Pat]

That worked well, i had coe there already but i had made a mistake using & and not "and" in the code.
Another question though, How can i reference another sheetname (tab at the bottom) having already opened a specific.
eg. I have already opened sheet named "aaa" and populated it, i now want to reference a sheet named "bbb", how do i do this without it asking me to lose all my changes first?

[HansV]

Let's get our terminology straight. In Excel, "sheet" refers to a worksheet or to a chart sheet in a workbook (=file). One workbook can contain many sheets.

If aaa is the active worksheet within the active workbook, you can refer to cell C5 on sheet bbb within the same workbook as

objXL.ActiveWorkbook.Worksheets("bbb").Range("C5")

or even

objXL.Worksheets("bbb").Range("C5")

since the active workbook is implicit if you don't specify a workbook.

You can refer to cell C5 on sheet bbb in another open workbook MyWorkbook.xls as follows:

Workbooks("MyWorkbook.xls").Worksheets("bbb").Range("C5")

There is no need to activate the other workbook to refer to it, but if you wish you can do so. Excel can have many workbooks open at the same time and you don't have to save the active workbook before activating another one.

You can't refer to cells in a workbook that is currently closed. You have to open the workbook first.

Instead of repeatedly referring to a workbook or worksheet by name, it is often convenient to define variables and to work with those:

Dim objWbk As Excel.Workbook ' or Object if you use late binding
Dim objWsh As Excel.Worksheet ' or Object if you use late binding
Set objWbk = objXL.Workbooks.Open("C:\Excel\MyWorkbook.xls")
Set objWsh = objWbk.Worksheets("bbb")
MsgBox objWsh.Range("C5").Value
...

[Pat]

Thank you for that info, most invaluable.

My previous post was wrong.

I will now take on board what you have suggested on how to address the sheet i am after (eg bbb).

I just needed to prefix the command above with xlapp.work eg:
xlApp.Worksheets(sSheetname).Cells(2, 1) = DMax("SurveyYear", "tblStatsGDSCEQ") - DMin("SurveyYear", "tblStatsGDSCEQ") + 1 'DLookup("StartYearOffset", "tbl loc Control") + 1