Darryl Collins
darryl at whittleconsulting.com.au
Thu Jan 30 20:30:40 CST 2014
Ok... that all looks good.
Did you try the 'opposite' approach. Delete all sheets except the one you want to keep as Lambert suggested earlier. I would also ensure the sheets targeted for deletion are visible and unprotected.
Dim xlS as Excel.worksheets
For Each xlS In objXLApp.Worksheets
If xlS.Name <> objXLWS.Name Then
xlS .Visible = True
xlS.Unprotect
xlS.Delete
End If
Next xlS
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 1:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Deleting Worksheet
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