Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem sharing an excel workbook when my formulas reference user.
Hi, I have wrote several simplifying user defined formulas related to
structural engineering. I have put these formulas in an excel addin and have successfully used the formulas to create workbooks on my own computer. I wanted to distribute these workbooks to other engineers in my office so I sent them the addin file and the workbooks, and got them to install the addin file before opening the workbook. However upon opening the workbook the other engineers found that all of the userdefined formulas in the workbook reference the Microsoft Addin file on my own computer. Thus the only way for another user to use the workbook is to delete the references to my own computer, which is really anoying and negates the time savings that was gained by programming the formulas. I think that a solution would be to make Excel look in the addin file on the users computer for the formulas rather than trying to reference the addin file on my own computer. Does anyone know how to do this? Or any other solution?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
problem sharing an excel workbook when my formulas reference user.
You might want to consider publishing your AddIn on a shared file server, if
you have one, and write some VBA code to do the following on the Workbook_Open event: * Install your AddIn on a shared file server, where all users have read-only access, at least * Test for the presence of the AddIn file on the local machine * Test to see if the AddIn is installed * Test for the current AddIn version on the local machine, compared with that on the server (not sure how to do this now) * Proceed with whatever the workbook does with the AddIn Following is some code I threw together for this reply, which is based on the assumption that the 'master' AddIn file that all users reference being on the server <AddInPath: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim AddInName As String AddInName = "My AddIn" If AddIns(AddInName).Installed = True Then AddIns(AddInName).Installed = False End If End Sub Private Sub Workbook_Open() Dim AddInName As String Dim AddInExtention As String Dim AddInPath As String Dim i As Integer Dim IsAddInAvailable As Boolean AddInName = "My AddIn" AddInExtention = ".xla" AddInPath = "\\servername\path\" For i = 1 To AddIns.Count If AddIns(i).Name = AddInName & AddInExtention Then IsAddInAvailable = True Exit For End If Next i If IsAddInAvailable = True Then If AddIns(AddInName).Installed = False Then AddIns(AddInName).Installed = True End If Else AddIns.Add(AddInPath & AddInName & AddInExtention, False).Installed = True End If End Sub It's been a long time since I've dabbled with this concept, and don't recall all of the quirks. HTH, VBA Dabbler "Waiward Engineer" wrote: Hi, I have wrote several simplifying user defined formulas related to structural engineering. I have put these formulas in an excel addin and have successfully used the formulas to create workbooks on my own computer. I wanted to distribute these workbooks to other engineers in my office so I sent them the addin file and the workbooks, and got them to install the addin file before opening the workbook. However upon opening the workbook the other engineers found that all of the userdefined formulas in the workbook reference the Microsoft Addin file on my own computer. Thus the only way for another user to use the workbook is to delete the references to my own computer, which is really anoying and negates the time savings that was gained by programming the formulas. I think that a solution would be to make Excel look in the addin file on the users computer for the formulas rather than trying to reference the addin file on my own computer. Does anyone know how to do this? Or any other solution?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem removing sharing from workbook via Macro | Excel Discussion (Misc queries) | |||
How do I resolve a problem with sharing a workbook | Excel Worksheet Functions | |||
Sharing workbook, show last user change? | Excel Discussion (Misc queries) | |||
Problem Sharing Workbook | Excel Discussion (Misc queries) | |||
workbook and application reference set to null after the user enters something in an excel sheet | Excel Programming |