Unable to save edits to xlam

[cuddling101]

Office Version:Office 2010 Operating System:Windows 7 Program:Excel


I have opened my xlsm spreadsheet and then proceeded via the View Code link to edit code in my loaded xlam, which I have previously written.


Though I can edit the code, I cannot then save it.


I get the message


Microsoft Office Excel cannot access the file 'C\Program Files (x86)\MyExcelAdd-Ins\94ED8D40'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

I have noticed that each time I have tried, I received a different last 8 digit hex string, but the rest of the message was always the same.


This is the first time I have tried to edit the macro since transferring the workbook and add-in to Windows 7, from my previous XP machine. The transferred spreadsheet and add-in functionality has been working perfectly. It is just that now I have to make some edits to the macro, and it won't let me.


Maybe my approach to doing the edits is wrong. It happily saved the edits that were done in the module1 area of the spreadsheet itself. It just wouldn't accept the edits of the xlam.

I learn from another board that I am not alone with this problem, somebody else is experiencing the same thing.

Any ideas please.

 

[cuddling101]

Using the repair feature is the recommended option.

I literally just downloaded the tech guarantee version of 2010 yesterday. This problem has existed since I went to edit the VBA a few weeks after transferring it from my old, now decommissioned, XP machine to my new Windows 7 machine. I first tried to edit under 2007, then did the tech guarantee update to 2010 - the problem was the same under both versions.

How do I get to the repair?

 

[cuddling101]

The file is not read only. I just tried the edit by simply having the xlam open and not the xlsm that uses it, and it still wouldn't take the edit.

 

[cuddling101]

Dear Richard

This work around worked, but was quite a bit more fiddly than that. I worked my way through. I will write up all the steps I took and post it here and on the Microsoft Answers board in a few days time.

Thank you

Philip

 

[cuddling101]

You could try saving it down as a regular xlsm file and modifying it in this state then saving back down as an xlam file.

When in the VBE, open up the Immediate Window (Ctrl+G) and type in (amending as necessary):

Workbooks("MyXLAMfilename.xlam").IsAddin = False
Workbooks("MyXLAMfilename.xlam").SaveAs "C:\SomeFolder\MyWorkingFile",xlWorkbookNormal

press Enter after each line and then continue working in the (now) xlsx/m file. Then, when finished save back down as an add-in.
<!-- / message --> <!-- sig --> __________________
Richard Schollar
Microsoft MVP - Excel

Dear Richard

This work around worked, but was quite a bit more fiddly than that. I worked my way through. I will write up all the steps I took and post it here and on the Microsoft Answers board in a few days time.

Thank you

Philip

 

[cuddling101]

The key steps of this solution were originally posted by Richard Schollar, Microsoft MVP - Excel on the MrExcel Message Board.

I have simply fleshed it out into a hopefully easy to follow step by step set of instructions.

The essence of the solution is to save the xlam as a as a regular xlsm file, modify it in that state, and then save it back as an xlam file. The experience is specific to a Windows 7, Excel 2010 environment.

I have also used some specific names, rather than more generic names, as I find that easier to read.

1. Within Windows Explorer in C drive create a directory named Workaround.

2. Take careful note of where your xlam file is currently located, in my case it was in C:\Program Files (x86)\MyExcelAdd-Ins.

3. Open up the spreadsheet where you are unable to save the edited xlam.

4. Open up the VBE (View Code).

5. When in the VBE, open up the Immediate Window (Ctrl+G) and type in (amending the italicised words to the filename of your xlam file, as necessary):

Workbooks("MyXLAMfilename.xlam").IsAddin = False

6. Press Enter.

7. Now type in, still within the VBE

Workbooks("MyXLAMfilename.xlam").SaveAs "C:\Workaround\MyXLAMfilename",xlWorkbookNormal

8. Press Enter. You may wish at this point to also do a Select All, copy and then paste the text of your xlam into a notepad file, just as a backup, in case you make a mistake along the way.

9. Close VBE.

10. Open the Add-In Manager from the Developer tab.

11. Untick the add-in that you are wanting to edit.

