Excel Applications.AddIns("xxxxx").Installed = True?

P

(PeteCresswell)

Working in MS Access VBA, I can make this work with an unqualified function lib
name as in:

2319 theSS.AddIns("Function").Installed = True

I'm guessing this looks in the path specified by some Office default or another.

But I've got users who have that particular library ("Function"....helluva name
for a library, I know.... but somebody else made it up...) in different places -
depending, I guess, on the parms supplied when Office was installed on their PC.


Seems like something along the lines of:

2319 theSS.AddIns("C:\Program Files\Microsoft
Office\Office10\Function.xla").Installed = True

should work to override any MS Office setting - but I can't make it work.
Don't have the error# handy, but it throws a runtime error when I try it.


Anybody know where I'm going wrong.
 
D

Dave Peterson

I think that this line:
theSS.AddIns("Function").Installed = True
will work if that addin is in the Tools|addins dialog. It won't have to be
checked, but it has to be on that list.

If the addin isn't on the list, you could look in the addin folder.

dim teststr as string
teststr = ""
on error resume next
teststr = dir(theSS.LibraryPath & "\function.xla")
on error goto 0

if teststr = "" then
'not there
else
thess.addins.add(theSS.LibraryPath & "\function.xla", copyfile:=false)
thess.addins("function").installed = true
end if

If the file isn't there, do you have another folder to look in--or could it be
anywhere?

And when you do
thess.addins(xxx)
xxx is not the unqualified filename, it's the title of the addin.
 
P

(PeteCresswell)

Per Dave Peterson:
thess.addins.add(theSS.LibraryPath & "\function.xla", copyfile:=false)
Click to expand...

That sounds like what I've been missing.

I'll try it today.

Thanks!
 
P

PeteCresswell

I think that this line:
theSS.AddIns("Function").Installed = True
will work if that addin is in the Tools|addins dialog. It won't have to be
checked, but it has to be on that list
Click to expand...


Here's what I'm doing now:
-------------------------------------
3155 .AddIns.Add "C:\Temp\FUNCTION.xla", copyfile:=False
3157 .AddIns("FUNCTION").Installed = True
-------------------------------------

Two weirdnesses, though:

1) Excel doesn't throw an error or even a "Do you want to replace..."
dialog even though "FUNCTION" is already on the list of addins shown
by Tools/Addins

2) When the sheet is opened and I check Tools/Addins, "FUNCTION" is
there and the checkbox is checked. However when I try a calc that
calles one of it's routines, Excel returns #NAME?. However if I go
to Tools/AddIns, uncheck the box, close the dialog, open the dialog
again, and check the box; the calculation that returned #NAME? now
works as expected.


Seems like Excel is trying to tell me something, but what?

There's something in the Help files about "Auto_Add functions" and I'm
wondering if FUNCTION.XLA is lacking something in that regard - but I
can't find anything about Auto_Add funcs.
 
D

Dave Peterson

If you reenter that formula with the function from the addin, does it work?

If yes, then maybe just doing
edit|replace
what: =
with: =
replace all

(the equivalent in code, actually)

would work ok.

Or maybe it's enough not to install the addin--maybe just opening that file
would be enough.

dim teststr as string
dim wkbk as workbook
teststr = ""
on error resume next
teststr = dir(theSS.LibraryPath & "\function.xla")
on error goto 0

if teststr = "" then
'not there
else
set wkbk = workbooks.open(filename:=thess.librarypath & "\Function.xla")
'in case there's an auto_open subroutine
wkbk.runautomacros 1 ' xlAutoOpen
end if
 
D

Dave Peterson

I would have guessed just opening the addin would have been sufficient--but I
didn't test it.

Glad you got it working.
 
P

(PeteCresswell)

Per Dave Peterson:
Or maybe it's enough not to install the addin--maybe just opening that file
would be enough.
Click to expand...

Opening it did the trick.

().Add
().Installed=True
..Open

Dunno if any of the above are redundant - I'm quitting while I'm ahead...-)
 
Top