Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 27th 09, 02:47 AM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 37
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   Report Post  
Old August 27th 09, 03:01 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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   Report Post  
Old August 27th 09, 03:42 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 37
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   Report Post  
Old August 27th 09, 05:40 PM posted to microsoft.public.excel.links
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 473
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
UPDATE LINKS AUTOMATICALLY Navya Excel Discussion (Misc queries) 0 June 24th 09 04:14 PM
Automatically update links when server names change J Hotch Excel Worksheet Functions 1 August 3rd 05 03:50 PM
automatically update links without opening file sir Lancelot Excel Worksheet Functions 0 July 29th 05 04:05 AM
Links update automatically Lp12 Excel Discussion (Misc queries) 0 July 14th 05 11:30 AM
How do I automatically update chart source data links for moved fi Suzuki7 Charts and Charting in Excel 2 February 15th 05 03:55 AM


All times are GMT +1. The time now is 05:54 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017