if addin loaded on command line, it is not in addins collection

[scotty]

I load an addin from the command line (debugging in visual studio this
is the only option)

excel.exe somename.xla...etc

Now, the Applications.Addins collection will NOT have the addin I just
started from the command line.

Anyone know how to gain a handle to it?

it isnt a workbook, it isnt a worksheet etc.

 

[Chip Pearson]

You can reference an add-in via the Workbooks collection, even though
add-ins aren't included in the enumeration. In other words, you won't see
the add-in in

For Each WB In Workbook
' whatever
Next WB

But you can access it with code like the following:

Workbooks("CellView.xla").Worksheets(1).Range("A1").Value = 123

Note that if you change cell values in an add-in, or make any other changes,
you are NOT prompted to Save when the add-in is closed, and changes will NOT
be saved unless you do a Save operation.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Jon Peltier]

The addins collection will only show installed addins and I believe
uninstalled addins in the Library directory. If you open an addin from the
command line, or from Windows Explorer, or from File Open, it will appear as
a workbook in the collection, but not as an addin.

- Jon

 

[Peter T]

Just to add, if an addin that's not in the addins Library directory has been
installed then uninstalled it WILL persist in the addins collection though
not visible in Tools > addins. It will normally remain in the registry under
Excel/Addin Manager which is one of the three places the collection is
defined, the others being Excel/Options*/OPEN(installed) and the default
addin library path.

*in xl97 not /Options but /Microsoft Excel

To remove from the collection, even if no longer installed, would need to
force the addin manager to fail to find the addin or edit the registry.

Regards,
Peter T

 

[scotty]

thank you for your responses.

the unfortunate part is i am writing code to discover what addins are
loaded and then peform various mergers etc. So, what i really needed
was to run through the workbooks collection, do a strcomp on xla to
pick them out.

it seems i need prior knowledge of their names as the collection loop
won't work.

 

[Peter T]

Maybe the following will work for you, ie set references to all loaded
addins including any not in the addins collection.

If needs you could compare with the addins collection to determine if each
loaded addin exists in the collection, if it exists is it installed or if
exists but not installed it must have been loaded by other means. (BTW
compare using workbook.Title not .Name)

Sub test2()
dim i as Long
Dim s As String
Dim nm As Name
Dim vasAddins
Dim colAddins As AddIns

Set nm = ActiveWorkbook.Names.Add("myAddins", "=DOCUMENTS(2)")
' must be in a normal xls, so not thisworkbook if it's an addin

vasAddins = Application.Evaluate(nm.Name)

nm.Delete

If IsError(vasAddins) Or IsEmpty(vasAddins) Then
' no addins or method not successful
Exit Sub
End If

For i = 1 To UBound(vasAddins)
'remove any apostrophes
#If VBA6 Then
s = Replace(vasAddins(i), "'", "")
#Else
s = Application.Substitute(vasAddins(i), "'", "")
#End If
vasAddins(i) = s
Next

ReDim arrXLA(1 To UBound(vasAddins))
For i = LBound(vasAddins) To UBound(vasAddins)
Set arrXLA(i) = Workbooks(vasAddins(i))
Debug.Print arrXLA(i).FullName
Next

End Sub

Regards,
Peter T

 

[scotty]

Peter, many many thanks for taking the time to help on this.

I have studied and used the code and it works perfectly.

The key seems to be I think the "documents(2)". Might you expand on
what the means if possible. I tried searching etc, and couldn't find
any reference to what this is.

I appreciate there is a documents object, but manual I find seems to
suggest what (2) represents.

 

[Peter T]

The key seems to be I think the "documents(2)".

A bit more than 'seems' !

It's an old Excel4Macro. Documents with (2) returns an array of all loaded
addins irrespective as to whether they exist in the addins collection. (1)
returns all loaded xls, (3) all loaded files.

Try not deleting the name or create same manually and array enter =myAddins
in a row of cells.

I don't know how to return the full array in VBA without using a Name (which
for me needs to be in a visible xls). eg -

v = ExecuteExcel4Macro("DOCUMENTS(2)")
returns string name of the first addin
v = Array(ExecuteExcel4Macro("DOCUMENTS(2)"))
returns a I element 0 base array, the first addin

If you should work out another way post back.

I posted some links to different versions of Excel4Macro help files here -
http://tinyurl.com/ycgrsx

Regards,
Peter T

 

[Peter T]

In the example -

ReDim arrXLA(1 To UBound(vasAddins))

would be better like this

ReDim arrXLA(1 To UBound(vasAddins)) As Workbook

Regards,
Peter T