Excel 2010 addin (XLAM) does not save

I have a recurring issue when saving an Excel addin I have created. Basically when I try to save (either via the 'Save' button in the VBA editor or via 'thisworkbook.save') the file tries, retries over and over again to save and fails - reporting that there are errors.

During the attempts a 0 byte temporary file is created but this is removed once Excel has finally given up. It usually takes about 10 mins to give up.

What is even more confusing is that the error occurs randomly. Sometimes it can save sucessfully 8 times before an error and sometimes it happens every time - and every variation in between.

I only ever make small code changes (like 20 / 50 lines) between saves. So I can't imagine it is the changes and if so why the random success afterwards, surely after I have added a block of code which is causing an issue - the save should fail from then on, but it doesn't allways.

I only have one instance active at a time usually. There does not appear to be any correlation between the fails and multiple sessions.

I have tried the following:
Rebuilding the addin from scratch but creating a new Excel file and reimporting all forms and modules
Using a sub to save the addin by using 'thisworkbook.save'
Modifying my save sub to disable DDE interchange for the duration of the save (via 'Application.IgnoreRemoteRequests'). This was based on a web article I found about another user who had issue saving and XL 2010 XLAM
Restarting the machine after an error - before another attempt
Checking disks access to see if the virus checker is trying to scan the file and has therefore locked it
Made certain I have enough disk space and there are no quotas or anything that could be blocking the save.

Unfortunately (annoying & limiting I know) but I cannot upload the actual file as it is for a client and contains proprietary code and information.

Can anyone suggest what could be the cause because it is driving me mad and slowing development ?
LVL 3
AL_XResearchAsked:
Who is Participating?
 
AL_XResearchConnect With a Mentor Author Commented:
I have just update this post and closed - not sure why it didn't take
0
 
AL_XResearchAuthor Commented:
Sometimes when Excel gives up I get the error message attached - but sometimes it just does nothing (in which case it only takes a few seconds).

NOTE this error happened before and after I had rebuild the addin file from reimporting the modules.
Excel-save-error.PNG
0
 
jkpieterseCommented:
It certainly sounds like there is stuff in the add-in Excel doesn't appreciate.

No solution, but I have the habit of doing my changes in a normal xlsm file and once I'm done saving it as add-in.

Have you also tried setting EnableEvents to false before the save and disabled any application-wide events your add-in might have?

Have you run Code Cleaner on the file (which is more or less the same as importing everything into a new workbook)?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
AL_XResearchAuthor Commented:
jkpieterse: Thanks for you comments

Unfortunately I cannot do my development in a XMSM as the project is too huge and has to be tested (along with the code's interaction with it's child workbooks) as each core feature is added. The system is not just one or two macro(s) running from an addin it is a director-style controller that oversees and manipulates multiple workbooks and has it's own custom ribbon and when functioning needs to be an addin. If it were not an XLAM it would not work. It would be very time consuming and laborious to add 40 lines of code, save as addin, test, re-save as XLSM, add another function and so on.

I have not tried setting EnableEvents before the save. It is definitely worth keeping in mind ... however ...

I have found that when on either of the client's two machines - I get the save problem. When I transferred the file to my machine - no issue at all (so far several dozen successful consecutive saves).

That would suggest a) it has something to do with the client's network (what ever that could be - maybe the GPO) rather than a specific computer / unstable system and b) it is not the Excel file or the contents.

(Note: Both the 2 computers from the client and my own laptop use Win 7 x64)

All Excel developers know how 9 times out of 10 Excel and VBA errors do not refer to the actual problem but the fallout from it.
0
 
AL_XResearchAuthor Commented:
I was sharing another user's login (who was logged in at the same time - albeit on a separate machine on the same network) and for that reason I am going to log in as another user and see if that makes a difference.

For over a week now I have been working on my own laptop and each save (I do it almost every 30 mins or more when significant code added) and each one has succeeded !
0
 
AL_XResearchAuthor Commented:
I have now have this (once so far) on my laptop !

If the file is corrupt (which I highly doubt since I recently rebuilt it by reimporting the modules) then why has it worked without error for two weeks on my laptop ? When it did not work on the client's machine so I copied it (without any correction or cleaning action) to my laptop and it worked first time and since.

Could there be some hidden temporary file that is building up slowly as I develop the code ? It would have to be something on-disk as on that would explain why transferring the file to another machine instantly corrected the issue.

On the other hand it could be a different cause by the same symptom - the zero byte file.

Any assistance would be greatly appreciated !
0
 
jkpieterseCommented:
Lots of stuff might be wrong:
- Networking issue
- Virus software
- Temporary files
- Bad Office installation
- Some problem with the file after all
- ...
Hard to say which it is.
0
 
AL_XResearchAuthor Commented:
jkpieterse:

- Networking issue
  As moving it from he client's system to my laptop originally cured the problem I think we can rule this one out. Especially since my laptop is not on a network - total independent (except for a wireless connection to the internet)

