Solved: Return default save path for an excel add-in

[Danny]

Greetings,
I am trying to add a code to a workbook (.xls) so that it will save itself as an add-in (.xla), then reference that add-in. It will be distributed to multiple people (usernames) that have different versions of Excel (2002 & 2003). Is there a way to return the default add-in (.xla) path for each user after the workbook (.xls) is opened?

Thanks

[Bob Phillips]

Danny,

That doesn't make a lot of sense (to me). What exactly are you trying to do that won't work by creating an addin and distibuting that?

[Danny]

Xld,
Well I may be making this more difficult than it needs to be. What I have done is just email a workbook to people, have them open it and save as an add-in (with the same name) by changing the save as type to an excel add-in which will automatically change the path. This has proven to be difficult for several people, so I was wanted to just write something that would install the .xla when .xls is opened. Plus, as all the bugs are being worked out I am sending out new versions, so I wanted the code to save itself over the existing .xla file. Any ideas on a better way to do this would also be appreciated.
Thanks

[Bob Phillips]

My only thought is, why don't you save it as an addin, and mail the addin out to them. I can't see the value in youor approach.

If you want to save over existing versions, look at a self-extracting installer, such as Inno Setup.

[Danny]

Would that automatically save in the correct folder, so that it could still be accessed through tools>add-ins? I am trying to make this as simple as possible on their end.

[GTO]

Greetings,

I'm fairly wiped, but had read this earlier and was thinking a (reasonable or retarded as the case may be) solution might be a .vbs script file.

Is there a sharedrive that has no access restrictions (that is, everyone can open a file on the sharedrive/folder)?

If so, do ya'll have Outlook as your email?

If we're still good there, I was thinking that you could save the .xla to an accessible folder, and stick a vbs file there as well.

Then email all, with a hyperlink to the script file.

I'm certain not a new idea, but I've sent out "shortcut-builders" (user clicks link, script runs, shortcut to a wb installed on user's desktop) this way, and it was pretty succesful, as required user only the ability to click once....

In this case, I was thinking that the script could create an instance of Excel and install the add-in.

Sorry no code with the idea, but way past rack time for me and I wanted to ask as to the sharedrive access.

Mark

[Danny]

Mark,
Yes there is a shared drive, and we do use outlook. I think that what you are talking about would work, but may be beyond my skill level. Perhaps you could give an example?

[GTO]

Off to bed for this lad, and I posted rather slowly and hadn't seen Bob's (XLD) last. Not familiar with Inno Setup, that may well be the thing to do.

Will try and check back this evening (it's 0638 hrs here) and come up with an example code if time permits.

In essence, the script (I think/hope this would be doable) would (if reqd) use Word or API to check processes if we needed to exnure excel not running, create an instance, install the add-in.

@XLD:

Does that sound like a thought, or problematic/goofy?

A great day to all,

Mark

[Bob Phillips]

Would that automatically save in the correct folder, so that it could still be accessed through tools>add-ins? I am trying to make this as simple as possible on their end.

On a rerun, Inno saves over the old file, so it is still installed in Excel.

[Danny]

I think I MAY have found something that will work. Do either of you see any potential problems with this? I also wanted to be able to set the reference to the add-in but I don't see how I would be able to do that.... any ideas??? Either way thanks for the help!!!


[vba]Sub SaveAsXLA()
ThisWorkbook.IsAddin = True
ChDir Application.UserLibraryPath
ThisWorkbook.SaveAs Filename:=Application.UserLibraryPath & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 3) & "xla"
End Sub[/vba]

[Bob Phillips]

I think this is a very crude approach, but if you must.

Best to use

[vba]

Sub SaveAsXLA()
ThisWorkbook.IsAddin = False
ThisWorkbook.IsAddin = True
ChDir Application.UserLibraryPath
ThisWorkbook.SaveAs Filename:=Application.UserLibraryPath & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 3) & "xla"
End Sub
[/vba]

it has proven to be more robust

[Danny]

it has proven to be more robust

Agreed. Thanks for the help guys.