Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
I wrote a library of utility functions in an XLA that have been in a
particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
Just for the future...
I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
The same drive letter is what IS wants to avoid, as they expect Corporate to
map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
I think it was in the .private newsgroups. (I don't recall any suggested code,
but you could contact Tushar to verify.) But it would use an application event that just looks for any workbook that's being opened: Chip Pearson has some notes at: http://www.cpearson.com/excel/AppEvent.htm A short sample (which goes under thisWorkbook): Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "Hey you created a workbook named: " & Wb.Name End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Hey you opened a workbook named: " & Wb.Name End Sub Jerry W. Lewis wrote: The same drive letter is what IS wants to avoid, as they expect Corporate to map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
I haven't seen Tushar's suggestion but I imagine it does pretty much as Dave
described, and makes sense. Dave did say it was for future reference, rather than a fix for your current problem. However you could similar in your new addin In a withevents as application class in your addin look at each workbook as it opens for links similar to your addin name, if any exist update them. The downside is a potential delay as each wb opens, but typically not much. Perhaps include a user option to turn automatic update on/off. Regards, Peter T in a class module in your addin Public WithEvents xlApp As Excel.Application Private Sub xlApp_WorkbookOpen(ByVal Wb As Excel.Workbook) Dim vLink vLink = Wb.LinkSources(xlLinkTypeExcelLinks) If Not IsEmpty(vLink) Then Erase vLink For Each vLink In Wb.LinkSources(xlLinkTypeExcelLinks) If vLink Like "PartofMyAddinName" Then rest of code change link, check it's not exactly MyAddinName wb.ChangeLink vLink, MyAddinName, xlLinkTypeExcelLinks etc End Sub In your addin's open event set clApp = new ClassName set clApp.xlApp = application in a normal module Public clApp as ClassName "Jerry W. Lewis" wrote in message ... The same drive letter is what IS wants to avoid, as they expect Corporate to map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
I think I have the concept now, thanks. It looks do-able in principle, but
would require a considerable validation effort. On problem that immediately surfaced is that Excel won't let you change that link on a protected sheet. This hole keeps getting deeper and deeper ... Jerry "Dave Peterson" wrote: I think it was in the .private newsgroups. (I don't recall any suggested code, but you could contact Tushar to verify.) But it would use an application event that just looks for any workbook that's being opened: Chip Pearson has some notes at: http://www.cpearson.com/excel/AppEvent.htm A short sample (which goes under thisWorkbook): Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "Hey you created a workbook named: " & Wb.Name End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Hey you opened a workbook named: " & Wb.Name End Sub Jerry W. Lewis wrote: The same drive letter is what IS wants to avoid, as they expect Corporate to map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
On problem that immediately
surfaced is that Excel won't let you change that link on a protected sheet. I find I can't update links if any sheet in the workbook is protected, even one that does not contain any links. Regards, Peter T "Jerry W. Lewis" wrote in message ... I think I have the concept now, thanks. It looks do-able in principle, but would require a considerable validation effort. On problem that immediately surfaced is that Excel won't let you change that link on a protected sheet. This hole keeps getting deeper and deeper ... Jerry "Dave Peterson" wrote: I think it was in the .private newsgroups. (I don't recall any suggested code, but you could contact Tushar to verify.) But it would use an application event that just looks for any workbook that's being opened: Chip Pearson has some notes at: http://www.cpearson.com/excel/AppEvent.htm A short sample (which goes under thisWorkbook): Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "Hey you created a workbook named: " & Wb.Name End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Hey you opened a workbook named: " & Wb.Name End Sub Jerry W. Lewis wrote: The same drive letter is what IS wants to avoid, as they expect Corporate to map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Minimize pain from relocating an XLA function library
Maybe it could become an instruction email that you could mail out.
Sometimes relying on the user isn't really too bad. (And just include your work phone number, home phone number, cell phone number, fax number...<bg). Jerry W. Lewis wrote: I think I have the concept now, thanks. It looks do-able in principle, but would require a considerable validation effort. On problem that immediately surfaced is that Excel won't let you change that link on a protected sheet. This hole keeps getting deeper and deeper ... Jerry "Dave Peterson" wrote: I think it was in the .private newsgroups. (I don't recall any suggested code, but you could contact Tushar to verify.) But it would use an application event that just looks for any workbook that's being opened: Chip Pearson has some notes at: http://www.cpearson.com/excel/AppEvent.htm A short sample (which goes under thisWorkbook): Option Explicit Public WithEvents xlApp As Excel.Application Private Sub Workbook_Open() Set xlApp = Application End Sub Private Sub Workbook_Close() Set xlApp = Nothing End Sub Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook) MsgBox "Hey you created a workbook named: " & Wb.Name End Sub Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook) MsgBox "Hey you opened a workbook named: " & Wb.Name End Sub Jerry W. Lewis wrote: The same drive letter is what IS wants to avoid, as they expect Corporate to map that drive letter to a remote server whose contents we do not control. I'm not sure that I understand what you believe to be Tushar's suggestion. The xla would open when Excel is started; the concept of an event that would start then but remain active to monitor all future file opens is foreign to me -- can you elaborate? I found nothing when I searched for anything by Tushar in microsoft.public.excel.* that contains the words "xla", "event", and either "link" or "links". Jerry "Dave Peterson" wrote: Just for the future... I think it was Tushar Mehta who suggested that when the .xla file opens, it creates an application event that looks for workbooks opening. Then it can try to change the links itself. (Not a pretty solution.) Any chance that the network location was a mapped drive? Maybe just using the same drive letter (and path) would be sufficient. (Yeah, you already thought of that, but it's the only thing I could think of.) Jerry W. Lewis wrote: I wrote a library of utility functions in an XLA that have been in a particular network location for about nine years. IS now wants to move the XLA to a different location, which will of course break all existing uses, since Excel embeds the path when a workbook is saved, instead of checking for the location from the registry's open add-in list (as it would with an XLL). I have no clue how many users and workbooks will be impacted (probably hundreds of users and thousands of workbooks). I can change the links on my workbooks on a case-by-case basis, but this approach would be problematic for less experienced users. Any suggestions? Jerry -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
goal seek minimize function | Excel Worksheet Functions | |||
library circulation function | New Users to Excel | |||
How can i create new function library ??? | Excel Worksheet Functions | |||
how do i minimize/maximize a workbook from vba? I want to minimize it durring processing to speed things up a bit | Excel Worksheet Functions | |||
Function Library Creation | Excel Programming |