- Bad Office installation
Two different copies of office installed at separate times. One in a corporate environment (and licensed using a central server) and the other installed for local use manually and independently activated

- Some problem with the file after all
How could a corruption 'sleep' for two weeks and hundreds of saves and all that VBA development and then suddenly appear one date whereas in the client environment it occurs every time. Very odd sort of corruption.

- Virus software
I checked the client's system (via Task Manager) to see if the on-access protection was active while I saved, it didn't seem to be. I have disabled my laptop's on-access protection and tested - I still get the error
0
 
jkpieterseCommented:
About the office installations:
Not sure what you mean by two different copies?

Even so, whether or not the installation was done properly, it still sometimes happens that problems surface.
Sometimes they are profile related and can be overcome by removing the user from the system and putting him back in again.

Is your project using any references to external libraries? Such as ADO, common controls, refedit, ...?

Any external controls on userforms?

Any ActiveX controls on worksheets?
0
 
AL_XResearchAuthor Commented:
About the office installations:
Not sure what you mean by two different copies?
Sorry, I mean two different installs, done at two different times on different platforms using different activation systems and licenses.

Even so, whether or not the installation was done properly, it still sometimes happens that problems surface.
Sometimes they are profile related and can be overcome by removing the user from the system and putting him back in again.
As you say it happens, but two different users on two different systems and failing then working and then failing and working ... If a user profile is corrupt it stays corrupt and I have seen no other signs of it.

Is your project using any references to external libraries? Such as ADO, common controls, refedit, ...?
Yes it has a number of references. The main one of which is IBM Cognos TM1. I have done a great deal of development against that addin and have never had this issue before.

Any external controls on userforms?
No

Any ActiveX controls on worksheets?
No
0
 
AL_XResearchAuthor Commented:
I have just tried moving the addin to a new path - in case there was any issue with a hidden file on that path etc... No joy I am afraid.
0
 
AL_XResearchAuthor Commented:
If I run the following code to save a copy of the XLAM
Thisworkbook.saveas thisworkbook.path & "\Sixth.xlam",xlOpenXMLAddin

Open in new window

and a new file is saved without a problem

However when I delete the old file and rename the new, open it, add code and try to save I get the same error
0
 
jkpieterseCommented:
Does it open well after running code cleaner? If so, code cleaner saves the file when done, so that might be a feasible work-around?
0
 
AL_XResearchAuthor Commented:
What is 'Code Cleaner' - who is it from ?

From what you have said it basically exports all the modules, cleans the file, reimports and saves as another file. Well I have now several times done the following:

Exported all modules, classes, forms and worksheets
Copied the RibbonX
Listed out the references
Created a brand new XLAM file
Added back the references
Added back the RibbonX
Reimported all code

Even after all that I still get the error. How can that be any different from this tool you are referring to ?

Note the problem is saving - not loading. Apart from the saving there is no issue.
0
 
AL_XResearchAuthor Commented:
I should add that I thought I had solved it - that it was a coding error.

Basically the addin is acting as a director-style app that has it's own app event watcher class.  One of the events 'Workbook_BeforeSave' had some legacy code where it tried to make one of the addin's worksheet active - which of course would be an error. I disabled the code and the error disappeared.

I have then been using it on my laptop and the client's network with no issue for several days and hundreds of saves.

I am sorry to say I have now got the error again.

I have a small sub that just does a 'Thisworkbook.save' and I have added 'EnableEvents = FALSE' and 'IgnoreRemoteRequests' but nothing seems to work.
0
 
jkpieterseCommented:
Code cleaner does not create a new file, it does the process against the existing file.

www.appspro.com turns up as first hit if you google for VBA code cleaner :-)

Another hunch: Maybe one of the userforms is causing all of this somehow. Have you got any (very) complex ones in the project?
0
 
AL_XResearchAuthor Commented:
Yes that is the one I found. Is it an install or an addin ? As this would be on a client's system they may not allow it.

No particularly large userforms

The code is huge though - over 25,000 lines of code, 15 userforms, 40 modules. Basically a rewrite of a system I wrote several years ago of the same size and complexity.

None of the modules are over 64K in size (which used to be an issue saving Workbooks).
0
 
jkpieterseCommented:
I have had a project that kept causing problems. I ended up pasting all the code in textfiles rather than exporting them. Perhaps something to try,
0
 
AL_XResearchAuthor Commented:
Well I am testing another change. There is another event procedure that seem to be called when the last crash happened (I have an external text file that records each procedure entered) and since I put 'Exit sub' at the top, to nullify that code, I have not had an issue. Touch wood !

I can't see anything in that procedure that would cause an issue though.
0
 
AL_XResearchAuthor Commented:
Unfortunately my amendment only seemed to work for a bit last week and this morning the error is back.

I will try rebuilding the file again.
0
 
jkpieterseCommented:
Does your add-in perhaps have an application-wide before_save event?
If so you might try disabling that for the add-in itself?
0
 
