Getting error looking for add in on workbook open

Question

I have a workbook that when open will scan for a specific addin, if it's already opened, it will not reopen it, but if it isn't, it will open it. First it will scan the application.vbe.vbprojects for the unregistered addin. If i open the workbook by itself it works fine, if i have a blank workbook open first then i open the workbook that will open the addin, i get a error:

Private Sub Workbook_Open()
Dim vbproj As Object
Dim theName As String
Dim flag as Boolean

For Each vbproj In Application.VBE.VBProjects
    theName = GetFilenameFromPath(vbproj.filename) <<--- Run-time error '76'" Path not found
    If theName = "AddIn.xlam" Then
        Flag = True
        Exit For
    End If
Next vbproj

    If Flag = False Then
    Workbooks.Open ("C:\AddIn.xlam"), , True
    Workbooks("AddIn.xlam").IsAddin = True
    End If

End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String    
        If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
            GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
        End If
End Function

Debug your code to see if `vbproj.filename` returns a value to work with. `Debug.Print vbproj.filename` — , Oct 25 '17 at 13:09
it doesn't return anything and that is one of the cause of the error but why? — pokemon_Man, Oct 25 '17 at 13:10
Then there is no object In Application.VBE.VBProjects. So you can add an Error Handler it there is no object, then set `flag = True` — , Oct 25 '17 at 13:16
I'm just curious as to why there would be no vbproject when open after opening a blank workbook compared to opening it by itself. — pokemon_Man, Oct 25 '17 at 13:25
I think it have to do something with also me activating the tab on open from UI Ribbon editor. — pokemon_Man, Oct 25 '17 at 13:32
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157479/discussion-between-david-g-and-pokemon-man). — , Oct 25 '17 at 13:34
https://stackoverflow.com/questions/287077/iterating-unregistered-add-ins-xla — Slai, Oct 25 '17 at 15:00

Slai · Accepted Answer · 2017-10-25T15:09:24.257

0

The Application.AddIns2 collection can be used in Office 2010 and later:

Dim isInstalled As Boolean, a As AddIn

For Each a In Application.AddIns2                              ' not sure if .IsOpen should be checked 
    If a.Name = "AddIn.xlam" Then isInstalled = a.Installed And a.IsOpen: Exit For 
Next

If Not isInstalled Then Workbooks.Open("AddIn.xlam", , True).IsAddin = True

AFAIK, .xla(m) files are always open with .IsAddin = True, so I think that's needed only for .xlsb format add-ins.

edited Oct 25 '17 at 15:09

answered Oct 25 '17 at 14:38

Slai

19,980
5
38
44

the addin in technically not installed, it should be just opened when the workbook opens, therefore if you run the above code, it doesn't find it. – pokemon_Man Oct 25 '17 at 14:46
I'm getting object doesnt' support method or property – pokemon_Man Oct 25 '17 at 14:54
oh darn, yes i forgot i was testing on 2007, i will check on 2016 – pokemon_Man Oct 25 '17 at 14:58
@pokemon_Man the pre 2010 version would be similar to yours, but with `On Error Resume Next` to ignore the error you get, but Office 2007 is not supported by Microsoft anymore https://support.microsoft.com/en-us/help/3198497/office-2007-approaching-end-of-extended-support – Slai Oct 25 '17 at 15:00

Getting error looking for add in on workbook open

1 Answers1