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