AL_XResearchAuthor Commented:
I rebuilt the file (which now saves again) and the only difference I can see is that it is 200K smaller than the file that would not save. We all know that Excel gathers 'content' that is unneeded during development but in all my years developing Excel VBA I have never had this problem and an issue that is this persistent and cannot be diagnosed.

It will be impossible to develop if every other day I have to rebuild the XLAM from scratch.

All I am doing is adding lines of code - nothing that should cause repeated randomly timed corruption.
0
 
AL_XResearchAuthor Commented:
Does your add-in perhaps have an application-wide before_save event?
Yes and I disabled it as my first 'solution' - but that the error came back after a while (and I checked the code was still enabled.

If so you might try disabling that for the add-in itself?
In fact I a) set the event watcher class to nothing before i saved and b) set Application.EnableEvents to FALSE before I saved. Both either have no success or short-lived success.

I am going spare - I have tried every diagnostic technique I know and it still causes errors - even after a rebuild.
0
 
jkpieterseCommented:
Have you ever tried re-doing the forms (i.e. not import them, but actually reconstruct)?
0
 
AL_XResearchAuthor Commented:
Sadly that would not be possible - way to complex. It could take me a day to rebuild the most complex form.

But the fact that it works sometimes and not others is what confused me. It feels like a 'cache buildup' type of issue.
0
 
AL_XResearchAuthor Commented:
I made sure AutoRecover was disabled before each save - no joy.
0
 
AL_XResearchAuthor Commented:
It is nothing like proof but it seems to me that if i open the addin, make some code changes (not matter how large) and save using by sub it works fine but if i make some changes over half an hour say and then save I get the error !

I mean that happens even if I actually input the same amount of code / changes. Say for example a save fails after I have amended some code. I will copy the amended code to notepad, close the file and reopen, paste the amended code in the VBA module, save and it now works, Very very strange.
0
 
jkpieterseCommented:
Indeed.
Lets take a step back.

After getting the error message, does Excel tell you what errors it encountered?  (perhaps you already mentioned this)

I have a tool that writes all controls and their properties of all userforms onto an Excel sheet. It can subsequently read that sheet to rebuild the userforms (including event code behind them). It does not handle multipages and the like, but it might get you half-way there :-)
0
 
AL_XResearchAuthor Commented:
Since my last post it has saved every time (I have done nothing special). Just now it has started failing.
0
 
AL_XResearchAuthor Commented:
I have had it again this morning (after 3 successful saves in one session and the reopening) I tried to do a 'SaveAs' using the following code once I had the issue - Excel could not even do that !
ThisWorkbook.SaveAs ThisWorkbook.path & "\Test.xlam",xlOpenXMLAddin

Open in new window

0
 
jkpieterseCommented:
You could try the attached tool to export your forms to a worksheet.
Then remove all userforms, save-as (copy!!) the workbook, close it, open it (disable macros) and run the tool again to rebuild the forms.
ControlLister.xls
0
 
AL_XResearchAuthor Commented:
I am still getting the error and still random.

Worked all day this monday.
Error at 10 am
Working ...
Error at 2 pm
Working ...
Error at 3.45pm
Working ...
Wont save at all know.

I have tried restarting the machine, shutting down the machine and rebuilding the book.
0
 
AL_XResearchAuthor Commented:
I may have found the cause, I am giving it until next week before I am sure.
0
 
AL_XResearchAuthor Commented:
Sorry to say that the hunt goes on ! I found that one of the forms was corrupt. I recreated it from scratch and after that for 4 days there were no issues. The problem is back and the same form appears fine.

Also if the corrupt form was the cause I would expect the issue to be constant - not random.
0
 
AL_XResearchAuthor Commented:
I am wondering if is a 'dual factor issue'. There appears to be an issue with the client's network in that sometimes (with a normal Excel file) other network users will not be able to save an Excel file that they have just opened (and is not shared) because it comes back and says that the file is locked. This occurs randomly. Sometimes just cancelling and dismissing the prompt allows the users to save as normal.

I am therefore wondering if the same issue sometimes occurs during a save of my VBA heavy addin and this corrupts the file somehow.

Anyone any comments, thoughts or experiences ?
0
 
jkpieterseConnect With a Mentor Commented:
I was suspecting a corrupt form, hence my suggestion to use the little tool I uploaded.
0
 
AL_XResearchConnect With a Mentor Author Commented:
Strange seems to be entirely a network issue. The cause is currently unknown. But some days it saves over 100 times without error and then it will fail. If you wait a few minutes it will save. I suspect that (as I said in my last post) the network issue is something like latency that causes Excel to think the file is still locked and unavailable.

Thanks for all your help and advice jkpieterse.
0
 
AL_XResearchAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 500 points for jkpieterse's comment #a39688255
Assisted answer: 0 points for AL_XResearch's comment #a39719787

for the following reason:

The question has not been resolved, I believe I had found the nature of the issue and there is no apparent solution but jkpieterse provided much needed help.
0
 
AL_XResearchAuthor Commented:
The issue is not resolved but jkpieterse has provided valuable assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.