easy way to update all links 1

[electricpete]

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.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[johnwm]

If the location of the linked sheets doesn't change then record a macro as normal. I got:

ActiveWorkbook.UpdateLink Name:="C:\global1.xls", Type:=xlExcelLinks
    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:

http://www.essexsteam.co.uk

 

[electricpete]

Thanks - that's a step in the right direction.

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.

 

[electricpete]

I noticed the help includes this:

"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.

 

[johnwm]

I've searched again, but I can't find a Collection Object for Links - sorry

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

UK steam enthusiasts:

http://www.essexsteam.co.uk

 

[prex]

electricpete,
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 [tt](Application.ActiveSheet.)Calculate[/tt] 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

 

[johnwm]

prex
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:

http://www.essexsteam.co.uk

 

[prex]

Sorry for my mistake: in my Excel setup all linked files are open at calculation time.
However, according to the help file, the following method should update all links:

  Application.DisplayAlerts = False
  ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
  Application.DisplayAlerts = True

The [tt]DisplayAlerts[/tt] off and on is there to avoid a window message opening when the linked files are not up to date.
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

 

[prex]

Oops!
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

 

[motayyem]

'from

http://www.ozgrid.com/forum/printthread.php?t=19700

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