Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
I have an addin I created that has some user defined functions (udf). They
work great. My only problem is that I sent the addin and a spreadsheet that utilized the udf's in the addin to a friend. When he opened up the related spreadsheet, it gave errors and pointed to the location of the addin on my computer. Is there a way to eliminate the direct references so Excel will see the addin on his computer and use the functions in the local addin and not return an error? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
Your friend can use Edit, Links, Change Source to point to the location of
the add-in on his machine. -- Jim Rech Excel MVP "dave k" wrote in message ... |I have an addin I created that has some user defined functions (udf). They | work great. My only problem is that I sent the addin and a spreadsheet that | utilized the udf's in the addin to a friend. When he opened up the related | spreadsheet, it gave errors and pointed to the location of the addin on my | computer. Is there a way to eliminate the direct references so Excel will | see the addin on his computer and use the functions in the local addin and | not return an error? | | Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
Thanks! I should have thought of that. Is there a way to automate it or do
I have to tell anyone who I share the addin with that they may need to do this? Thanks again. Dave "Jim Rech" wrote: Your friend can use Edit, Links, Change Source to point to the location of the add-in on his machine. -- Jim Rech Excel MVP "dave k" wrote in message ... |I have an addin I created that has some user defined functions (udf). They | work great. My only problem is that I sent the addin and a spreadsheet that | utilized the udf's in the addin to a friend. When he opened up the related | spreadsheet, it gave errors and pointed to the location of the addin on my | computer. Is there a way to eliminate the direct references so Excel will | see the addin on his computer and use the functions in the local addin and | not return an error? | | Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
If you want to automate it, then you have to make the user save th addin in a particular directory (exception being: if it is not in th same directory as that of the file which uses it). Anothet solution o course will be to have your UDFs in the same file as the file in whic you are using them. This will save you the trouble of adding addins -- mangesh_yada ----------------------------------------------------------------------- mangesh_yadav's Profile: http://www.excelforum.com/member.php...fo&userid=1047 View this thread: http://www.excelforum.com/showthread.php?threadid=27727 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
Hi Dave,
Is there a way to automate it or do I have to tell anyone who I share the addin with that they may need to do this? Assuming the addin will be installed, it could contain a class module that hooks application events and detects when a workbook is opened. It could then check if the workbook contains any links to the addin, and if so change them to itself: In a standard module: 'Create an instance of our event-handling class Dim mclsAppEvents As CAppEvents Sub Auto_Open() Set mclsAppEvents = New CAppEvents End Sub 'A public function to test it with Public Function AddTwo(d1 As Double, d2 As Double) AddTwo = d1 + d2 End Function In a class module called CAppEvents: Dim WithEvents moXL As Application Private Sub Class_Initialize() Set moXL = Application End Sub Private Sub moXL_WorkbookOpen(ByVal Wb As Workbook) Dim vLink As Variant 'Scan through the Excel links in the workbook For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks) 'Does it link to this addin name? If InStr(1, vLink, "\" & ThisWorkbook.Name, vbTextCompare) 0 Then 'Yes, so update it to point to this file Wb.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks Exit For End If Next End Sub In this case, they'll still get an 'Update Links' prompt, but if they click 'Don't update', the code in the addin will update the links for them. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Addin reference
That's it! Thanks for the help.
Dave "Stephen Bullen" wrote: Hi Dave, Is there a way to automate it or do I have to tell anyone who I share the addin with that they may need to do this? Assuming the addin will be installed, it could contain a class module that hooks application events and detects when a workbook is opened. It could then check if the workbook contains any links to the addin, and if so change them to itself: In a standard module: 'Create an instance of our event-handling class Dim mclsAppEvents As CAppEvents Sub Auto_Open() Set mclsAppEvents = New CAppEvents End Sub 'A public function to test it with Public Function AddTwo(d1 As Double, d2 As Double) AddTwo = d1 + d2 End Function In a class module called CAppEvents: Dim WithEvents moXL As Application Private Sub Class_Initialize() Set moXL = Application End Sub Private Sub moXL_WorkbookOpen(ByVal Wb As Workbook) Dim vLink As Variant 'Scan through the Excel links in the workbook For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks) 'Does it link to this addin name? If InStr(1, vLink, "\" & ThisWorkbook.Name, vbTextCompare) 0 Then 'Yes, so update it to point to this file Wb.ChangeLink vLink, ThisWorkbook.FullName, xlLinkTypeExcelLinks Exit For End If Next End Sub In this case, they'll still get an 'Update Links' prompt, but if they click 'Don't update', the code in the addin will update the links for them. Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word with embedded Excel object which has reference to addin | Excel Discussion (Misc queries) | |||
Reference to sheet name in addIn | Excel Programming | |||
Addin / Library Reference question | Excel Programming | |||
Reference Addin Module from sheet code | Excel Programming | |||
Create button with addin/reference macro in another file | Excel Programming |