Worksheets that refuse to go away
 

I have some code that deletes a few worksheets.
After it's executed, the worksheets seem be deleted from the workbook.

However sometimes, I'll save and close the workbook and reopen it again
only to notice that the worksheets that were supposed ot have been
deleted are back again.

Other times I'll save and close it, and reopen the file and notice that
the worksheets are actually gone.

I'm working with the same excel file all the time and there's no other
code that would delete, reinsert the stubborn worksheets.

I tried yelling at the computer, that didn't do much

Can anyone tell me what it is i'm dealing with here?


--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

Worksheets that refuse to go away
 

code ?

--
Tim Williams
Palo Alto, CA


"bails" wrote in message
...

I have some code that deletes a few worksheets.
After it's executed, the worksheets seem be deleted from the workbook.

However sometimes, I'll save and close the workbook and reopen it again
only to notice that the worksheets that were supposed ot have been
deleted are back again.

Other times I'll save and close it, and reopen the file and notice that
the worksheets are actually gone.

I'm working with the same excel file all the time and there's no other
code that would delete, reinsert the stubborn worksheets.

I tried yelling at the computer, that didn't do much

Can anyone tell me what it is i'm dealing with here?


--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

Worksheets that refuse to go away
 

My first guess is that you're not opening the workbook that you just saved.

try putting
=cell("Filename",a1)
in an empty cell and see if it points to the same location that you
expected/saved.

bails wrote:

I have some code that deletes a few worksheets.
After it's executed, the worksheets seem be deleted from the workbook.

However sometimes, I'll save and close the workbook and reopen it again
only to notice that the worksheets that were supposed ot have been
deleted are back again.

Other times I'll save and close it, and reopen the file and notice that
the worksheets are actually gone.

I'm working with the same excel file all the time and there's no other
code that would delete, reinsert the stubborn worksheets.

I tried yelling at the computer, that didn't do much

Can anyone tell me what it is i'm dealing with here?

--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

--

Dave Peterson

Worksheets that refuse to go away
 

Sorry for being vague with my problem, here's some of the code I'm
using

An Access file exports some queries to the given excel file and calls
one of its procedures


Code:
--------------------
... setup xlapp and file objects

DoCmd.TransferSpreadsheet transfertype:=acExport, tablename:="Query", Filename:="...file.xls"

...

file.Open "...file.xls"

'Run populateData Procedure of the excel file
xlApp.Run "populateData"


xlApp.Visible = True

Set file = Nothing
Set xlApp = Nothing
--------------------



the procedure populateDate contains:


Code:
--------------------
With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
.Sheets("Qry_YearlyInvoiceSummary").Delete
.Sheets("Qry_YTDDiscardsGrouped").Delete
.Sheets("Qry_YTDInventoryCountsGrouped").Delete
Application.DisplayAlerts = True

End With
--------------------


I'm sure that i'm opening the same file all the time and sheet/file
names are spelled correctly


--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

Worksheets that refuse to go away
 

I'd remove the "on error statement". Maybe the worksheet names aren't what you
expected?????



bails wrote:

Sorry for being vague with my problem, here's some of the code I'm
using

An Access file exports some queries to the given excel file and calls
one of its procedures

Code:
--------------------
... setup xlapp and file objects

DoCmd.TransferSpreadsheet transfertype:=acExport, tablename:="Query", Filename:="...file.xls"

...

file.Open "...file.xls"

'Run populateData Procedure of the excel file
xlApp.Run "populateData"


xlApp.Visible = True

Set file = Nothing
Set xlApp = Nothing
--------------------

the procedure populateDate contains:

Code:
--------------------
With ActiveWorkbook
On Error Resume Next
Application.DisplayAlerts = False
.Sheets("Qry_YearlyInvoiceSummary").Delete
.Sheets("Qry_YTDDiscardsGrouped").Delete
.Sheets("Qry_YTDInventoryCountsGrouped").Delete
Application.DisplayAlerts = True

End With
--------------------

I'm sure that i'm opening the same file all the time and sheet/file
names are spelled correctly

--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

--

Dave Peterson

Worksheets that refuse to go away
 

Thanks for the tips, but i think i figured out part of the problem.
The .Delete action has to be saved. The worksheets may very well have
been deleted but if i close without saving the workbook, i guess the
workbook is restored to the state it was in before the delete actions.


but this still leaves some things unexplained.

soemthing else that happens is that, after the worksheets are deleted
and workbook is saved, when i run the Access file again, the
transferspreadsheet method will export a query as an additional copy.
what i mean by that is, suppose my query was called "Query", it will
appear in the workbook as a worksheet called "Query1" suggesting that
there's already a worksheet called "Query".
But the sheet was previously deleted the last time and is clearly not
there.

and again im 100% sure im looking at the same file.

Dave Peterson Wrote:
I'd remove the "on error statement". Maybe the worksheet names aren't
what you
expected?????

--

Dave Peterson


--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

Worksheets that refuse to go away
 

Maybe someone who knows Access/Queries will jump in.

bails wrote:

Thanks for the tips, but i think i figured out part of the problem.
The .Delete action has to be saved. The worksheets may very well have
been deleted but if i close without saving the workbook, i guess the
workbook is restored to the state it was in before the delete actions.

but this still leaves some things unexplained.

soemthing else that happens is that, after the worksheets are deleted
and workbook is saved, when i run the Access file again, the
transferspreadsheet method will export a query as an additional copy.
what i mean by that is, suppose my query was called "Query", it will
appear in the workbook as a worksheet called "Query1" suggesting that
there's already a worksheet called "Query".
But the sheet was previously deleted the last time and is clearly not
there.

and again im 100% sure im looking at the same file.

Dave Peterson Wrote:
I'd remove the "on error statement". Maybe the worksheet names aren't
what you
expected?????

--

Dave Peterson

--
bails
------------------------------------------------------------------------
bails's Profile: http://www.excelforum.com/member.php...o&userid=36159
View this thread: http://www.excelforum.com/showthread...hreadid=559383

--

Dave Peterson