I'm trying to load an add-in when my workbook opens. First I created an
add-in at "C:\data\test_addin.xla" that contains the code found below in
"CODE 2" section. Next, I added code found below in "CODE 1" section to the
file "C:\data\test.xls". While the add-in was open, in the Module property,
I named the module "test_AddIn".

If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript out of
range" error and trace the error to the line

If AddIns("test_AddIn").Installed = False

in "CODE 1". To further troubleshoot, I manually added the add-in within
Excel's References and noticed the name of my add-in within References is
called "VBAProject".

I asume, the error is being caused because of the "VBAProject" name of my
add-in.

QUESTIONS:

1. How can I change the "VBAProject" name to "test_AddIn".
2. Will changing the name as described in QUESTION #1 allow my add-in to
load when the "C:\data\test.xls" workbook opens?
3. If I'm going about this wrong, can someone shed some light on how to
accomplish this task?



' CODE 1: ****************************

Sub Auto_Open()

Dim tempStr As String
tempStr = "C:\data\test_addin.xla"
If Dir(tempStr) = "" Then
MsgBox "You do not have the Test Add-In installed."
End
End If

If AddIns("test_AddIn").Installed = False _
Then AddIns("test_AddIn").Installed = True

End Sub

Sub Auto_Close()

If AddIns("test_AddIn").Installed = True _
Then AddIns("test_AddIn").Installed = False

End Sub


' CODE 2: ****************************

Sub Auto_Open()
MsgBox "Add-In Loaded"
End Sub

Looks like you don't particularly need to add the addin to the addin's
collection so just open and close it like a normal workbook, eg

Sub OpenCloseAddin(bOpen as boolean)
dim sFile as string
dim wb as workbook

sFile = "C:\data\test_addin.xla"
On error resume next
set wb = application.workboooks(sFile)
On error goto errH
if bOpen then
if wb is nothing then
application.workbooks.open sFile
end if
elseif not wb is nothing then
wb.close
end if
exit sub
errH:
if bOpen then
msgbox "Problem opening " & sFile
end if
end sub

warning - aircode !

Regards,
Peter T

"scott" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I'm trying to load an add-in when my workbook opens. First I created an
> add-in at "C:\data\test_addin.xla" that contains the code found below in
> "CODE 2" section. Next, I added code found below in "CODE 1" section to
the
> file "C:\data\test.xls". While the add-in was open, in the Module
property,
> I named the module "test_AddIn".
>
> If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript out
of
> range" error and trace the error to the line
>
> If AddIns("test_AddIn").Installed = False
>
> in "CODE 1". To further troubleshoot, I manually added the add-in within
> Excel's References and noticed the name of my add-in within References is
> called "VBAProject".
>
> I asume, the error is being caused because of the "VBAProject" name of my
> add-in.
>
> QUESTIONS:
>
> 1. How can I change the "VBAProject" name to "test_AddIn".
> 2. Will changing the name as described in QUESTION #1 allow my add-in to
> load when the "C:\data\test.xls" workbook opens?
> 3. If I'm going about this wrong, can someone shed some light on how to
> accomplish this task?
>
>
>
> ' CODE 1: ****************************
>
> Sub Auto_Open()
>
> Dim tempStr As String
> tempStr = "C:\data\test_addin.xla"
> If Dir(tempStr) = "" Then
> MsgBox "You do not have the Test Add-In installed."
> End
> End If
>
> If AddIns("test_AddIn").Installed = False _
> Then AddIns("test_AddIn").Installed = True
>
> End Sub
>
> Sub Auto_Close()
>
> If AddIns("test_AddIn").Installed = True _
> Then AddIns("test_AddIn").Installed = False
>
> End Sub
>
>
> ' CODE 2: ****************************
>
> Sub Auto_Open()
> MsgBox "Add-In Loaded"
> End Sub
>
>
>

I re-wrote my code into several subs listed below. I'd really like to learn
the method of adding the addin to the references. Do you have any code to
accomplish this?

I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I call
the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow my
code in CODE #2 to test if the add-in is loaded.

Basically, I'm striking out no matter what I try. I know there must be a way
to accomplish this feat.

Note: I did manage to properly name my project "test_AddIn" instead of the
default "VBAProject".


CODE 1: *************************

Sub loadAddIn()

ThisWorkbook.VBProject.References.AddFromFile _
"C:\data\test_addin.xla"
End Sub

ERROR 1: *************************

Run-time error '1004':
Method 'VBProject' of object '_Workbook' failed


CODE 2: *************************


Sub TestAddIn()

Dim tempStr As String
tempStr = "C:\data\test_addin.xla"
If Dir(tempStr) = "" Then
MsgBox "you do not have test_addin.xla installed"
End
End If

If AddIns("test_AddIn").Installed = False _
Then AddIns("test_AddIn").Installed = True

Dim bFound As Boolean
Dim obj
'see if there is a reference already
For Each obj In ThisWorkbook.VBProject.References
MsgBox "name: " & UCase(obj.Name)
If UCase(obj.Name) = "test_addin.xla" Then
bFound = True
Exit For
End If
Next obj

'if no reference then set a reference.
If bFound = False Then _
ThisWorkbook.VBProject.References.AddFromFile _
"C:\data\test_addin.xla"
End Sub


ERROR 2: *************************

Run-time error '9':
Subscript out of range




"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> Looks like you don't particularly need to add the addin to the addin's
> collection so just open and close it like a normal workbook, eg
>
> Sub OpenCloseAddin(bOpen as boolean)
> dim sFile as string
> dim wb as workbook
>
> sFile = "C:\data\test_addin.xla"
> On error resume next
> set wb = application.workboooks(sFile)
> On error goto errH
> if bOpen then
> if wb is nothing then
> application.workbooks.open sFile
> end if
> elseif not wb is nothing then
> wb.close
> end if
> exit sub
> errH:
> if bOpen then
> msgbox "Problem opening " & sFile
> end if
> end sub
>
> warning - aircode !
>
> Regards,
> Peter T
>
> "scott" <(E-Mail Removed)> wrote in message
> news:#(E-Mail Removed)...
>> I'm trying to load an add-in when my workbook opens. First I created an
>> add-in at "C:\data\test_addin.xla" that contains the code found below in
>> "CODE 2" section. Next, I added code found below in "CODE 1" section to
> the
>> file "C:\data\test.xls". While the add-in was open, in the Module
> property,
>> I named the module "test_AddIn".
>>
>> If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript out
> of
>> range" error and trace the error to the line
>>
>> If AddIns("test_AddIn").Installed = False
>>
>> in "CODE 1". To further troubleshoot, I manually added the add-in within
>> Excel's References and noticed the name of my add-in within References is
>> called "VBAProject".
>>
>> I asume, the error is being caused because of the "VBAProject" name of my
>> add-in.
>>
>> QUESTIONS:
>>
>> 1. How can I change the "VBAProject" name to "test_AddIn".
>> 2. Will changing the name as described in QUESTION #1 allow my add-in to
>> load when the "C:\data\test.xls" workbook opens?
>> 3. If I'm going about this wrong, can someone shed some light on how to
>> accomplish this task?
>>
>>
>>
>> ' CODE 1: ****************************
>>
>> Sub Auto_Open()
>>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "You do not have the Test Add-In installed."
>> End
>> End If
>>
>> If AddIns("test_AddIn").Installed = False _
>> Then AddIns("test_AddIn").Installed = True
>>
>> End Sub
>>
>> Sub Auto_Close()
>>
>> If AddIns("test_AddIn").Installed = True _
>> Then AddIns("test_AddIn").Installed = False
>>
>> End Sub
>>
>>
>> ' CODE 2: ****************************
>>
>> Sub Auto_Open()
>> MsgBox "Add-In Loaded"
>> End Sub
>>
>>
>>
>
>

You're loosing me! Is this thread about loading an addin, or adding a
references.

Anyway -

> ThisWorkbook.VBProject.References.AddFromFile _
> "C:\data\test_addin.xla"

The above looks fine, assuming the ref does not already exist and a valid
project in the path.

