Solved

Excel 2010 addin (XLAM) does not save

Posted on 2013-10-24
39
1,282 Views
Last Modified: 2013-12-20
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 ?
0
Comment
Question by:AL_XResearch
  • 28
  • 11
39 Comments
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39597166
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39599702
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39599802
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39611271
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39613761
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39613772
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39613796
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39613816
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39613865
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39613876
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39613894
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39613939
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39616617
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39616656
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39616698
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39617002
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39617041
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39617257
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621033
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 11

Expert Comment

by:jkpieterse
ID: 39621117
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621140
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621158
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39621215
Have you ever tried re-doing the forms (i.e. not import them, but actually reconstruct)?
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621258
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621260
I made sure AutoRecover was disabled before each save - no joy.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39621346
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39621499
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39622073
Since my last post it has saved every time (I have done nothing special). Just now it has started failing.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39623946
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
 
LVL 11

Expert Comment

by:jkpieterse
ID: 39624115
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39642186
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39660812
I may have found the cause, I am giving it until next week before I am sure.
0
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39671522
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39688087
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
 
LVL 11

Assisted Solution

by:jkpieterse
jkpieterse earned 500 total points
ID: 39688255
I was suspecting a corrupt form, hence my suggestion to use the little tool I uploaded.
0
 
LVL 3

Assisted Solution

by:AL_XResearch
AL_XResearch earned 0 total points
ID: 39719787
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
 
LVL 3

Author Comment

by:AL_XResearch
ID: 39719796
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
 
LVL 3

Accepted Solution

by:
AL_XResearch earned 0 total points
ID: 39719797
I have just update this post and closed - not sure why it didn't take
0
 
LVL 3

Author Closing Comment

by:AL_XResearch
ID: 39731375
The issue is not resolved but jkpieterse has provided valuable assistance
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
When you upgrade from Windows 8 to 8.1 or to Windows 10 or if you are like me you are on the Insider Program you may find yourself with many 450MB recovery partitions.  With a traditional disk that may not be a problem but with relatively smaller SS…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now