12. Save your Excel spreadsheet (you may at this stage find that parts of your spreadsheet are 'corrupted', don't worry it will all come good in the end.

13. Close Excel.

14. Open, using Excel, the workbook format version of your xlam, which you created in step 6 at C:\Workaround\MyXLAMfilename.xlsm

15. Open up the VBE (View Code).

16. Do what ever edits you need to do the code.

17. Close VBE.

18. Save as the file as an Excel Add-In.

19. Close Excel.

20. Here comes what I found to be the 'trick for new players'
The Save As of the file as an Excel Add-In will have placed it in -

C:\Users\{Your User Name}\AppData\Roaming\Microsoft\AddIns

If, like me, you don't want to edit your spreadsheet to look at a new location, this is what is then necessary.

21. Within Windows Explorer, go to the directory where you have your old xlam, in my case this was C:\Program Files (x86)\MyExcelAdd-Ins.

22. Delete the xlam file, as you now want to replace it with your new version.

23. Within Windows Explorer copy your updated xlam from C:\Users\{Your User Name}\AppData\Roaming\Microsoft\AddIns to the directory where you want it to be, again, in my case this was C:\Program Files

(x86)\MyExcelAdd-Ins.

24. Open your spreadsheet, if at this point you get errors about links, cancel updating and just get into the spreadsheet - the spreadsheet may appear 'corrupted' at this time.

25. Go in to Add-In Manager on the Developer Tab.

26. Put the tick back against your Add-In; if the Add-In is not showing in the list, then Browse to the directory where you have placed it and add it back in to the list of Add-Ins known to that spreadsheet.

27. Close the Add-In manager.

28. Press F9 to recalculate your spreadsheet using the new version of your Add-In.

That is what did it for me, at least with my own code.

I have though hit a snag with Laurent Longre's morefunc, which I call extensively. Unfortunately his code is password protected, and though it appears to load into the Functions area, Excel 2010 does not see the functions as valid. It seems they're there but not really, so to say. I am hoping to find an answer somewhere; if I don't my spreadsheet world is 'screwed'.

I know it's laborious but it worked for me.

 

[cuddling101]

The workaround I finally developed to this problem is -

The key steps of this solution were originally posted by Richard Schollar, Microsoft MVP - Excel on the MrExcel Message Board.

I have simply fleshed it out into a hopefully easy to follow step by step set of instructions.

The essence of the solution is to save the xlam as a as a regular xlsm file, modify it in that state, and then save it back as an xlam file. The experience is specific to a Windows 7, Excel 2010 environment.

I have also used some specific names, rather than more generic names, as I find that easier to read.

1. Within Windows Explorer in C drive create a directory named Workaround.

2. Take careful note of where your xlam file is currently located, in my case it was in C:\Program Files (x86)\MyExcelAdd-Ins.

3. Open up the spreadsheet where you are unable to save the edited xlam.

4. Open up the VBE (View Code).

5. When in the VBE, open up the Immediate Window (Ctrl+G) and type in (amending the italicised words to the filename of your xlam file, as necessary):

Workbooks("MyXLAMfilename.xlam").IsAddin = False

6. Press Enter.

7. Now type in, still within the VBE

Workbooks("MyXLAMfilename.xlam").SaveAs "C:\Workaround\MyXLAMfilename",xlWorkbookNormal

8. Press Enter. You may wish at this point to also do a Select All, copy and then paste the text of your xlam into a notepad file, just as a backup, in case you make a mistake along the way.

9. Close VBE.

10. Open the Add-In Manager from the Developer tab.

11. Untick the add-in that you are wanting to edit.

12. Save your Excel spreadsheet (you may at this stage find that parts of your spreadsheet are 'corrupted', don't worry it will all come good in the end.

13. Close Excel.

14. Open, using Excel, the workbook format version of your xlam, which you created in step 6 at C:\Workaround\MyXLAMfilename.xlsm

15. Open up the VBE (View Code).

16. Do what ever edits you need to do the code.

17. Close VBE.

18. Save as the file as an Excel Add-In.

19. Close Excel.

20. Here comes what I found to be the 'trick for new players'
The Save As of the file as an Excel Add-In will have placed it in -

C:\Users\{Your User Name}\AppData\Roaming\Microsoft\AddIns

If, like me, you don't want to edit your spreadsheet to look at a new location, this is what is then necessary.

21. Within Windows Explorer, go to the directory where you have your old xlam, in my case this was C:\Program Files (x86)\MyExcelAdd-Ins.

22. Delete the xlam file, as you now want to replace it with your new version.

23. Within Windows Explorer copy your updated xlam from C:\Users\{Your User Name}\AppData\Roaming\Microsoft\AddIns to the directory where you want it to be, again, in my case this was C:\Program Files (x86)\MyExcelAdd-Ins.

24. Open your spreadsheet, if at this point you get errors about links, cancel updating and just get into the spreadsheet - the spreadsheet may appear 'corrupted' at this time.

25. Go in to Add-In Manager on the Developer Tab.

26. Put the tick back against your Add-In; if the Add-In is not showing in the list, then Browse to the directory where you have placed it and add it back in to the list of Add-Ins known to that spreadsheet.

27. Close the Add-In manager.

28. Press F9 to recalculate your spreadsheet using the new version of your Add-In.

That is what did it for me, at least with my own code.