easy way to update all links
easy way to update all links
(OP)
I would like to create a macro which updates all links in the current spreadsheet. (i.e. formula's calculated based on value in the other spreadsheet).
I realize this can easily be done by
1 - closing and reopening the files.
OR
2 - Edit / Links / Use mouse to select All / Update now.
But this spreadsheet is being used by others and I would like to make it extremely simple (alt-u for update). Any way to automate #2 into a macro... I see difficulty because there is no way to select all using keys... requires mouse moves which don't do well in macros.
I realize this can easily be done by
1 - closing and reopening the files.
OR
2 - Edit / Links / Use mouse to select All / Update now.
But this spreadsheet is being used by others and I would like to make it extremely simple (alt-u for update). Any way to automate #2 into a macro... I see difficulty because there is no way to select all using keys... requires mouse moves which don't do well in macros.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.





RE: easy way to update all links
CODE
ActiveWorkbook.UpdateLink Name:= "C:\global22.xls", Type:=xlExcelLinks
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: easy way to update all links
Unfortunately the nature of the spreadsheet is that I will add linked info from new files periodically.
For now I will plan on updating my macro every time I add a new spreadsheet (at least it's easy on the users). Is there a way to do it which simply updates all links regardless of filename?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: easy way to update all links
"Automatically update links to other programs when performing calculations
1 On the Edit menu, click Links.
2 Next to Update, click Automatic.
3 Click OK.
4 On the Tools menu, click Options, and then click the Calculation tab.
5 Select the Update remote references check box."
In spite of setting it up this way, I don't get any automatic update even when I press F9. Just what is so automatic about it? (The only way I can get an update is manually or with macro.)
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: easy way to update all links
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: easy way to update all links
can't check this at the moment, but perhaps what is shown in the help works only when you use the Recalculate button in the Options Calculation window.
What I am sure of (at least in my setup of Excel...) is that it works with the (Application.ActiveSheet.)Calculate function in VB, that you obtain also by recording a macro where you go to that button under Options Calculate and press it.
If you can go that further, you'll be able to connect the F9 key to a macro or VB code, so that everything happens in the dark...
prex
http://www.xcalcs.com
Online tools for structural design
RE: easy way to update all links
As far as I know the Recalculate option (along with F9 etc) will only recalculate on open spreadsheets, and won't pick up changes in linked spreadsheets that are not currently open.
The UpdateLink code shown above will pick up changes in linked spreadsheets without them being open, which I understand is what OP was looking for.
Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting
UK steam enthusiasts: www.essexsteam.co.uk
RE: easy way to update all links
However, according to the help file, the following method should update all links:
CODE
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Application.DisplayAlerts = True
In my Excel setup this doesn't work, though: a run time error 1004 is generated. Perhaps electricpete will spend a little more time on it to find the right way.
prex
http://www.xcalcs.com
Online tools for structural design
RE: easy way to update all links
Probably it doesn't work just because the linked file is not open...
I give up
prex
http://www.xcalcs.com
Online tools for structural design
RE: easy way to update all links
Sub OpenCloseLinks()
Dim myLinks As Variant, i As Integer
myLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(myLinks) Then
On Error Resume Next
For i = LBound(myLinks) To UBound(myLinks)
Workbooks.Open myLinks(i)
If Err <> 0 Then
Err.Clear
Else
ActiveWorkbook.Close False
End If
'ActiveWorkbook.UpdateLink Name:=myLinks(i), Type:=xlExcelLinks
Next i
End If
End Sub