Refering to Excel Add-In Problem

[SUnderwood]

Dear All,

I have installed on a computer (called Compy) my excel add-in called PInfo.xla using the Tools menu.

I have written a sheet with a combo box on it which gets its data from a local range called MyDatabase, which in turn points to an Add-In range "=PInfo.xla!Database".

On my computer this works fine. But when I open the spreadsheet on Compy, the sheet tries to look in my profile (c:\...\sunderwood\applica\...) for the Add_In, which obviously will work for me but not for anyone else.

How can I stop this? How can I change it so that it looks for the Add-In which is already loaded?

 

[dan1967]

When you are ready to distribute your add-in, create a self-extracting zip file that will extract the add-in to whatever file location you want it to. That way, it will be the same on all machines.



Dan.

 

[combo]

As I understood, you need to refer to a range in the add-in. As the default path to add-ins depends on the user name, but the add-in can' be changed, I would think of an UDF in the add-in returning range:

[tt]Function DB_Range() As Range
Set DB_Range = ThisWorkbook.Worksheets(1).Range("A1:C3&quot
End Function[/tt]

Now you can use it in formulas like =SUM(DB_Range()) or INDEX(DB_Range(),1,1)

combo

 

[SUnderwood]

Dan,

Doh! I had tried this method but excel was refusing to look outside "My" add-in folder. However, with persistence it finally worked. The procedure:

1) Load excel with blank workbook. Uninstall Add-In and close excel.
2) Load excel, open the add-ins folder with Tool | Add-In | Browse and delete the Add-In. Try to add the add-in again and say yes to the message. Close excel
3) Open it again. Open you .xls sheet and save it as a .xla in the location of choice - i.e. c:\prog file\here_thee_be\under_this_tree.xla. Close excel
4) Open excel and go to add-in form. Browse you your new add-in and add it. Close excel
5) Open excel and the add-in should be loaded! Now I customised my .xls's so they used the add-in at this location.

Incidentally, this also works over a network. AND multiple users can access the add-in stored on the network, so keeping all files centralised. Excellent for easy update of company-wide code.


COMBO

This idea works great when for my functions on the worksheets. I found, however, that it couldn’t be used in a control (ie textbox). Also setting a range to point to the function proved fruitless.

You’ve both been a great help.

Many thanks

Sean

 

[dan1967]

glad to have been help.




Dan.