Thread: 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?

code ?

--
Tim Williams
Palo Alto, CA


"bails" <bails.2al0ch_1152286530.1947@excelforum-nospam.com> wrote in message
news:bails.2al0ch_1152286530.1947@excelforum-nospam.com...
>
> 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
>

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

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

... 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:

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

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

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.

Originally Posted by Dave Peterson

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

--

Dave Peterson

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