×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

easy way to update all links

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.

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

RE: easy way to update all links

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

CODE

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: www.essexsteam.co.uk

RE: easy way to update all links

(OP)
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.

RE: easy way to update all links

(OP)
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.

RE: easy way to update all links

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: www.essexsteam.co.uk

RE: easy way to update all links

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 (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

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: www.essexsteam.co.uk

RE: easy way to update all links

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:

CODE

  Application.DisplayAlerts = False
  ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
  Application.DisplayAlerts = True
The DisplayAlerts 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

RE: easy way to update all links

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

RE: easy way to update all links

'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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources