Charlotte Foust
charlotte.foust at gmail.com
Thu Jan 30 20:31:57 CST 2014
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
>