1

I have an Excel Add-In which uses a worksheet in its workbook to save some preferences data (eg the last used value of a refEdit control on a userform).

I then save the add-in workbook using vba thisworkbook.save when preferences are changed in the userform.

I have found that this sometimes creates an xlsm file in myDocuments rather than saving the add-in in place (see also a copy of excel add-in is created in my documents after saving).

How can I save the add-in in place (in the add-ins folder) without creating a copy? Note activeworkbook.save wouldn't work as it saves the open workbook not the add-in.

I could alternatively create a temp file for the preferences but using the sheets in the add-in workbook seems a good place to store data.

stephen pincher
  • 15
  • 5
  • 2
    `ThisWorkbook.Save` should work, assuming the add-in wasn't opened as read-only, but I personally think it's bad practice. I would suggest you store preferences in the registry using `SaveSetting` and `GetSetting` – Rory May 15 '18 at 11:32
  • Where is that add-In saved (which path)? I suspect that the user who is running that add-in doesn't have write access to that path and it saves into my documents instead. Can you check this? – Pᴇʜ May 15 '18 at 11:33
  • if read-only... there's a fix `ThisWorkbook.ChangeFileAccess(xlReadWrite)` ....! But yeah I agree with Rory on the preference thing. That or store an file in %appdata%. – Cody Ｇ May 15 '18 at 12:55
  • Thanks all. Regarding using the registry my add-in is just a .xla add-in not an .exe. Also some of the preference data is stored across a multi-cell range. For these reasons I think the temp file alternative is a better bet for an alternative. – stephen pincher May 15 '18 at 13:20
  • Regarding the read/write status of the containing folder: It should be read/write as the user copied the add-in into the folder himself. Wouldn't the code return an error rather than just save the file somewhere else? – stephen pincher May 15 '18 at 13:22
  • @stephenpincher See my answer. You can't save data on a add-in file. and the file will be saved in a folder used for addins. My add-ins are saved at: `C:\Users\a78208\AppData\Roaming\Microsoft\AddIns` where a78208 is my username. – Andreas May 15 '18 at 14:05

2 Answers2

0

As far as I know you can't have a file saved as an add-in and use the sheet on the file.
An add-in file is a VBA only file.

You say you want to save settings on the sheet. What about using the registry?

Saving data to the registry is very easy (actually easier than cells in my opinion).

To save a setting: 

SaveSetting "MyAddIn", "Settings", "Username", "BOB"

The above line creates a value in the registry of windows with the value "BOB" as "Username".

To get the setting from the registry you use: 

Username = GetSetting("MyAddIn", "Settings", "Username")

You can read about the method here

The good thing about saving it on the registry opposed to a sheet is that you can't by accident delete or manipulate the data.

Andreas
  • 23,610
  • 6
  • 30
  • 62
  • Thanks Andreas. You can read and write to the data on the worksheets in the add-in file through code. I was using cell references on thisworkbook.sheets(1) to store values. I'm going to change my plans now after working out the reason for the problem. I'll look into the registry option. – stephen pincher May 15 '18 at 14:21
0

I found the answer: The user had two copies of Excel open (2016 and 2010). This resulted in the add-in file being locked by one of the two open copies of Excel meaning that the second copy opened the add-in as read-only and couldn't save the changes.

A rare bug and one I didn't foresee. I'm going to rewrite to save the preferences into an %appdata% file instead.

I'd advise people to avoid using thisworkbook.save with addins.

Thanks everyone for your help.

stephen pincher
  • 15
  • 5