Update links in Excel using VBA

[ChrisP]

I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

 

[ChrisP]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]

I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

I'm sorry, I'm new to VBA... where would I put this? I have Opening and
closing VBA (to make sure the user selects to enable Macros). The opening
looks like this:

Private Sub Workbook_Open()

Sheets("Summary").Visible = xlSheetVisible
Sheets("Admin").Visible = xlSheetVisible
Sheets("Data Center").Visible = xlSheetVisible
Sheets("IMO").Visible = xlSheetVisible
Sheets("TBD").Visible = xlSheetVisible
Sheets("VRMS Rewrite").Visible = xlSheetVisible
Sheets("VRMS").Visible = xlSheetVisible
Sheets("Enable Macros").Visible = xlSheetHidden

End Sub

Where would I put your code? Thanks again! I really appreciate all the
assistance!!!

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

So that the links to them will update. The code doesn't update the links, per se. If you study the code you'll see there's nothing
that says 'update this link'. Instead, the code takes advantage of the fact that Excel automatically updates the links to any source
workbooks that you open.

--
Cheers
macropod
[MVP - Microsoft Word]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

Anywhere you like. For example:

Private Sub Workbook_Open()
Call UpdateSelectedLinks
Sheets("Summary").Visible = xlSheetVisible
....

--
Cheers
macropod
[MVP - Microsoft Word]

I'm sorry, I'm new to VBA... where would I put this? I have Opening and
closing VBA (to make sure the user selects to enable Macros). The opening
looks like this:

Private Sub Workbook_Open()

Sheets("Summary").Visible = xlSheetVisible
Sheets("Admin").Visible = xlSheetVisible
Sheets("Data Center").Visible = xlSheetVisible
Sheets("IMO").Visible = xlSheetVisible
Sheets("TBD").Visible = xlSheetVisible
Sheets("VRMS Rewrite").Visible = xlSheetVisible
Sheets("VRMS").Visible = xlSheetVisible
Sheets("Enable Macros").Visible = xlSheetHidden

End Sub

Where would I put your code? Thanks again! I really appreciate all the
assistance!!!

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

You are the best! Thank you so much! It works perfectly!!!

Hi Chris,

Anywhere you like. For example:

Private Sub Workbook_Open()
Call UpdateSelectedLinks
Sheets("Summary").Visible = xlSheetVisible
....

--
Cheers
macropod
[MVP - Microsoft Word]

I'm sorry, I'm new to VBA... where would I put this? I have Opening and
closing VBA (to make sure the user selects to enable Macros). The opening
looks like this:

Private Sub Workbook_Open()

Sheets("Summary").Visible = xlSheetVisible
Sheets("Admin").Visible = xlSheetVisible
Sheets("Data Center").Visible = xlSheetVisible
Sheets("IMO").Visible = xlSheetVisible
Sheets("TBD").Visible = xlSheetVisible
Sheets("VRMS Rewrite").Visible = xlSheetVisible
Sheets("VRMS").Visible = xlSheetVisible
Sheets("Enable Macros").Visible = xlSheetHidden

End Sub

Where would I put your code? Thanks again! I really appreciate all the
assistance!!!

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

Thanks so much... how would I write this if I have a souce document that is
password protected and I need to update the links (still only updating
certain links and not them all)?

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

When running this code in excel I am getting the following error:

Run-time error '1004':
Method 'UpdateLink' of object '_Workbook' failed

What's wrong? Here is the code I'm using:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

When I choose to debug the row that's hightlighted is "
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks"

HELP!!!

Thanks!

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

You probably got that message because you dismissed the password dialogue box for a protected workbook. In order to avoid the
password prompt for such a workbook, you could use something like:
If cLink = "Protected.xls" Then
Workbooks.Open aLinks(i), Password:="MyPassword", ReadOnly:=True
Workbooks(cLink).Close SaveChanges:=False
ElseIf cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i)
End If
In this case, I think you do need to open & close the workbook concerned, as I don't see a 'password' parameter for the 'UpdateLink'
method. Consequently, you need to include both the relevant workbook's name and the password in the code (which you'll want to
protect).
--
Cheers
macropod
[MVP - Microsoft Word]

When running this code in excel I am getting the following error:

Run-time error '1004':
Method 'UpdateLink' of object '_Workbook' failed

What's wrong? Here is the code I'm using:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

When I choose to debug the row that's hightlighted is "
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks"

