[AccessD] Deleting Worksheet

There is no need to select the sheets - it just slows things down.

objXLApp.Sheets("Sheet1").Delete
objXLApp.Sheets("Sheet2").Delete
objXLApp.Sheets("Sheet3").Delete

will work faster

Cheers
Darryl.

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

Paul:

Gonna try it.  Although it's more of an esthetic thing that substantive.
The extra blank worksheets don't bother the user - just bothers me. L)

R
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Paul Hartland
Sent: Wednesday, February 26, 2014 7:37 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Deleting Worksheet

Rocky ,

Just nicked a bit of your code from a previous message, have you tried (off top of my head),:

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)

' add your new sheet, unless already done

' now firstly try to delete with individual code.
objXLApp.displayalerts = False

objXLApp.Sheets("Sheet1").Select
objXLApp.ActiveSheet.Delete

objXLApp.Sheets("Sheet2").Select
objXLApp.ActiveSheet.Delete

objXLApp.Sheets("Sheet3").Select
objXLApp.ActiveSheet.Delete

xlBook.Saveas ("your filename")
objXLApp.displayalerts = True

objXLApp.Sheets("your new sheet name").Select

objXLApp.Quit
Set objXLBook = Nothing
Set objXLApp = Nothing

Paul


On 30 January 2014 15:40, 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
>



--
Paul Hartland
paul.hartland at googlemail.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