Delete Excel worksheets from Access.

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

Perry

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
 
G

GJ_

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
 
D

Douglas J. Steele

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
 
N

Noah Meyer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top