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

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

Thank you. It did not work. I tried many different ways without success.

"GJ_" wrote:

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Perry" <(E-Mail Removed)> wrote in message
news:F159184B-1DE4-4936-A526-(E-Mail Removed)...
> Thank you. It did not work. I tried many different ways without success.
>
> "GJ_" wrote:
>
>> 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
>>
>> 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
>>
>> .
>>

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



On Apr 2, 10:03*pm, Perry <Pe...@discussions.microsoft.com> wrote:
> 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