[AccessD] Deleting Worksheet

Paul Hartland paul.hartland at googlemail.com
Wed Feb 26 09:35:12 CST 2014


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 31 January 2014 02:31, Charlotte Foust <charlotte.foust at gmail.com> wrote:

> So what is the original name of the sheet you're adding?  What is its name
> in its parent template? And is the value of your Me.lstJobs.ItemData(intI)
> expression a string that you can evaluate and test for?    I have workbooks
> with no "Sheet1" in them, so it isn't requiring that name that's the
> problem.  When you copy a worksheet it usually retains the name of the
> sheet that's being copied (unless it would be a duplicate, of course).
>  Maybe you need to count the number of worksheets in the workbook to be
> sure you're tackling the right problem.
>
> Charlotte
>
>
> On Thu, Jan 30, 2014 at 6:12 PM, Rocky Smolin <rockysmolin at bchacc.com
> >wrote:
>
> > Darryl:
> >
> > I'm not adding the new sheets - I'm copying from a template in another
> > workbook:
> >
> >                 objXLWSAlcoa.Copy
> After:=objXLBook.Sheets(intXLSheetCount)
> >                 objXLApp.ActiveSheet.Name = Me.lstJobs.ItemData(intI)
> >                 objXLApp.ActiveWorkbook.Save
> >                 Set objXLWS = objXLApp.ActiveSheet
> >
> > And that's working well.  The delete routine at the end cycles through
> all
> > the sheets and deletes any that start with "Sheet".
> >
> > I had three brute force deletes call out Sheet1, Sheet2, Sheet3.  It
> > deleted
> > 2 and 3 and left 1.  So I tried cycling through the sheets looking for
> > left("Sheet",5). No go.  I've tried a bunch of different things.  Nothing
> > seems to work.
> >
> > I'm leaving town tomorrow so I may take this up again when I get back on
> > Monday if I can't find a solution while I'm on the road.
> >
> > Thanks
> >
> > Rocky
> >
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl
> Collins
> > Sent: Thursday, January 30, 2014 5:22 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Deleting Worksheet
> >
> > Rocky,
> >
> > So the process should be.
> >
> > Open XL
> > Add new sheets (which are default named as "Sheet5", "Sheet6" assuming
> you
> > add two new ones).  I would also assume you are renaming these sheets.
> > Then remove Sheet1, Sheet2 and Sheet3.
> >
> > It smells a lot like you are not adding the sheets correctly, as a couple
> > of
> > folks have pointed out, the workbook must always have a sheet and XL
> won't
> > let you delete the last worksheet.
> >
> > Firstly I would ask why you are adding sheets and just not using the
> blanks
> > ones available.  You can just populate the default sheet1, rename it to
> > something useful and then delete the other two blanks if you so please.
> >
> > There can be advantages of bringing in a sheet from an external source,
> if
> > that is what you are doing (?)
> >
> > Can you post the code you are using to add and then delete the sheets?
> >
> > I would strongly recommend stepping thru the code and checking the
> creation
> > and deletion is working as expected.
> >
> > 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 11:41 AM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Re: [AccessD] Deleting Worksheet
> >
> > There is still one sheet left after I delete the three defaults created
> > when
> > the workbook is first opened.  It's that last one Sheet1 that I can't get
> > rid of.
> >
> > r
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
> > Sent: Thursday, January 30, 2014 4:38 PM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Deleting Worksheet
> >
> > Hi Rocky:
> >
> > Late to this topic but I thought that you can not have zero sheets in a
> > Excel spreadsheet any more than you can have zero tabs in a browser? IOW,
> > it
> > can be an blank sheet but not "no" sheets...so it is possible that you
> are
> > wasting your time.
> >
> > Jim
> >
> > ----- Original Message -----
> > From: "Rocky Smolin" <rockysmolin at bchacc.com>
> > To: "Access Developers discussion and problem solving"
> > <accessd at databaseadvisors.com>
> > Sent: Thursday, January 30, 2014 4:23:58 PM
> > Subject: Re: [AccessD] Deleting Worksheet
> >
> > 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
> >
> > --
> > 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
>



-- 
Paul Hartland
paul.hartland at googlemail.com


More information about the AccessD mailing list