PC Review


Reply
Thread Tools Rate Thread

Delete Excel worksheets from Access.

 
 
Perry
Guest
Posts: n/a
 
      3rd Apr 2010
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.
 
Reply With Quote
 
 
 
 
Perry
Guest
Posts: n/a
 
      3rd Apr 2010
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
 
Reply With Quote
 
 
 
 
GJ_
Guest
Posts: n/a
 
      3rd Apr 2010
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

 
Reply With Quote
 
Perry
Guest
Posts: n/a
 
      3rd Apr 2010
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
>
> .
>

 
Reply With Quote
 
Douglas J. Steele
Guest
Posts: n/a
 
      3rd Apr 2010
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:(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
>>
>> .
>>



 
Reply With Quote
 
Noah Meyer
Guest
Posts: n/a
 
      5th Apr 2010
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 <(E-Mail Removed)> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How use info in Excel shared worksheets to create new worksheets =?Utf-8?B?ZGtj?= Microsoft Excel Worksheet Functions 0 28th Jun 2007 08:36 PM
Worksheets are referenced in expressions, how make the worksheets visible? Excel 2000 L Mehl Microsoft Excel Misc 2 27th Jul 2004 07:20 PM
Sort Numerically Worksheets via VB when creating a new worksheets John Microsoft Excel Programming 6 1st Jun 2004 07:21 AM
refer to data on multiple worksheets using hlookup/look up data on many worksheets? qiong Microsoft Excel Misc 2 24th Dec 2003 01:22 AM
Need code to protect worksheets - amount of worksheets varies Sandy Microsoft Excel Programming 1 9th Sep 2003 02:17 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:20 PM.