Unable to set Installed Property of AddIn

[Mike H.]

I get this message so I just went to doing this when I load a file that I
wish to have the add-in loaded for:

Workbooks.Open Filename:="P:\Library\test\test.xla"

But now I get a run-time 1004 error: Method 'Open' of object 'Workbooks'
failed. I can't seem to get the addin to function exactly right. I am using
XL 2003 version ,b tw! Anyone have suggestions. I get error messages
sometimes when I load the added using the addin "Tools>Addins". Ideas what I
am doing wrong? Also, I wish the addin to load one any a dozen files is
loaded , but I wish leave the addin loaded as long as one of these files
remains active. ANy ideas on this?

 

[Peter T]

Sounds like the path and/or filename is wrong. Load the addin some other way
(double click, drag into Excel etc). Select it in the VBE's project
explorer, in the Immediate window (ctrl-g)

?thisworkbook.fullname

Afraid I could make sense of the rest of your post.

Regards,
Peter T

 

[Peter T]

Oh dear

Afraid I could make sense of the rest of your post.

should have read

Afraid I couldn't make sense of the rest of your post.

Peter T

 

[Mike H.]

Oops Too!

My last sentence:

Also, I wish the addin to load when any of a dozen files are loaded , but I
wish to leave the addin loaded as long as any one of these files remains
active. Any ideas on this?

In other words, if the user loads file1.xls then the add-in loads. Then the
user loads file2.xls and then the user unloads file1.xls. At this point, I
wish the addin to remain loaded. There is a list of files that would all
load the addin but only when the last of those specific files is unloaded
would I want to unload the addin. Ideas?

 

[Peter T]

Here's one approach, untested so double check method & typos etc

On a sheet in the addin, name a range and type your file names.

' In a normal module

Function KeepMeOpen() As Boolean
Dim b As Boolean
Dim wb As Workbook
Dim vArr

' "Files" is an named range of cells in a column in this addin
' that contain file names

vArr = Range("Files")

For Each wb In Workbooks
For i = 1 To UBound(vArr)
If UCase(wb.Name) = UCase(vArr(i, 1)) Then
b = True
Exit For
End If
Next
If b Then Exit For
Next
KeepMeOpen = b

End Function

'' in ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = KeepMeOpen
End Sub


One advantage of this approach is the Named range can be writtien or
modified programatically as well as new files added / deleted to the named
range in the addin. The code to do this can be in some other workbook. The
code would also need to save the addin. IOW, updateable without needing to
redistribute an amended addin or programatically write to the code-project.

Alternatively, if all your filenames have say the first part as something
unique, even simpler. No need to bother with the named range of file names,
just compare the unique part with open file names.

In other files, in their close event's close the addin
Workbooks("myAddinName.xla").Close

This should trigger the BeforeClose event in the addin, if "Canel" returns
True close will abort.

Like I say, totally untested so do report back.

Regards,
Peter T

PS, was it a file name error