> Dim tempStr As String
> tempStr = "C:\data\test_addin.xla"
> If Dir(tempStr) = "" Then
> MsgBox "you do not have test_addin.xla installed"
> End
> End If

The message implies the addin is not in the addins collection rather than
not found in the specified folder.

Avoid the "End" statement. If necessary release any object variables and
use Exit Sub or Function, possibly all the way back up to the original
calling routine.


> If AddIns("test_AddIn").Installed =

Before the above, unless certain the addin exists in the addins collection
do -

On error resume next
Set adn = addins(the-Addin's-Title)
If adn is nothing then
Set adn = addins.add(C:\data\test_addin.xla")
End if

If not adn.installed then adn.installed = True


Normally once the reference has been added to a wb, when the wb opens the
referenced wb will open automatically.

Regards,
Peter T


"scott" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I re-wrote my code into several subs listed below. I'd really like to
learn
> the method of adding the addin to the references. Do you have any code to
> accomplish this?
>
> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I call
> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow my
> code in CODE #2 to test if the add-in is loaded.
>
> Basically, I'm striking out no matter what I try. I know there must be a
way
> to accomplish this feat.
>
> Note: I did manage to properly name my project "test_AddIn" instead of the
> default "VBAProject".
>
>
> CODE 1: *************************
>
> Sub loadAddIn()
>
> ThisWorkbook.VBProject.References.AddFromFile _
> "C:\data\test_addin.xla"
> End Sub
>
> ERROR 1: *************************
>
> Run-time error '1004':
> Method 'VBProject' of object '_Workbook' failed
>
>
> CODE 2: *************************
>
>
> Sub TestAddIn()
>
> Dim tempStr As String
> tempStr = "C:\data\test_addin.xla"
> If Dir(tempStr) = "" Then
> MsgBox "you do not have test_addin.xla installed"
> End
> End If
>
> If AddIns("test_AddIn").Installed = False _
> Then AddIns("test_AddIn").Installed = True
>
> Dim bFound As Boolean
> Dim obj
> 'see if there is a reference already
> For Each obj In ThisWorkbook.VBProject.References
> MsgBox "name: " & UCase(obj.Name)
> If UCase(obj.Name) = "test_addin.xla" Then
> bFound = True
> Exit For
> End If
> Next obj
>
> 'if no reference then set a reference.
> If bFound = False Then _
> ThisWorkbook.VBProject.References.AddFromFile _
> "C:\data\test_addin.xla"
> End Sub
>
>
> ERROR 2: *************************
>
> Run-time error '9':
> Subscript out of range
>
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > Looks like you don't particularly need to add the addin to the addin's
> > collection so just open and close it like a normal workbook, eg
> >
> > Sub OpenCloseAddin(bOpen as boolean)
> > dim sFile as string
> > dim wb as workbook
> >
> > sFile = "C:\data\test_addin.xla"
> > On error resume next
> > set wb = application.workboooks(sFile)
> > On error goto errH
> > if bOpen then
> > if wb is nothing then
> > application.workbooks.open sFile
> > end if
> > elseif not wb is nothing then
> > wb.close
> > end if
> > exit sub
> > errH:
> > if bOpen then
> > msgbox "Problem opening " & sFile
> > end if
> > end sub
> >
> > warning - aircode !
> >
> > Regards,
> > Peter T
> >
> > "scott" <(E-Mail Removed)> wrote in message
> > news:#(E-Mail Removed)...
> >> I'm trying to load an add-in when my workbook opens. First I created an
> >> add-in at "C:\data\test_addin.xla" that contains the code found below
in
> >> "CODE 2" section. Next, I added code found below in "CODE 1" section to
> > the
> >> file "C:\data\test.xls". While the add-in was open, in the Module
> > property,
> >> I named the module "test_AddIn".
> >>
> >> If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript
out
> > of
> >> range" error and trace the error to the line
> >>
> >> If AddIns("test_AddIn").Installed = False
> >>
> >> in "CODE 1". To further troubleshoot, I manually added the add-in
within
> >> Excel's References and noticed the name of my add-in within References
is
> >> called "VBAProject".
> >>
> >> I asume, the error is being caused because of the "VBAProject" name of
my
> >> add-in.
> >>
> >> QUESTIONS:
> >>
> >> 1. How can I change the "VBAProject" name to "test_AddIn".
> >> 2. Will changing the name as described in QUESTION #1 allow my add-in
to
> >> load when the "C:\data\test.xls" workbook opens?
> >> 3. If I'm going about this wrong, can someone shed some light on how to
> >> accomplish this task?
> >>
> >>
> >>
> >> ' CODE 1: ****************************
> >>
> >> Sub Auto_Open()
> >>
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "You do not have the Test Add-In installed."
> >> End
> >> End If
> >>
> >> If AddIns("test_AddIn").Installed = False _
> >> Then AddIns("test_AddIn").Installed = True
> >>
> >> End Sub
> >>
> >> Sub Auto_Close()
> >>
> >> If AddIns("test_AddIn").Installed = True _
> >> Then AddIns("test_AddIn").Installed = False
> >>
> >> End Sub
> >>
> >>
> >> ' CODE 2: ****************************
> >>
> >> Sub Auto_Open()
> >> MsgBox "Add-In Loaded"
> >> End Sub
> >>
> >>
> >>
> >
> >
>
>

I thought loading an add-in and adding a reference were the same. I'm mainly
an Access developer and used to adding/removing refeence libraries.

If I can manually add an .xla in Referencs, should I not be able to do it
with vba?


"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> You're loosing me! Is this thread about loading an addin, or adding a
> references.
>
> Anyway -
>
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>
> The above looks fine, assuming the ref does not already exist and a valid
> project in the path.
>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "you do not have test_addin.xla installed"
>> End
>> End If
>
> The message implies the addin is not in the addins collection rather than
> not found in the specified folder.
>
> Avoid the "End" statement. If necessary release any object variables and
> use Exit Sub or Function, possibly all the way back up to the original
> calling routine.
>
>
>> If AddIns("test_AddIn").Installed =
>
> Before the above, unless certain the addin exists in the addins collection
> do -
>
> On error resume next
> Set adn = addins(the-Addin's-Title)
> If adn is nothing then
> Set adn = addins.add(C:\data\test_addin.xla")
> End if
>
> If not adn.installed then adn.installed = True
>
>
> Normally once the reference has been added to a wb, when the wb opens the
> referenced wb will open automatically.
>
> Regards,
> Peter T
>
>
> "scott" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I re-wrote my code into several subs listed below. I'd really like to
> learn
>> the method of adding the addin to the references. Do you have any code to
>> accomplish this?
>>
>> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I
>> call
>> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow
>> my
>> code in CODE #2 to test if the add-in is loaded.
>>
>> Basically, I'm striking out no matter what I try. I know there must be a
> way
>> to accomplish this feat.
>>
>> Note: I did manage to properly name my project "test_AddIn" instead of
>> the
>> default "VBAProject".
>>
>>
>> CODE 1: *************************
>>
>> Sub loadAddIn()
>>
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>> End Sub
>>
>> ERROR 1: *************************
>>
>> Run-time error '1004':
>> Method 'VBProject' of object '_Workbook' failed
>>
>>
>> CODE 2: *************************
>>
>>
>> Sub TestAddIn()
>>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "you do not have test_addin.xla installed"
>> End
>> End If
>>
>> If AddIns("test_AddIn").Installed = False _
>> Then AddIns("test_AddIn").Installed = True
>>
>> Dim bFound As Boolean
>> Dim obj
>> 'see if there is a reference already
>> For Each obj In ThisWorkbook.VBProject.References
>> MsgBox "name: " & UCase(obj.Name)
>> If UCase(obj.Name) = "test_addin.xla" Then
>> bFound = True
>> Exit For
>> End If
>> Next obj
>>
>> 'if no reference then set a reference.
>> If bFound = False Then _
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>> End Sub
>>
>>
>> ERROR 2: *************************
>>
>> Run-time error '9':
>> Subscript out of range
>>
>>
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%(E-Mail Removed)...
>> > Looks like you don't particularly need to add the addin to the addin's
>> > collection so just open and close it like a normal workbook, eg
>> >
>> > Sub OpenCloseAddin(bOpen as boolean)
>> > dim sFile as string
>> > dim wb as workbook
>> >
>> > sFile = "C:\data\test_addin.xla"
>> > On error resume next
>> > set wb = application.workboooks(sFile)
>> > On error goto errH
>> > if bOpen then
>> > if wb is nothing then
>> > application.workbooks.open sFile
>> > end if
>> > elseif not wb is nothing then
>> > wb.close
>> > end if
>> > exit sub
>> > errH:
>> > if bOpen then
>> > msgbox "Problem opening " & sFile
>> > end if
>> > end sub
>> >
>> > warning - aircode !
>> >
>> > Regards,
>> > Peter T
>> >
>> > "scott" <(E-Mail Removed)> wrote in message
>> > news:#(E-Mail Removed)...
>> >> I'm trying to load an add-in when my workbook opens. First I created
>> >> an
>> >> add-in at "C:\data\test_addin.xla" that contains the code found below
> in
>> >> "CODE 2" section. Next, I added code found below in "CODE 1" section
>> >> to
>> > the
>> >> file "C:\data\test.xls". While the add-in was open, in the Module
>> > property,
>> >> I named the module "test_AddIn".
>> >>
>> >> If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript
> out
>> > of
>> >> range" error and trace the error to the line
>> >>
>> >> If AddIns("test_AddIn").Installed = False
>> >>
>> >> in "CODE 1". To further troubleshoot, I manually added the add-in
> within
>> >> Excel's References and noticed the name of my add-in within References
> is
>> >> called "VBAProject".
>> >>
>> >> I asume, the error is being caused because of the "VBAProject" name of
> my
>> >> add-in.
>> >>
>> >> QUESTIONS:
>> >>
>> >> 1. How can I change the "VBAProject" name to "test_AddIn".
>> >> 2. Will changing the name as described in QUESTION #1 allow my add-in
> to
>> >> load when the "C:\data\test.xls" workbook opens?
>> >> 3. If I'm going about this wrong, can someone shed some light on how
>> >> to
>> >> accomplish this task?
>> >>
>> >>
>> >>
>> >> ' CODE 1: ****************************
>> >>
>> >> Sub Auto_Open()
>> >>
>> >> Dim tempStr As String
>> >> tempStr = "C:\data\test_addin.xla"
>> >> If Dir(tempStr) = "" Then
>> >> MsgBox "You do not have the Test Add-In installed."
>> >> End
>> >> End If
>> >>
>> >> If AddIns("test_AddIn").Installed = False _
>> >> Then AddIns("test_AddIn").Installed = True
>> >>
>> >> End Sub
>> >>
>> >> Sub Auto_Close()
>> >>
>> >> If AddIns("test_AddIn").Installed = True _
>> >> Then AddIns("test_AddIn").Installed = False
>> >>
>> >> End Sub
>> >>
>> >>
>> >> ' CODE 2: ****************************
>> >>
>> >> Sub Auto_Open()
>> >> MsgBox "Add-In Loaded"
>> >> End Sub
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

I did manage to load the add-in into referencds after checking the option
under Tools|Macro that gives access to vba projects. However, no matter if
the add-in is loaded or not, I can't test it with vba. The code in CODE 2
below continues to give the subscript out of range error.

Why do you think excel is preventing me to simply test if a add-in reference
exists?

CODE 1: *************************

Sub loadAddIn()

ThisWorkbook.VBProject.References.AddFromFile _
"c:\data\test_addin.xla"

End Sub



CODE 2: *************************


Sub TestAddIn()

Dim tempStr As String
tempStr = "C:\data\test_addin.xla"
If Dir(tempStr) = "" Then
MsgBox "you do not have test_addin.xla installed"
End
End If

If AddIns("test_AddIn").Installed = False _
Then AddIns("test_AddIn").Installed = True

Dim bFound As Boolean
Dim obj
'see if there is a reference already
For Each obj In ThisWorkbook.VBProject.References
MsgBox "name: " & UCase(obj.Name)
If UCase(obj.Name) = "test_addin.xla" Then
bFound = True
Exit For
End If
Next obj

'if no reference then set a reference.
If bFound = False Then _
ThisWorkbook.VBProject.References.AddFromFile _
"C:\data\test_addin.xla"
End Sub


ERROR 2: *************************

Run-time error '9':
Subscript out of range



"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> You're loosing me! Is this thread about loading an addin, or adding a
> references.
>
> Anyway -
>
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>
> The above looks fine, assuming the ref does not already exist and a valid
> project in the path.
>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "you do not have test_addin.xla installed"
>> End
>> End If
>
> The message implies the addin is not in the addins collection rather than
> not found in the specified folder.
>
> Avoid the "End" statement. If necessary release any object variables and
> use Exit Sub or Function, possibly all the way back up to the original
> calling routine.
>
>
>> If AddIns("test_AddIn").Installed =
>
> Before the above, unless certain the addin exists in the addins collection
> do -
>
> On error resume next
> Set adn = addins(the-Addin's-Title)
> If adn is nothing then
> Set adn = addins.add(C:\data\test_addin.xla")
> End if
>
> If not adn.installed then adn.installed = True
>
>
> Normally once the reference has been added to a wb, when the wb opens the
> referenced wb will open automatically.
>
> Regards,
> Peter T
>
>
> "scott" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> I re-wrote my code into several subs listed below. I'd really like to
> learn
>> the method of adding the addin to the references. Do you have any code to
>> accomplish this?
>>
>> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I
>> call
>> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow
>> my
>> code in CODE #2 to test if the add-in is loaded.
>>
>> Basically, I'm striking out no matter what I try. I know there must be a
> way
>> to accomplish this feat.
>>
>> Note: I did manage to properly name my project "test_AddIn" instead of
>> the
>> default "VBAProject".
>>
>>
>> CODE 1: *************************
>>
>> Sub loadAddIn()
>>
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>> End Sub
>>
>> ERROR 1: *************************
>>
>> Run-time error '1004':
>> Method 'VBProject' of object '_Workbook' failed
>>
>>
>> CODE 2: *************************
>>
>>
>> Sub TestAddIn()
>>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "you do not have test_addin.xla installed"
>> End
>> End If
>>
>> If AddIns("test_AddIn").Installed = False _
>> Then AddIns("test_AddIn").Installed = True
>>
>> Dim bFound As Boolean
>> Dim obj
>> 'see if there is a reference already
>> For Each obj In ThisWorkbook.VBProject.References
>> MsgBox "name: " & UCase(obj.Name)
>> If UCase(obj.Name) = "test_addin.xla" Then
>> bFound = True
>> Exit For
>> End If
>> Next obj
>>
>> 'if no reference then set a reference.
>> If bFound = False Then _
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>> End Sub
>>
>>
>> ERROR 2: *************************
>>
>> Run-time error '9':
>> Subscript out of range
>>
>>
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%(E-Mail Removed)...
>> > Looks like you don't particularly need to add the addin to the addin's
>> > collection so just open and close it like a normal workbook, eg
>> >
>> > Sub OpenCloseAddin(bOpen as boolean)
>> > dim sFile as string
>> > dim wb as workbook
>> >
>> > sFile = "C:\data\test_addin.xla"
>> > On error resume next
>> > set wb = application.workboooks(sFile)
>> > On error goto errH
>> > if bOpen then
>> > if wb is nothing then
>> > application.workbooks.open sFile
>> > end if
>> > elseif not wb is nothing then
>> > wb.close
>> > end if
>> > exit sub
>> > errH:
>> > if bOpen then
>> > msgbox "Problem opening " & sFile
>> > end if
>> > end sub
>> >
>> > warning - aircode !
>> >
>> > Regards,
>> > Peter T
>> >
>> > "scott" <(E-Mail Removed)> wrote in message
>> > news:#(E-Mail Removed)...
>> >> I'm trying to load an add-in when my workbook opens. First I created
>> >> an
>> >> add-in at "C:\data\test_addin.xla" that contains the code found below
> in
>> >> "CODE 2" section. Next, I added code found below in "CODE 1" section
>> >> to
>> > the
>> >> file "C:\data\test.xls". While the add-in was open, in the Module
>> > property,
>> >> I named the module "test_AddIn".
>> >>
>> >> If I open "C:\data\test.xls" containing "CODE 1", I get a "Subscript
> out
>> > of
>> >> range" error and trace the error to the line
>> >>
>> >> If AddIns("test_AddIn").Installed = False
>> >>
>> >> in "CODE 1". To further troubleshoot, I manually added the add-in
> within
>> >> Excel's References and noticed the name of my add-in within References
> is
>> >> called "VBAProject".
>> >>
>> >> I asume, the error is being caused because of the "VBAProject" name of
> my
>> >> add-in.
>> >>
>> >> QUESTIONS:
>> >>
>> >> 1. How can I change the "VBAProject" name to "test_AddIn".
>> >> 2. Will changing the name as described in QUESTION #1 allow my add-in
> to
>> >> load when the "C:\data\test.xls" workbook opens?
>> >> 3. If I'm going about this wrong, can someone shed some light on how
>> >> to
>> >> accomplish this task?
>> >>
>> >>
>> >>
>> >> ' CODE 1: ****************************
>> >>
>> >> Sub Auto_Open()
>> >>
>> >> Dim tempStr As String
>> >> tempStr = "C:\data\test_addin.xla"
>> >> If Dir(tempStr) = "" Then
>> >> MsgBox "You do not have the Test Add-In installed."
>> >> End
>> >> End If
>> >>
>> >> If AddIns("test_AddIn").Installed = False _
>> >> Then AddIns("test_AddIn").Installed = True
>> >>
>> >> End Sub
>> >>
>> >> Sub Auto_Close()
>> >>
>> >> If AddIns("test_AddIn").Installed = True _
>> >> Then AddIns("test_AddIn").Installed = False
>> >>
>> >> End Sub
>> >>
>> >>
>> >> ' CODE 2: ****************************
>> >>
>> >> Sub Auto_Open()
>> >> MsgBox "Add-In Loaded"
>> >> End Sub
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

in line -

"scott" <(E-Mail Removed)> wrote in message
news:#(E-Mail Removed)...
> I thought loading an add-in and adding a reference were the same. I'm
mainly
> an Access developer and used to adding/removing refeence libraries.

No, adding a reference in the VBE from Tools > References is completely to
adding an Addin to the Addins collection in Excel, Tools > Addins. Though
you can, I suspect you do not need to do both, but which would depend on
needs. Perhaps all you need to do is load the addin as a normal workbook,
along lines I mentioned in my first reply. I don't know.

> If I can manually add an .xla in Referencs, should I not be able to do it
> with vba?

Talking about VBE, Tools References, indeed you can. The code you posted
previously to do that looked fine.

Regards,
Peter T


>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
> > You're loosing me! Is this thread about loading an addin, or adding a
> > references.
> >
> > Anyway -
> >
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >
> > The above looks fine, assuming the ref does not already exist and a
valid
> > project in the path.
> >
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "you do not have test_addin.xla installed"
> >> End
> >> End If
> >
> > The message implies the addin is not in the addins collection rather
than
> > not found in the specified folder.
> >
> > Avoid the "End" statement. If necessary release any object variables
and
> > use Exit Sub or Function, possibly all the way back up to the original
> > calling routine.
> >
> >
> >> If AddIns("test_AddIn").Installed =
> >
> > Before the above, unless certain the addin exists in the addins
collection
> > do -
> >
> > On error resume next
> > Set adn = addins(the-Addin's-Title)
> > If adn is nothing then
> > Set adn = addins.add(C:\data\test_addin.xla")
> > End if
> >
> > If not adn.installed then adn.installed = True
> >
> >
> > Normally once the reference has been added to a wb, when the wb opens
the
> > referenced wb will open automatically.
> >
> > Regards,
> > Peter T
> >
> >
> > "scott" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> I re-wrote my code into several subs listed below. I'd really like to
> > learn
> >> the method of adding the addin to the references. Do you have any code
to
> >> accomplish this?
> >>
> >> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I
> >> call
> >> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow
> >> my
> >> code in CODE #2 to test if the add-in is loaded.
> >>
> >> Basically, I'm striking out no matter what I try. I know there must be
a
> > way
> >> to accomplish this feat.
> >>
> >> Note: I did manage to properly name my project "test_AddIn" instead of
> >> the
> >> default "VBAProject".
> >>
> >>
> >> CODE 1: *************************
> >>
> >> Sub loadAddIn()
> >>
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >> End Sub
> >>
> >> ERROR 1: *************************
> >>
> >> Run-time error '1004':
> >> Method 'VBProject' of object '_Workbook' failed
> >>
> >>
> >> CODE 2: *************************
> >>
> >>
> >> Sub TestAddIn()
> >>
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "you do not have test_addin.xla installed"
> >> End
> >> End If
> >>
> >> If AddIns("test_AddIn").Installed = False _
> >> Then AddIns("test_AddIn").Installed = True
> >>
> >> Dim bFound As Boolean
> >> Dim obj
> >> 'see if there is a reference already
> >> For Each obj In ThisWorkbook.VBProject.References
> >> MsgBox "name: " & UCase(obj.Name)
> >> If UCase(obj.Name) = "test_addin.xla" Then
> >> bFound = True
> >> Exit For
> >> End If
> >> Next obj
> >>
> >> 'if no reference then set a reference.
> >> If bFound = False Then _
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >> End Sub
> >>
> >>
> >> ERROR 2: *************************
> >>
> >> Run-time error '9':
> >> Subscript out of range
> >>
> >>
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Looks like you don't particularly need to add the addin to the
addin's
> >> > collection so just open and close it like a normal workbook, eg
> >> >
> >> > Sub OpenCloseAddin(bOpen as boolean)
> >> > dim sFile as string
> >> > dim wb as workbook
> >> >
> >> > sFile = "C:\data\test_addin.xla"
> >> > On error resume next
> >> > set wb = application.workboooks(sFile)
> >> > On error goto errH
> >> > if bOpen then
> >> > if wb is nothing then
> >> > application.workbooks.open sFile
> >> > end if
> >> > elseif not wb is nothing then
> >> > wb.close
> >> > end if
> >> > exit sub
> >> > errH:
> >> > if bOpen then
> >> > msgbox "Problem opening " & sFile
> >> > end if
> >> > end sub
> >> >
> >> > warning - aircode !
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> > "scott" <(E-Mail Removed)> wrote in message
> >> > news:#(E-Mail Removed)...
> >> >> I'm trying to load an add-in when my workbook opens. First I created
> >> >> an
> >> >> add-in at "C:\data\test_addin.xla" that contains the code found
below
> > in
> >> >> "CODE 2" section. Next, I added code found below in "CODE 1" section
> >> >> to
> >> > the
> >> >> file "C:\data\test.xls". While the add-in was open, in the Module
> >> > property,
> >> >> I named the module "test_AddIn".
> >> >>
> >> >> If I open "C:\data\test.xls" containing "CODE 1", I get a
"Subscript
> > out
> >> > of
> >> >> range" error and trace the error to the line
> >> >>
> >> >> If AddIns("test_AddIn").Installed = False
> >> >>
> >> >> in "CODE 1". To further troubleshoot, I manually added the add-in
> > within
> >> >> Excel's References and noticed the name of my add-in within
References
> > is
> >> >> called "VBAProject".
> >> >>
> >> >> I asume, the error is being caused because of the "VBAProject" name
of
> > my
> >> >> add-in.
> >> >>
> >> >> QUESTIONS:
> >> >>
> >> >> 1. How can I change the "VBAProject" name to "test_AddIn".
> >> >> 2. Will changing the name as described in QUESTION #1 allow my
add-in
> > to
> >> >> load when the "C:\data\test.xls" workbook opens?
> >> >> 3. If I'm going about this wrong, can someone shed some light on how
> >> >> to
> >> >> accomplish this task?
> >> >>
> >> >>
> >> >>
> >> >> ' CODE 1: ****************************
> >> >>
> >> >> Sub Auto_Open()
> >> >>
> >> >> Dim tempStr As String
> >> >> tempStr = "C:\data\test_addin.xla"
> >> >> If Dir(tempStr) = "" Then
> >> >> MsgBox "You do not have the Test Add-In installed."
> >> >> End
> >> >> End If
> >> >>
> >> >> If AddIns("test_AddIn").Installed = False _
> >> >> Then AddIns("test_AddIn").Installed = True
> >> >>
> >> >> End Sub
> >> >>
> >> >> Sub Auto_Close()
> >> >>
> >> >> If AddIns("test_AddIn").Installed = True _
> >> >> Then AddIns("test_AddIn").Installed = False
> >> >>
> >> >> End Sub
> >> >>
> >> >>
> >> >> ' CODE 2: ****************************
> >> >>
> >> >> Sub Auto_Open()
> >> >> MsgBox "Add-In Loaded"
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Is the addin actually in the addins collection, your code does not check for
that. It needs to be before you can set the Installed property = true. Refer
to the notes I posted previously.

> Why do you think excel is preventing me to simply test if a add-in
reference
> exists?

What kind of reference are you referring to.

Where is your error generated.

Regards,
Peter T

"scott" <(E-Mail Removed)> wrote in message
news:e9$(E-Mail Removed)...
> I did manage to load the add-in into referencds after checking the option
> under Tools|Macro that gives access to vba projects. However, no matter if
> the add-in is loaded or not, I can't test it with vba. The code in CODE 2
> below continues to give the subscript out of range error.
>
> Why do you think excel is preventing me to simply test if a add-in
reference
> exists?
>
> CODE 1: *************************
>
> Sub loadAddIn()
>
> ThisWorkbook.VBProject.References.AddFromFile _
> "c:\data\test_addin.xla"
>
> End Sub
>
>
>
> CODE 2: *************************
>
>
> Sub TestAddIn()
>
> Dim tempStr As String
> tempStr = "C:\data\test_addin.xla"
> If Dir(tempStr) = "" Then
> MsgBox "you do not have test_addin.xla installed"
> End
> End If
>
> If AddIns("test_AddIn").Installed = False _
> Then AddIns("test_AddIn").Installed = True
>
> Dim bFound As Boolean
> Dim obj
> 'see if there is a reference already
> For Each obj In ThisWorkbook.VBProject.References
> MsgBox "name: " & UCase(obj.Name)
> If UCase(obj.Name) = "test_addin.xla" Then
> bFound = True
> Exit For
> End If
> Next obj
>
> 'if no reference then set a reference.
> If bFound = False Then _
> ThisWorkbook.VBProject.References.AddFromFile _
> "C:\data\test_addin.xla"
> End Sub
>
>
> ERROR 2: *************************
>
> Run-time error '9':
> Subscript out of range
>
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
> > You're loosing me! Is this thread about loading an addin, or adding a
> > references.
> >
> > Anyway -
> >
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >
> > The above looks fine, assuming the ref does not already exist and a
valid
> > project in the path.
> >
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "you do not have test_addin.xla installed"
> >> End
> >> End If
> >
> > The message implies the addin is not in the addins collection rather
than
> > not found in the specified folder.
> >
> > Avoid the "End" statement. If necessary release any object variables
and
> > use Exit Sub or Function, possibly all the way back up to the original
> > calling routine.
> >
> >
> >> If AddIns("test_AddIn").Installed =
> >
> > Before the above, unless certain the addin exists in the addins
collection
> > do -
> >
> > On error resume next
> > Set adn = addins(the-Addin's-Title)
> > If adn is nothing then
> > Set adn = addins.add(C:\data\test_addin.xla")
> > End if
> >
> > If not adn.installed then adn.installed = True
> >
> >
> > Normally once the reference has been added to a wb, when the wb opens
the
> > referenced wb will open automatically.
> >
> > Regards,
> > Peter T
> >
> >
> > "scott" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >> I re-wrote my code into several subs listed below. I'd really like to
> > learn
> >> the method of adding the addin to the references. Do you have any code
to
> >> accomplish this?
> >>
> >> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I
> >> call
> >> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even allow
> >> my
> >> code in CODE #2 to test if the add-in is loaded.
> >>
> >> Basically, I'm striking out no matter what I try. I know there must be
a
> > way
> >> to accomplish this feat.
> >>
> >> Note: I did manage to properly name my project "test_AddIn" instead of
> >> the
> >> default "VBAProject".
> >>
> >>
> >> CODE 1: *************************
> >>
> >> Sub loadAddIn()
> >>
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >> End Sub
> >>
> >> ERROR 1: *************************
> >>
> >> Run-time error '1004':
> >> Method 'VBProject' of object '_Workbook' failed
> >>
> >>
> >> CODE 2: *************************
> >>
> >>
> >> Sub TestAddIn()
> >>
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "you do not have test_addin.xla installed"
> >> End
> >> End If
> >>
> >> If AddIns("test_AddIn").Installed = False _
> >> Then AddIns("test_AddIn").Installed = True
> >>
> >> Dim bFound As Boolean
> >> Dim obj
> >> 'see if there is a reference already
> >> For Each obj In ThisWorkbook.VBProject.References
> >> MsgBox "name: " & UCase(obj.Name)
> >> If UCase(obj.Name) = "test_addin.xla" Then
> >> bFound = True
> >> Exit For
> >> End If
> >> Next obj
> >>
> >> 'if no reference then set a reference.
> >> If bFound = False Then _
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >> End Sub
> >>
> >>
> >> ERROR 2: *************************
> >>
> >> Run-time error '9':
> >> Subscript out of range
> >>
> >>
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Looks like you don't particularly need to add the addin to the
addin's
> >> > collection so just open and close it like a normal workbook, eg
> >> >
> >> > Sub OpenCloseAddin(bOpen as boolean)
> >> > dim sFile as string
> >> > dim wb as workbook
> >> >
> >> > sFile = "C:\data\test_addin.xla"
> >> > On error resume next
> >> > set wb = application.workboooks(sFile)
> >> > On error goto errH
> >> > if bOpen then
> >> > if wb is nothing then
> >> > application.workbooks.open sFile
> >> > end if
> >> > elseif not wb is nothing then
> >> > wb.close
> >> > end if
> >> > exit sub
> >> > errH:
> >> > if bOpen then
> >> > msgbox "Problem opening " & sFile
> >> > end if
> >> > end sub
> >> >
> >> > warning - aircode !
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> > "scott" <(E-Mail Removed)> wrote in message
> >> > news:#(E-Mail Removed)...
> >> >> I'm trying to load an add-in when my workbook opens. First I created
> >> >> an
> >> >> add-in at "C:\data\test_addin.xla" that contains the code found
below
> > in
> >> >> "CODE 2" section. Next, I added code found below in "CODE 1" section
> >> >> to
> >> > the
> >> >> file "C:\data\test.xls". While the add-in was open, in the Module
> >> > property,
> >> >> I named the module "test_AddIn".
> >> >>
> >> >> If I open "C:\data\test.xls" containing "CODE 1", I get a
"Subscript
> > out
> >> > of
> >> >> range" error and trace the error to the line
> >> >>
> >> >> If AddIns("test_AddIn").Installed = False
> >> >>
> >> >> in "CODE 1". To further troubleshoot, I manually added the add-in
> > within
> >> >> Excel's References and noticed the name of my add-in within
References
> > is
> >> >> called "VBAProject".
> >> >>
> >> >> I asume, the error is being caused because of the "VBAProject" name
of
> > my
> >> >> add-in.
> >> >>
> >> >> QUESTIONS:
> >> >>
> >> >> 1. How can I change the "VBAProject" name to "test_AddIn".
> >> >> 2. Will changing the name as described in QUESTION #1 allow my
add-in
> > to
> >> >> load when the "C:\data\test.xls" workbook opens?
> >> >> 3. If I'm going about this wrong, can someone shed some light on how
> >> >> to
> >> >> accomplish this task?
> >> >>
> >> >>
> >> >>
> >> >> ' CODE 1: ****************************
> >> >>
> >> >> Sub Auto_Open()
> >> >>
> >> >> Dim tempStr As String
> >> >> tempStr = "C:\data\test_addin.xla"
> >> >> If Dir(tempStr) = "" Then
> >> >> MsgBox "You do not have the Test Add-In installed."
> >> >> End
> >> >> End If
> >> >>
> >> >> If AddIns("test_AddIn").Installed = False _
> >> >> Then AddIns("test_AddIn").Installed = True
> >> >>
> >> >> End Sub
> >> >>
> >> >> Sub Auto_Close()
> >> >>
> >> >> If AddIns("test_AddIn").Installed = True _
> >> >> Then AddIns("test_AddIn").Installed = False
> >> >>
> >> >> End Sub
> >> >>
> >> >>
> >> >> ' CODE 2: ****************************
> >> >>
> >> >> Sub Auto_Open()
> >> >> MsgBox "Add-In Loaded"
> >> >> End Sub
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Finally got it. I had to re-write it a little, but this will load/unload and
test properly.

One final question:

Please explain what you meant by "addin actually in the addins collection,
your code does not check for that. It needs to be before you can set the
Installed property = true"

I don't exactly understand what the "addins" collection really means. And
BTW, thank you for your input on this issue.

CODE: ***********************************

Sub loadAddIn()

Dim bFound As Boolean

On Error Resume Next

For Each Ref In ThisWorkbook.VBProject.References
i = i + 1
If Ref.Name = "test_AddIn" Then
MsgBox "Test Add-In installed."
bFound = True
Exit For
End If

Next Ref

If bFound = False Then _
ThisWorkbook.VBProject.References.AddFromFile _
"C:\data\test_addin.xla"

On Error GoTo 0

End Sub


"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> Is the addin actually in the addins collection, your code does not check
> for
> that. It needs to be before you can set the Installed property = true.
> Refer
> to the notes I posted previously.
>
>> Why do you think excel is preventing me to simply test if a add-in
> reference
>> exists?
>
> What kind of reference are you referring to.
>
> Where is your error generated.
>
> Regards,
> Peter T
>
> "scott" <(E-Mail Removed)> wrote in message
> news:e9$(E-Mail Removed)...
>> I did manage to load the add-in into referencds after checking the option
>> under Tools|Macro that gives access to vba projects. However, no matter
>> if
>> the add-in is loaded or not, I can't test it with vba. The code in CODE
>> 2
>> below continues to give the subscript out of range error.
>>
>> Why do you think excel is preventing me to simply test if a add-in
> reference
>> exists?
>>
>> CODE 1: *************************
>>
>> Sub loadAddIn()
>>
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "c:\data\test_addin.xla"
>>
>> End Sub
>>
>>
>>
>> CODE 2: *************************
>>
>>
>> Sub TestAddIn()
>>
>> Dim tempStr As String
>> tempStr = "C:\data\test_addin.xla"
>> If Dir(tempStr) = "" Then
>> MsgBox "you do not have test_addin.xla installed"
>> End
>> End If
>>
>> If AddIns("test_AddIn").Installed = False _
>> Then AddIns("test_AddIn").Installed = True
>>
>> Dim bFound As Boolean
>> Dim obj
>> 'see if there is a reference already
>> For Each obj In ThisWorkbook.VBProject.References
>> MsgBox "name: " & UCase(obj.Name)
>> If UCase(obj.Name) = "test_addin.xla" Then
>> bFound = True
>> Exit For
>> End If
>> Next obj
>>
>> 'if no reference then set a reference.
>> If bFound = False Then _
>> ThisWorkbook.VBProject.References.AddFromFile _
>> "C:\data\test_addin.xla"
>> End Sub
>>
>>
>> ERROR 2: *************************
>>
>> Run-time error '9':
>> Subscript out of range
>>
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:(E-Mail Removed)...
>> > You're loosing me! Is this thread about loading an addin, or adding a
>> > references.
>> >
>> > Anyway -
>> >
>> >> ThisWorkbook.VBProject.References.AddFromFile _
>> >> "C:\data\test_addin.xla"
>> >
>> > The above looks fine, assuming the ref does not already exist and a
> valid
>> > project in the path.
>> >
>> >> Dim tempStr As String
>> >> tempStr = "C:\data\test_addin.xla"
>> >> If Dir(tempStr) = "" Then
>> >> MsgBox "you do not have test_addin.xla installed"
>> >> End
>> >> End If
>> >
>> > The message implies the addin is not in the addins collection rather
> than
>> > not found in the specified folder.
>> >
>> > Avoid the "End" statement. If necessary release any object variables
> and
>> > use Exit Sub or Function, possibly all the way back up to the original
>> > calling routine.
>> >
>> >
>> >> If AddIns("test_AddIn").Installed =
>> >
>> > Before the above, unless certain the addin exists in the addins
> collection
>> > do -
>> >
>> > On error resume next
>> > Set adn = addins(the-Addin's-Title)
>> > If adn is nothing then
>> > Set adn = addins.add(C:\data\test_addin.xla")
>> > End if
>> >
>> > If not adn.installed then adn.installed = True
>> >
>> >
>> > Normally once the reference has been added to a wb, when the wb opens
> the
>> > referenced wb will open automatically.
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>> > "scott" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> >> I re-wrote my code into several subs listed below. I'd really like to
>> > learn
>> >> the method of adding the addin to the references. Do you have any code
> to
>> >> accomplish this?
>> >>
>> >> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If I
>> >> call
>> >> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even
>> >> allow
>> >> my
>> >> code in CODE #2 to test if the add-in is loaded.
>> >>
>> >> Basically, I'm striking out no matter what I try. I know there must be
> a
>> > way
>> >> to accomplish this feat.
>> >>
>> >> Note: I did manage to properly name my project "test_AddIn" instead of
>> >> the
>> >> default "VBAProject".
>> >>
>> >>
>> >> CODE 1: *************************
>> >>
>> >> Sub loadAddIn()
>> >>
>> >> ThisWorkbook.VBProject.References.AddFromFile _
>> >> "C:\data\test_addin.xla"
>> >> End Sub
>> >>
>> >> ERROR 1: *************************
>> >>
>> >> Run-time error '1004':
>> >> Method 'VBProject' of object '_Workbook' failed
>> >>
>> >>
>> >> CODE 2: *************************
>> >>
>> >>
>> >> Sub TestAddIn()
>> >>
>> >> Dim tempStr As String
>> >> tempStr = "C:\data\test_addin.xla"
>> >> If Dir(tempStr) = "" Then
>> >> MsgBox "you do not have test_addin.xla installed"
>> >> End
>> >> End If
>> >>
>> >> If AddIns("test_AddIn").Installed = False _
>> >> Then AddIns("test_AddIn").Installed = True
>> >>
>> >> Dim bFound As Boolean
>> >> Dim obj
>> >> 'see if there is a reference already
>> >> For Each obj In ThisWorkbook.VBProject.References
>> >> MsgBox "name: " & UCase(obj.Name)
>> >> If UCase(obj.Name) = "test_addin.xla" Then
>> >> bFound = True
>> >> Exit For
>> >> End If
>> >> Next obj
>> >>
>> >> 'if no reference then set a reference.
>> >> If bFound = False Then _
>> >> ThisWorkbook.VBProject.References.AddFromFile _
>> >> "C:\data\test_addin.xla"
>> >> End Sub
>> >>
>> >>
>> >> ERROR 2: *************************
>> >>
>> >> Run-time error '9':
>> >> Subscript out of range
>> >>
>> >>
>> >>
>> >>
>> >> "Peter T" <peter_t@discussions> wrote in message
>> >> news:%(E-Mail Removed)...
>> >> > Looks like you don't particularly need to add the addin to the
> addin's
>> >> > collection so just open and close it like a normal workbook, eg
>> >> >
>> >> > Sub OpenCloseAddin(bOpen as boolean)
>> >> > dim sFile as string
>> >> > dim wb as workbook
>> >> >
>> >> > sFile = "C:\data\test_addin.xla"
>> >> > On error resume next
>> >> > set wb = application.workboooks(sFile)
>> >> > On error goto errH
>> >> > if bOpen then
>> >> > if wb is nothing then
>> >> > application.workbooks.open sFile
>> >> > end if
>> >> > elseif not wb is nothing then
>> >> > wb.close
>> >> > end if
>> >> > exit sub
>> >> > errH:
>> >> > if bOpen then
>> >> > msgbox "Problem opening " & sFile
>> >> > end if
>> >> > end sub
>> >> >
>> >> > warning - aircode !
>> >> >
>> >> > Regards,
>> >> > Peter T
>> >> >
>> >> > "scott" <(E-Mail Removed)> wrote in message
>> >> > news:#(E-Mail Removed)...
>> >> >> I'm trying to load an add-in when my workbook opens. First I
>> >> >> created
>> >> >> an
>> >> >> add-in at "C:\data\test_addin.xla" that contains the code found
> below
>> > in
>> >> >> "CODE 2" section. Next, I added code found below in "CODE 1"
>> >> >> section
>> >> >> to
>> >> > the
>> >> >> file "C:\data\test.xls". While the add-in was open, in the Module
>> >> > property,
>> >> >> I named the module "test_AddIn".
>> >> >>
>> >> >> If I open "C:\data\test.xls" containing "CODE 1", I get a
> "Subscript
>> > out
>> >> > of
>> >> >> range" error and trace the error to the line
>> >> >>
>> >> >> If AddIns("test_AddIn").Installed = False
>> >> >>
>> >> >> in "CODE 1". To further troubleshoot, I manually added the add-in
>> > within
>> >> >> Excel's References and noticed the name of my add-in within
> References
>> > is
>> >> >> called "VBAProject".
>> >> >>
>> >> >> I asume, the error is being caused because of the "VBAProject" name
> of
>> > my
>> >> >> add-in.
>> >> >>
>> >> >> QUESTIONS:
>> >> >>
>> >> >> 1. How can I change the "VBAProject" name to "test_AddIn".
>> >> >> 2. Will changing the name as described in QUESTION #1 allow my
> add-in
>> > to
>> >> >> load when the "C:\data\test.xls" workbook opens?
>> >> >> 3. If I'm going about this wrong, can someone shed some light on
>> >> >> how
>> >> >> to
>> >> >> accomplish this task?
>> >> >>
>> >> >>
>> >> >>
>> >> >> ' CODE 1: ****************************
>> >> >>
>> >> >> Sub Auto_Open()
>> >> >>
>> >> >> Dim tempStr As String
>> >> >> tempStr = "C:\data\test_addin.xla"
>> >> >> If Dir(tempStr) = "" Then
>> >> >> MsgBox "You do not have the Test Add-In installed."
>> >> >> End
>> >> >> End If
>> >> >>
>> >> >> If AddIns("test_AddIn").Installed = False _
>> >> >> Then AddIns("test_AddIn").Installed = True
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >> Sub Auto_Close()
>> >> >>
>> >> >> If AddIns("test_AddIn").Installed = True _
>> >> >> Then AddIns("test_AddIn").Installed = False
>> >> >>
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >> ' CODE 2: ****************************
>> >> >>
>> >> >> Sub Auto_Open()
>> >> >> MsgBox "Add-In Loaded"
>> >> >> End Sub
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Your code looks fine to add a project reference, in so doing will 'by
default' load the file if not already loaded. I don't see anything in what
you posted that will unload the addin.

The "addins" collection is in effect all the addins in the "Addins Manager"
as listed in Tools > Addins, irrespective as to whether the addin is loaded
(installed). The collection may also include some addins that do are not
visible in the list if they are not installed and not in one of the default
addin's paths.

Sub ListAddins()
Dim i As Long
Dim adn As AddIn

Set adns = Application.AddIns

For Each adn In Application.AddIns
i = i + 1
With adn
Cells(i, 1) = .Installed ' ie ticked in Tools > Addins & loaded
Cells(i, 2) = .Title ' File > Properties > Title
Cells(i, 3) = .Name

s = .Path & "\"
If Len(s) <= 1 Then s = "MS built-in addin"
Cells(i, 4) = s
End With
Next
Columns("B:C").EntireColumn.AutoFit
End Sub

As I've tried to explain, if you want to do .Installed = True, first you
need to verify the addin exists in the addins collection and if not '.Add'
it to the collection.

For your purposes I assume it's not necessary for your addin to exist in the
addins collection as you are loading/unloading it by other means.

I also assume the reason you want to add a project reference to the addin to
your main wb, is because vba-code in your main wb directly calls code in the
addin virtually as if the addin's code exists in the main-wb (that's the
only reason to add the ref as you are doing).

Regards,
Peter T

"scott" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Finally got it. I had to re-write it a little, but this will load/unload
and
> test properly.
>
> One final question:
>
> Please explain what you meant by "addin actually in the addins collection,
> your code does not check for that. It needs to be before you can set the
> Installed property = true"
>
> I don't exactly understand what the "addins" collection really means. And
> BTW, thank you for your input on this issue.
>
> CODE: ***********************************
>
> Sub loadAddIn()
>
> Dim bFound As Boolean
>
> On Error Resume Next
>
> For Each Ref In ThisWorkbook.VBProject.References
> i = i + 1
> If Ref.Name = "test_AddIn" Then
> MsgBox "Test Add-In installed."
> bFound = True
> Exit For
> End If
>
> Next Ref
>
> If bFound = False Then _
> ThisWorkbook.VBProject.References.AddFromFile _
> "C:\data\test_addin.xla"
>
> On Error GoTo 0
>
> End Sub
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > Is the addin actually in the addins collection, your code does not check
> > for
> > that. It needs to be before you can set the Installed property = true.
> > Refer
> > to the notes I posted previously.
> >
> >> Why do you think excel is preventing me to simply test if a add-in
> > reference
> >> exists?
> >
> > What kind of reference are you referring to.
> >
> > Where is your error generated.
> >
> > Regards,
> > Peter T
> >
> > "scott" <(E-Mail Removed)> wrote in message
> > news:e9$(E-Mail Removed)...
> >> I did manage to load the add-in into referencds after checking the
option
> >> under Tools|Macro that gives access to vba projects. However, no matter
> >> if
> >> the add-in is loaded or not, I can't test it with vba. The code in
CODE
> >> 2
> >> below continues to give the subscript out of range error.
> >>
> >> Why do you think excel is preventing me to simply test if a add-in
> > reference
> >> exists?
> >>
> >> CODE 1: *************************
> >>
> >> Sub loadAddIn()
> >>
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "c:\data\test_addin.xla"
> >>
> >> End Sub
> >>
> >>
> >>
> >> CODE 2: *************************
> >>
> >>
> >> Sub TestAddIn()
> >>
> >> Dim tempStr As String
> >> tempStr = "C:\data\test_addin.xla"
> >> If Dir(tempStr) = "" Then
> >> MsgBox "you do not have test_addin.xla installed"
> >> End
> >> End If
> >>
> >> If AddIns("test_AddIn").Installed = False _
> >> Then AddIns("test_AddIn").Installed = True
> >>
> >> Dim bFound As Boolean
> >> Dim obj
> >> 'see if there is a reference already
> >> For Each obj In ThisWorkbook.VBProject.References
> >> MsgBox "name: " & UCase(obj.Name)
> >> If UCase(obj.Name) = "test_addin.xla" Then
> >> bFound = True
> >> Exit For
> >> End If
> >> Next obj
> >>
> >> 'if no reference then set a reference.
> >> If bFound = False Then _
> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> "C:\data\test_addin.xla"
> >> End Sub
> >>
> >>
> >> ERROR 2: *************************
> >>
> >> Run-time error '9':
> >> Subscript out of range
> >>
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:(E-Mail Removed)...
> >> > You're loosing me! Is this thread about loading an addin, or adding a
> >> > references.
> >> >
> >> > Anyway -
> >> >
> >> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> >> "C:\data\test_addin.xla"
> >> >
> >> > The above looks fine, assuming the ref does not already exist and a
> > valid
> >> > project in the path.
> >> >
> >> >> Dim tempStr As String
> >> >> tempStr = "C:\data\test_addin.xla"
> >> >> If Dir(tempStr) = "" Then
> >> >> MsgBox "you do not have test_addin.xla installed"
> >> >> End
> >> >> End If
> >> >
> >> > The message implies the addin is not in the addins collection rather
> > than
> >> > not found in the specified folder.
> >> >
> >> > Avoid the "End" statement. If necessary release any object variables
> > and
> >> > use Exit Sub or Function, possibly all the way back up to the
original
> >> > calling routine.
> >> >
> >> >
> >> >> If AddIns("test_AddIn").Installed =
> >> >
> >> > Before the above, unless certain the addin exists in the addins
> > collection
> >> > do -
> >> >
> >> > On error resume next
> >> > Set adn = addins(the-Addin's-Title)
> >> > If adn is nothing then
> >> > Set adn = addins.add(C:\data\test_addin.xla")
> >> > End if
> >> >
> >> > If not adn.installed then adn.installed = True
> >> >
> >> >
> >> > Normally once the reference has been added to a wb, when the wb opens
> > the
> >> > referenced wb will open automatically.
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> >
> >> > "scott" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> >> I re-wrote my code into several subs listed below. I'd really like
to
> >> > learn
> >> >> the method of adding the addin to the references. Do you have any
code
> > to
> >> >> accomplish this?
> >> >>
> >> >> I tried the sub loadAddIn() below IN CODE #1 and I get ERROR #1. If
I
> >> >> call
> >> >> the sub, TestAddIn() in CODE #2, I get ERROR #2. Excel won't even
> >> >> allow
> >> >> my
> >> >> code in CODE #2 to test if the add-in is loaded.
> >> >>
> >> >> Basically, I'm striking out no matter what I try. I know there must
be
> > a
> >> > way
> >> >> to accomplish this feat.
> >> >>
> >> >> Note: I did manage to properly name my project "test_AddIn" instead
of
> >> >> the
> >> >> default "VBAProject".
> >> >>
> >> >>
> >> >> CODE 1: *************************
> >> >>
> >> >> Sub loadAddIn()
> >> >>
> >> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> >> "C:\data\test_addin.xla"
> >> >> End Sub
> >> >>
> >> >> ERROR 1: *************************
> >> >>
> >> >> Run-time error '1004':
> >> >> Method 'VBProject' of object '_Workbook' failed
> >> >>
> >> >>
> >> >> CODE 2: *************************
> >> >>
> >> >>
> >> >> Sub TestAddIn()
> >> >>
> >> >> Dim tempStr As String
> >> >> tempStr = "C:\data\test_addin.xla"
> >> >> If Dir(tempStr) = "" Then
> >> >> MsgBox "you do not have test_addin.xla installed"
> >> >> End
> >> >> End If
> >> >>
> >> >> If AddIns("test_AddIn").Installed = False _
> >> >> Then AddIns("test_AddIn").Installed = True
> >> >>
> >> >> Dim bFound As Boolean
> >> >> Dim obj
> >> >> 'see if there is a reference already
> >> >> For Each obj In ThisWorkbook.VBProject.References
> >> >> MsgBox "name: " & UCase(obj.Name)
> >> >> If UCase(obj.Name) = "test_addin.xla" Then
> >> >> bFound = True
> >> >> Exit For
> >> >> End If
> >> >> Next obj
> >> >>
> >> >> 'if no reference then set a reference.
> >> >> If bFound = False Then _
> >> >> ThisWorkbook.VBProject.References.AddFromFile _
> >> >> "C:\data\test_addin.xla"
> >> >> End Sub
> >> >>
> >> >>
> >> >> ERROR 2: *************************
> >> >>
> >> >> Run-time error '9':
> >> >> Subscript out of range
> >> >>
> >> >>
> >> >>
> >> >>
> >> >> "Peter T" <peter_t@discussions> wrote in message
> >> >> news:%(E-Mail Removed)...
> >> >> > Looks like you don't particularly need to add the addin to the
> > addin's
> >> >> > collection so just open and close it like a normal workbook, eg
> >> >> >
> >> >> > Sub OpenCloseAddin(bOpen as boolean)
> >> >> > dim sFile as string
> >> >> > dim wb as workbook
> >> >> >
> >> >> > sFile = "C:\data\test_addin.xla"
> >> >> > On error resume next
> >> >> > set wb = application.workboooks(sFile)
> >> >> > On error goto errH
> >> >> > if bOpen then
> >> >> > if wb is nothing then
> >> >> > application.workbooks.open sFile
> >> >> > end if
> >> >> > elseif not wb is nothing then
> >> >> > wb.close
> >> >> > end if
> >> >> > exit sub
> >> >> > errH:
> >> >> > if bOpen then
> >> >> > msgbox "Problem opening " & sFile
> >> >> > end if
> >> >> > end sub
> >> >> >
> >> >> > warning - aircode !
> >> >> >
> >> >> > Regards,
> >> >> > Peter T
> >> >> >
> >> >> > "scott" <(E-Mail Removed)> wrote in message
> >> >> > news:#(E-Mail Removed)...
> >> >> >> I'm trying to load an add-in when my workbook opens. First I
> >> >> >> created
> >> >> >> an
> >> >> >> add-in at "C:\data\test_addin.xla" that contains the code found
> > below
> >> > in
> >> >> >> "CODE 2" section. Next, I added code found below in "CODE 1"
> >> >> >> section
> >> >> >> to
> >> >> > the
> >> >> >> file "C:\data\test.xls". While the add-in was open, in the
Module
> >> >> > property,
> >> >> >> I named the module "test_AddIn".
> >> >> >>
> >> >> >> If I open "C:\data\test.xls" containing "CODE 1", I get a
> > "Subscript
> >> > out
> >> >> > of
> >> >> >> range" error and trace the error to the line
> >> >> >>
> >> >> >> If AddIns("test_AddIn").Installed = False
> >> >> >>
> >> >> >> in "CODE 1". To further troubleshoot, I manually added the add-in
> >> > within
> >> >> >> Excel's References and noticed the name of my add-in within
> > References
> >> > is
> >> >> >> called "VBAProject".
> >> >> >>
> >> >> >> I asume, the error is being caused because of the "VBAProject"
name
> > of
> >> > my
> >> >> >> add-in.
> >> >> >>
> >> >> >> QUESTIONS:
> >> >> >>
> >> >> >> 1. How can I change the "VBAProject" name to "test_AddIn".
> >> >> >> 2. Will changing the name as described in QUESTION #1 allow my
> > add-in
> >> > to
> >> >> >> load when the "C:\data\test.xls" workbook opens?
> >> >> >> 3. If I'm going about this wrong, can someone shed some light on
> >> >> >> how
> >> >> >> to
> >> >> >> accomplish this task?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> ' CODE 1: ****************************
> >> >> >>
> >> >> >> Sub Auto_Open()
> >> >> >>
> >> >> >> Dim tempStr As String
> >> >> >> tempStr = "C:\data\test_addin.xla"
> >> >> >> If Dir(tempStr) = "" Then
> >> >> >> MsgBox "You do not have the Test Add-In installed."
> >> >> >> End
> >> >> >> End If
> >> >> >>
> >> >> >> If AddIns("test_AddIn").Installed = False _
> >> >> >> Then AddIns("test_AddIn").Installed = True
> >> >> >>
> >> >> >> End Sub
> >> >> >>
> >> >> >> Sub Auto_Close()
> >> >> >>
> >> >> >> If AddIns("test_AddIn").Installed = True _
> >> >> >> Then AddIns("test_AddIn").Installed = False
> >> >> >>
> >> >> >> End Sub
> >> >> >>
> >> >> >>
> >> >> >> ' CODE 2: ****************************
> >> >> >>
> >> >> >> Sub Auto_Open()
> >> >> >> MsgBox "Add-In Loaded"
> >> >> >> End Sub
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>