[AccessD] Deleting Worksheet

Just tried brute force:

For intI = objXLBook.Worksheets.Count To 1 Step -1
MsgBox objXLBook.Worksheets(intI).Name
If Left(objXLBook.Worksheets(intI).Name, 5) = "Sheet" Then
    objXLBook.Worksheets(intI).Delete
End If
Next intI

But it leaves Sheet1 even though it executes the .Delete line. 

R


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 30, 2014 8:09 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Deleting Worksheet

Just tried:

Set objXLWS = objXLBook.Worksheets("Sheet1") objXLWS.Delete

But the Sheet1 is still there. :(

Rocky
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 30, 2014 8:05 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Deleting Worksheet

I have my objects dimmed so:

Dim objXLApp As Excel.Application
Dim objXLBook As Excel.Workbook
Dim objXLWS As Excel.Worksheet

I set objXLApp - Set objXLApp = New Excel.Application - and open a new
worksheet:

Set objXLBook = objXLApp.Workbooks.Open(Me.txtOutputFileName)

into which I copy the template worksheet from another XL workbook, giving it
a unique name each time.

At the end of the process I end up with a workbook with n number of
worksheet and all's well except I still have the default "Sheet1", "Sheet2",
and "Sheet3", which I want to delete.

I've tried several things among which are

            objXLBook.Worksheets("Sheet1").Delete

And 
            objXLApp.Worksheets("Sheet1").Activate
            objXLWS.Delete

But so far no joy.  


Rocky

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Thursday, January 30, 2014 7:47 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Deleting Worksheet

<
http://www.techrepublic.com/blog/10-things/10-ways-to-reference-excel-workbo
oks-and-sheets-using-vba/
>

#9 and #10 might be helpful, but they're very basic -- referencing 101.
What have you tried and why isn't it working?


On Thu, Jan 30, 2014 at 10:40 AM, Rocky Smolin
<rockysmolin at bchacc.com>wrote:

> Dear List:
>
> I'm automating a spreadsheet from Access (2003) and importing a 
> worksheet from another excel file as a template.  I want to delete the 
> default worksheets Sheet1, Sheet2, and Sheet3 but can't seem to get 
> the right syntax going.
>
> Any pointers on how to get this done?
>
> MTIA
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.bchacc.com <http://www.bchacc.com/> www.e-z-mrp.com 
> <http://www.e-z-mrp.com/>
> Skype: rocky.smolin
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com