Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interrupted sheet copying causes phantom Book2
Run this with a sheet1 that has enough calculations to make the copying
non-instantaneous. Sheets("sheet1").Copy If you press ESC quickly enough during the copying, you get a not-surprising error: "Copy method of Worksheet class failed." That's fine. However, if sheet1 has a pie chart in it, then the next time you exit from Excel, you get: "Do you want to save the changes you made to 'Book2'?" and if you naively say "Yes," Excel crashes. (And if you've tested it several times before exiting, it also asks you about Book3, Book4, etc.) When I trap the ESC key, the trapping works fine, but the same error happens when I exit Excel anyway. I was building a mechanism to allow users to interrupt a tiresome loop, but this makes me afraid to suggest using the ESC key at all. They could lose stuff in other open workbooks. I'm guessing I'm just going to have to live with this. Perhaps I'll have to re-assemble the pie chart after each copy. But I thought I'd at least document it. Dan Williams <danwPlanet |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interrupted sheet copying causes phantom Book2
I didn't try it, but do you have trouble if you change calculation to manual,
then copy the sheet, then change it to automatic? Dan Williams wrote: Run this with a sheet1 that has enough calculations to make the copying non-instantaneous. Sheets("sheet1").Copy If you press ESC quickly enough during the copying, you get a not-surprising error: "Copy method of Worksheet class failed." That's fine. However, if sheet1 has a pie chart in it, then the next time you exit from Excel, you get: "Do you want to save the changes you made to 'Book2'?" and if you naively say "Yes," Excel crashes. (And if you've tested it several times before exiting, it also asks you about Book3, Book4, etc.) When I trap the ESC key, the trapping works fine, but the same error happens when I exit Excel anyway. I was building a mechanism to allow users to interrupt a tiresome loop, but this makes me afraid to suggest using the ESC key at all. They could lose stuff in other open workbooks. I'm guessing I'm just going to have to live with this. Perhaps I'll have to re-assemble the pie chart after each copy. But I thought I'd at least document it. Dan Williams <danwPlanet -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interrupted sheet copying causes phantom Book2
I just tried it with manual calculation permanently on, and it still
happens. After posting this, I discovered that if you DON'T interrupt it, the over-simplified statement "Sheets("sheet1").Copy" actually copies the sheet into a newly-created Book2. This hints at where the phantom Book2 may come from. But the same thing does happen when you interrupt copying within the workbook, e.g., Sheets("sheet1").Copy befo=Worksheets(1) It's almost as if interrupting this statement makes processing stop after the "Copy" and before the "befo=" and it aborts in such a way as to sort of half-create Book2. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interrupted sheet copying causes phantom Book2
BTW:
Excel 2000 Windows 2000 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interrupted sheet copying causes phantom Book2
This line:
Sheets("sheet1").Copy should create a new workbook, so it's really not much of a phantom. But I don't have a guess why you're getting crashes. === Just some things to test... Clean up the windows temp folder (can't hurt and sometimes helps) Start excel in safe mode: close excel windows start button|Run excel /safe File|Open your workbook (macros will be disabled, though) hit alt-f11 (to get to the vbe) hit ctrl-g (to see the immediate window) type this and hit enter: Sheets("sheet1").Copy Does it crash excel? === One more question: Does this happen on all pc's (if you can test)? I don't have any guesses (no matter what your responses), but maybe someone else will. Dan Williams wrote: I just tried it with manual calculation permanently on, and it still happens. After posting this, I discovered that if you DON'T interrupt it, the over-simplified statement "Sheets("sheet1").Copy" actually copies the sheet into a newly-created Book2. This hints at where the phantom Book2 may come from. But the same thing does happen when you interrupt copying within the workbook, e.g., Sheets("sheet1").Copy befo=Worksheets(1) It's almost as if interrupting this statement makes processing stop after the "Copy" and before the "befo=" and it aborts in such a way as to sort of half-create Book2. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Book1 and Book2 | Excel Discussion (Misc queries) | |||
=SUMIF(book2!L3:AF3, "=book1!O30", book2!L20:AF20) | Excel Discussion (Misc queries) | |||
opens to Book2 | Excel Discussion (Misc queries) | |||
Copying Book1 to Book2 without selecting | Excel Programming | |||
run macro in book1 from book2 - how? | Excel Programming |