A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

automatically update links to add-ins



 
 
Thread Tools Display Modes
  #1  
Old August 27th, 2009, 02:47 AM posted to microsoft.public.excel.links
bigjimmer
external usenet poster
 
Posts: 5
Default automatically update links to add-ins

When I copy an Excel file from my C: drive to a LAN drive, any formulas that
use functions defined in an add-in are changed so that the add-in functions
reference the new location of the Excel file, instead of the path for the
loaded add-in.

Is there any way to automatically update these links, without the user being
prompted to update linked data? Setting Application.AskToUpdateLinks = False
will prevent the prompt, but then it returns a different dialog box that the
file cannot be found.

I know that once the file is opened that I can manually go to ToolsLink to
loaded add-ins, and this will relink all of my formulas at one time.
However, I haven't found how (or if) I can utilize this from VBA.

Thanks.
  #2  
Old August 27th, 2009, 03:01 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default automatically update links to add-ins

I am not familiar with the "Link to loaded add-ins" option.
Which version of Excel are you using?

If I were faced with this I guess I would do something like the
following (untested) macro

Sub FixUpAddInLinks()
Dim vLinks
Dim iLink As Integer
Dim stSourceName As String
Dim stFileName As String
vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinks) Then
For iLink = LBound(vLinks) To UBound(vLinks)
stSourceName = LCase(vLinks(iLink))
If Right(stSourceName,4)=".xla" or Right(stSourceName,5)=".xlam"
Then
stFileName = Mid(stSourceName,InStrRev(stSourceName,"\")+1)
' we have a link to an add-in
If IsIn(Workbooks, stFileName) Then
' an add-in with that name is present
If LCase(Workbooks(stFileName).FullName)stSourceNam e Then
' but it's from a different place
With ActiveWorkbook
.ChangeLink stSourceName, Workbooks(stFileName).FullName
End With
End If
End If
End If
Next
End If
End Sub

Function IsIn(oCollection As Object, stName As String)
Dim O As Object
On Error GoTo NotIn
Set O = oCollection(stName)
IsIn = True
NotIn:
End Function


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

  #3  
Old August 27th, 2009, 03:42 PM posted to microsoft.public.excel.links
bigjimmer
external usenet poster
 
Posts: 5
Default automatically update links to add-ins

I am (unfortunately) using Excel 2000.

I have code very similar to what you provided (I had found it from a
newsgroup post from 2006) that I had already modified that basically does
what you suggest.

The only thing I didn't like, which is why I asked about this, is that the
user is prompted about updating the links before the logic is run (even when
in the Workbook_Open event).

The problem is that the users I deal with sometimes do things thaey
shouldn't, and so could have unintentionally added links to other workbooks
that they need to fix. I typically instruct them that if they ever get the
"update links" question in a file from me, that they have likely done
something wrong to the workbook, and need to fix the links, which I also tell
them how to do. Since this method still produces that messsage, and doesn't
mean that there are any links that still require review, I was hoping to
avoid the prompt.

I will look further now into adding another layer to my code to determine if
there are any other linked files, other than the add-ins, and provide an
addirtional prompt to the user that there are still links that they need to
review.

Thanks for your prompt reply. I aslo would like to say thank you very much
for Find Links. I have used that for a while now, and it is an extremely
helpful application.

"Bill Manville" wrote:

I am not familiar with the "Link to loaded add-ins" option.
Which version of Excel are you using?

If I were faced with this I guess I would do something like the
following (untested) macro

Sub FixUpAddInLinks()
Dim vLinks
Dim iLink As Integer
Dim stSourceName As String
Dim stFileName As String
vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinks) Then
For iLink = LBound(vLinks) To UBound(vLinks)
stSourceName = LCase(vLinks(iLink))
If Right(stSourceName,4)=".xla" or Right(stSourceName,5)=".xlam"
Then
stFileName = Mid(stSourceName,InStrRev(stSourceName,"\")+1)
' we have a link to an add-in
If IsIn(Workbooks, stFileName) Then
' an add-in with that name is present
If LCase(Workbooks(stFileName).FullName)stSourceNam e Then
' but it's from a different place
With ActiveWorkbook
.ChangeLink stSourceName, Workbooks(stFileName).FullName
End With
End If
End If
End If
Next
End If
End Sub

Function IsIn(oCollection As Object, stName As String)
Dim O As Object
On Error GoTo NotIn
Set O = oCollection(stName)
IsIn = True
NotIn:
End Function


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup


  #4  
Old August 27th, 2009, 05:40 PM posted to microsoft.public.excel.links
Bill Manville
external usenet poster
 
Posts: 258
Default automatically update links to add-ins

One way to get round the update links message is to have a 2nd simple
"loader" workbook which in its Auto_Open just does

' open the real workbook without updating links.
Workbooks.Open ThisWorkbook.Path & "\RealBook.xls", UpdateLinks:=0
' run the Auto_Open for the real workbook
ActiveWorkbook.RunAutoMacros xlAutoOpen
' close me
ThisWorkbook.Close False

The real workbook can do any link munging that may be needed in its
Auto_Open.

Excel 2002 introduced more options on how to handle the startup prompt
for links, as you probably know.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:50 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.