3

A vaguely related question seems to be: Remove AddIn path from UDF in Excel formula

Hello, I am having trouble keeping an excel vba add-in in sync between two computers.

Computer A Windows 7 - Excel 2007

Path for add-ins: %APPDATA%\Microsoft\AddIns

%APPDATA% = z:.windows\Application Data\

Computer B Windows 7 - Excel 2010

Path for add-ins = %APPDATA%\Roaming\Microsoft\AddIns

%APPDATA% = c:\Users\username\Roaming\

In each directory I have identical plugin.xlam add-in files. The add-in contains simple functions of the form:

Public Function avog()
avog = 6.02214129E+23
End Function

When I save a workbook using avog in computer A and open it in computer B, everyplace the function avog is used is replaced by 'pathname to addin file'!avog(). Since the path names on each computer are different, I have to search and replace the path string to remove it everywhere in the workbook. The same happens from B to A.

How can I tell excel to stop adding the path? Or is it possible to make excel aware that the function will be available in the other computer at a different location?

Community
  • 1
  • 1
sturgman
  • 257
  • 2
  • 8

2 Answers2

3

One easy way is to store it in the same absolute path, for example c:\program files\addins\xxxx.xlam.

assylias
  • 321,522
  • 82
  • 660
  • 783
  • I thought I tried this but it turns out I haven't. I thought for a while that excel copied the add-in from wherever it is located to its custom add-in folder. Alas, this does not seem to be the case with Excel 2010. A good work around. I will check tomorrow if it works with Excel 2007. I wish the %APPDATA% folders worked. – sturgman Apr 26 '12 at 23:59
  • I use this approach with Excel 2003/7/10 and it works fine + it is easy to update the addin automatically with a batch that copies the new version to that folder which is the same for everybody. – assylias Apr 27 '12 at 07:17
2

Get the absolute path from:

? Application.UserLibraryPath
Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191