Excel hangs on Save As

[Guest]

I have a workbook which refuses to save once data has been imported. I am not
using an ActiveWorkbook.OnSave command and have tried turning everything I
can think of back on i.e. ScreenIpdating=True, EnableEvents=True,
IgnoreRemoteRequests=False, have unprotected the worksheets and macros and
unhidden all rows & columns.
Whether I try to SaveAs programatically, or via the Excel User Interface, I
sometimes get a status bar message of 'Calculating Cells 0%' - so I have
tried Application.CalculateBeforeSave = False. I have also run Windows
Update to ensure that the Verclsid.exe glitch is not the cause.
The save creates a 16KB file with a name such as 7ACA2000 or 8C805000, but
that is as far as it gets. CPU usage goes to 100% and even leaving it for
half an hour does not allow it to complete a save.

 

[Guest]

This is the Event Viewer log

Event Type: Error
Event Source: Application Hang
Event Category: (101)
Event ID: 1002
Date: 10/10/2007
Time: 09:33:41
User: N/A
Computer: PRIVATE
Description:
Hanging application EXCEL.EXE, version 11.0.8146.0, hang module hungapp,
version 0.0.0.0, hang address 0x00000000.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 41 70 70 6c 69 63 61 74 Applicat
0008: 69 6f 6e 20 48 61 6e 67 ion Hang
0010: 20 20 45 58 43 45 4c 2e EXCEL.
0018: 45 58 45 20 31 31 2e 30 EXE 11.0
0020: 2e 38 31 34 36 2e 30 20 .8146.0
0028: 69 6e 20 68 75 6e 67 61 in hunga
0030: 70 70 20 30 2e 30 2e 30 pp 0.0.0
0038: 2e 30 20 61 74 20 6f 66 .0 at of
0040: 66 73 65 74 20 30 30 30 fset 000
0048: 30 30 30 30 30 00000

 

[Dave Peterson]

Saved from a previous post:

When excel saves the file, it saves it as a temporary file with a funny name (8
characters--no extension) in the same folder.

If the save is successful, xl will delete the original (or rename it to its
backup name (like "backup of book1.xlk)) and if that's successful, xl will
rename the funny named file to the original's name.

If you're seeing that funny named file, then something is going wrong.

Common things that get blamed for interruptions to this process are antivirus
software poking its head in or network errors--either permissions or physical
problems.

=======
So you may want to try saving to a local drive or disable your antivirus
software to see if it works.

If one of those works, then I'd stop looking.

If neither works, can you save the same workbook on a different pc?

Maybe it's time to rebuild that troublesome workbook.

 

[Guest]

I am saving it to a local drive. If I copy the data to new workbook then it
saves straight away, however, if I copy all the worksheets and macros over,
then if hangs again - well, it may not be a total hang as if I strip out most
of the lines of data, then it does save after taking a fair while to think
about it. This is repeatable on another PC. I'm just angling for clues as to
what could potentially cause this.

 

[Ed Klein]

Zarch,
I am having almost exactly the same issue - in my case it occurs only after
I create a pivot table connecting to external data (both manually and
programmatically).
This behavior seems to have begun suddenly.
Did you ever find a solution?

 

[Zarch]

Hi Ed,
I ended up with a workaround. I was importing the data from an XML file into
an unpopulated templete. What I did was to force the user to save the
workbook when they opened the template, before the data import routines
kicked in. It then saved OK after the data was imported, overwriting the
existing file.
Perhaps you could try saving your workbook (programatically) before creating
the pivot table?
Regards,
Z

 

[Ed Klein]

I can save the file, manually or progammatically, right up to the time I
create a pivot table. Once the pivot table is created (again manually or
programmatically - but ONLY using the IBM notessql ODBC driver), the file
cannot be saved.

One additional thing that I have noticed - periodically the code wants to
open an old version of the same workbook to execute a date-timestamp function
I created to save the pivot table name. I created an entirely new workbook
and imported my code modules to make a clean start, but I still see that
periodically. There is nothing that I can find that refers to the old version
(different name) of the function.
Perhaps there is some sort of turd hanging on that is causing this save
problem - I have learned that coincidences are usually something to pay
attention to.

Thanks for the thoughts.

Regards,

Ed