Delete Excel worksheets from Access.

  • Thread starter Thread starter Perry
  • Start date Start date
P

Perry

I tried to delete all the Excel worksheets from within Access except the
first sheet but was unsuccessful. This is the code.

Sub test1()
Dim oXL, wk, sht1, cnt, ptr
Set oXL = CreateObject("Excel.Application")
Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
ptr = wk.sheets.Count
Debug.Print "Before count:" & ptr
For ptr = 2 To ptr
wk.worksheets(ptr).Delete
Next ptr
Debug.Print "After count:" & wk.sheets.Count
wk.Close
Set oXL = Nothing
End Sub

Thank you for the help.
 
Sorry for the typo, the routine should read. It executed but sheets were not
deleted.

Sub test1()
Dim oXL, wk, sht1, cnt, ptr, ptr1
Set oXL = CreateObject("Excel.Application")
Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
ptr = wk.sheets.Count
Debug.Print "Before count:" & ptr
For ptr1 = 2 To ptr
wk.worksheets(ptr1).Delete
Next ptr1
Debug.Print "After count:" & wk.sheets.Count
wk.Close
Set oXL = Nothing
End Sub
 
Op 3-4-2010 4:03, Perry schreef:
Sorry for the typo, the routine should read. It executed but sheets were not
deleted.

Sub test1()
Dim oXL, wk, sht1, cnt, ptr, ptr1
Set oXL = CreateObject("Excel.Application")
Set wk = oXL.workbooks.Open(CurrentProject.Path& "\POReport#2.xls")
ptr = wk.sheets.Count
Debug.Print "Before count:"& ptr
For ptr1 = 2 To ptr
wk.worksheets(ptr1).Delete
Next ptr1
Debug.Print "After count:"& wk.sheets.Count
wk.Close
Set oXL = Nothing
End Sub
Click to expand...

Hi,

Could it be the case, that your code deletes worksheet 2, (your former
worksheet 3 will be worksheet 2 from now on), then your code skips to
worksheet 3 (formerly known as ws 4) and deletes it, etc?
Or were none of the sheets deleted at all?

I would try something like this (code not tested):

Dim oXL, wk, sht1, cnt, ptr, ptr1
Set oXL = CreateObject("Excel.Application")
Set wk = oXL.workbooks.Open(CurrentProject.Path & "\POReport#2.xls")
ptr = wk.sheets.Count
Debug.Print "Before count:" & ptr
While ptr > 2
ptr = wk.sheets.Count
wk.worksheets(ptr).Delete
Wend
Debug.Print "After count:" & wk.sheets.Count
wk.Close
Set oXL = Nothing

End Sub
 
GJ_ is correct: the problem is because the worksheets renumber themselves
when you delete.

A solution, though, is to delete from the end:

For ptr1 = ptr To 2 Step -1
wk.worksheets(ptr1).Delete
Next ptr1

Another is

For ptr1 = 2 To ptr
wk.worksheets(2).Delete
Next ptr1
 
Hi Perry,

I would use Excel's built in "worksheets" collection to make sure you
are deleting the correct sheets. Also, Excel is probably prompting
you for confirmation before deleting each sheet. To disable this
confirmation, set the DisplayAlerts property to False. Also, when you
close the workbook you should explicitly save the workbook. Your code
should look something like this:

Sub test1()
Dim oXL, wk, sht1, cnt, ptr, ptr1
Set oXL = CreateObject("Excel.Application")
Set wk = oXL.workbooks.Open(CurrentProject.Path &
"\POReport#2.xls")

oXL.DisplayAlerts = False

Dim ws as oXL.Worksheet

For Each ws In Excel.Worksheets

If ws.Index <> 1 Then
ws.Delete
End If

Next ws

wk.Close True

oXL.DisplayAlerts = True


Set oXL = Nothing

End Sub
 
Back
Top