[AccessD] Deleting Worksheet

Rocky Smolin rockysmolin at bchacc.com
Thu Jan 30 18:23:58 CST 2014


Darryl:

I added true but no cigar.  The other two 'Sheets' Sheet2 and Sheet3 delete
just fine.  But Sheet1 remains even though I watched it go through the
delete statement for Sheet1.  Odd that.
 

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

Hi Rocky.


Firstly you would have to add the "TRUE" post fix to the following if you
want the changes to stick.

    objXLApp.ActiveWorkbook.Close

should be

    objXLApp.ActiveWorkbook.Close TRUE

If you just use    objXLApp.ActiveWorkbook.Close than the workbook will
close without saving (false is the default).

I would also be leery of using 'ActiveWorkbook' as it may not be the
workbook you think it is.  Be specific:  I usually use something like


Dim objExcelApp As Object
Dim objExcelSheet As Excel.Worksheet
Dim objExcelWB As Excel.Workbook

Set objExcelApp = CreateObject("Excel.Application")
        objExcelApp.Visible = True
        
        With objExcelApp
            .Workbooks.Open FileName:=strTargetPathAndName
            Set objExcelWB = objExcelApp.ActiveWorkbook

	With objExcelWB.Worksheets("Lookups")
				Etc....


Have you (for testing purposes) made the XL application visible and stepped
thru the code and see if you are actually deleting the sheets?  This is
probably the best way to debug this.

I would also turn off any "On Error Resume Next" statement (or all On Error
Statements).  Perhaps the source workbook has protection turned on, which
would prevent sheet deletion and the On Error is skipping over this step
without warning.

Lambert's code below should work fine - assuming there is no issue with XL
protection.  Keep in mind there is a different between the SheetName (which
the user sees) and the SheetCodeName, which is the VBA name.  They are often
the same, but don't have to be.  In your case you are using sheetname, which
should work fine on the code you have.

I do this sort of thing all the time, so let me know if you cannot get it
working.

cheers
Darryl.





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

Lambert:

After the deletes I have

' Save and Close Workbook
    objXLApp.ActiveWorkbook.Save
    objXLApp.Quit
    Set objXLApp = Nothing 

Adding 

    objXLApp.ActiveWorkbook.Close

Didn't help.  Sheet1 is still there.

R

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

After your code runs to delete the offending worksheets you then must save
the workbook with its changes. Like this...


Dim xlApp As Excel.Application
Dim xlS As Excel.Worksheet
    Set xlApp = Excel_OpenWorkBook(strFile) ' my canned "open an excel
workbook object routine"
    For Each xlS In xlApp.Worksheets
        If xlS.Name <> "MySheet" Then
            xlS.Delete
        End If
    Next xlS
    Excel_CloseWorkBook xlApp, strFile, True ' the True parameter tells my
canned routine to save the workbook
    Set xlApp = Nothing



Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
Sent: Thursday, January 30, 2014 11: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

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



More information about the AccessD mailing list