HELP!!!

Thanks!

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]

Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

:

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[ChrisP]

Thanks! the only workbook that has a password is the MasterList which I don't
want to have the links updated. Would it also give me this error if a linked
spreadsheet wasn't calculated before it was closed?

Where would I put this statement in my code?

Thanks again... you've been a huge help!!!

Hi Chris,

You probably got that message because you dismissed the password dialogue box for a protected workbook. In order to avoid the
password prompt for such a workbook, you could use something like:
If cLink = "Protected.xls" Then
Workbooks.Open aLinks(i), Password:="MyPassword", ReadOnly:=True
Workbooks(cLink).Close SaveChanges:=False
ElseIf cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i)
End If
In this case, I think you do need to open & close the workbook concerned, as I don't see a 'password' parameter for the 'UpdateLink'
method. Consequently, you need to include both the relevant workbook's name and the password in the code (which you'll want to
protect).
--
Cheers
macropod
[MVP - Microsoft Word]

When running this code in excel I am getting the following error:

Run-time error '1004':
Method 'UpdateLink' of object '_Workbook' failed

What's wrong? Here is the code I'm using:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

When I choose to debug the row that's hightlighted is "
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks"

HELP!!!

Thanks!

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]


Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

:

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!

 

[macropod]

Hi Chris,

That code would replace the existing If .... End If code. However, since the only protected workbook is the one you don't want to
update the links to, then that's not going to help.

Are you sure you've typed your master workbook's name correctly? The only other thing I can think of right now that might have
generated an error is that one of the other linked workbooks was open (perhaps by someone else). Does this problem happen every
time?

--
Cheers
macropod
[MVP - Microsoft Word]

Thanks! the only workbook that has a password is the MasterList which I don't
want to have the links updated. Would it also give me this error if a linked
spreadsheet wasn't calculated before it was closed?

Where would I put this statement in my code?

Thanks again... you've been a huge help!!!

Hi Chris,

You probably got that message because you dismissed the password dialogue box for a protected workbook. In order to avoid the
password prompt for such a workbook, you could use something like:
If cLink = "Protected.xls" Then
Workbooks.Open aLinks(i), Password:="MyPassword", ReadOnly:=True
Workbooks(cLink).Close SaveChanges:=False
ElseIf cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i)
End If
In this case, I think you do need to open & close the workbook concerned, as I don't see a 'password' parameter for the
'UpdateLink'
method. Consequently, you need to include both the relevant workbook's name and the password in the code (which you'll want to
protect).
--
Cheers
macropod
[MVP - Microsoft Word]

When running this code in excel I am getting the following error:

Run-time error '1004':
Method 'UpdateLink' of object '_Workbook' failed

What's wrong? Here is the code I'm using:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "MasterList.xls" Then
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

When I choose to debug the row that's hightlighted is "
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks"

HELP!!!

Thanks!



:

Hi Chris,

Actually, on relection, changing these two lines:
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
to:
ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
should work just as well, if not better, as it avoids all the file opening/closing.

--
Cheers
macropod
[MVP - Microsoft Word]


Thank you so much!!! One question, if I'm telling excel to update all the
links but one, why do I need to open the other workbooks?

:

Hi Chris,

You can do all the temporary opening (then closing) with vba. For example:

Sub UpdateSelectedLinks()
Dim aLinks
Dim cLink As String
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
cLink = Split(aLinks(i), "\")(UBound(Split(aLinks(i), "\")))
If cLink <> "IgnoreIt.xls" Then
Workbooks.Open aLinks(i)
Workbooks(cLink).Close SaveChanges:=False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

where 'IgnoreIt.xls' is the name of the workbook you don't want to update the links to.

--
Cheers
macropod
[MVP - Microsoft Word]


Thanks for the suggestion but that's not in option in my case. I need to
write something in VBA that would update each source documents links - I've
already disabled the update links pop up from appearing and have it set to
not update. Any other suggestions?

:

Hi Chris,

If you disable the links, then temporarily open each of the workbooks for which you want the links to update, that
should
address
the problem. Note: AFAIK, you can't prevent links to an open workbook from updating.

--
Cheers
macropod
[MVP - Microsoft Word]


I have a document that has links to 6 different source documents. I only want
to have 5 of them updated (the last one is updated by being "pushed down"
from that document when it's changed). Is there a way to do this? Please
help!!! I've been looking for an answer to this question for over 2 days
now...

Thanks!