Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas refuse to update | Excel Worksheet Functions | |||
How use info in Excel shared worksheets to create new worksheets | Excel Worksheet Functions | |||
Charts refuse to Zoom | Charts and Charting in Excel | |||
I refuse to be let down by the experts | Excel Programming | |||
Need code to protect worksheets - amount of worksheets varies | Excel Programming |