Does anyone have any suggestions on why macro cannot refresh all link?
On file.xls, there are import external links on many worksheets, which will
automatically update all links once it is opened, but when I use a macro to
open this worksheets, the worksheet does not update the import external links
at all. Even though, the code .RefreshAll is included, this macro will close
this file, but the links do not update, does anyone have any suggestions why
RefreshAll coding does not perform?
Thanks in advance for any suggestions
Eric

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

With wkbk
.RefreshAll
.Close savechanges:=True
End With

hi
it is to may understanding that the refreshall command applies to query
tables and refresh all external querys that have their background query
property set to true.
see vb help on refresh all.
links are updated. see this site...
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

regards
FSt1

"Eric" wrote:

Does anyone have any suggestions on why macro cannot refresh all link?
On file.xls, there are import external links on many worksheets, which will
automatically update all links once it is opened, but when I use a macro to
open this worksheets, the worksheet does not update the import external links
at all. Even though, the code .RefreshAll is included, this macro will close
this file, but the links do not update, does anyone have any suggestions why
RefreshAll coding does not perform?
Thanks in advance for any suggestions
Eric

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

With wkbk
.RefreshAll
.Close savechanges:=True
End With

Thank you very much for suggestions
So how can I modify existing code to add xlUpdateLinksAlways option to
perform refreshAll?
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

"FSt1" wrote:

hi
it is to may understanding that the refreshall command applies to query
tables and refresh all external querys that have their background query
property set to true.
see vb help on refresh all.
links are updated. see this site...
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

regards
FSt1

"Eric" wrote:

Does anyone have any suggestions on why macro cannot refresh all link?
On file.xls, there are import external links on many worksheets, which will
automatically update all links once it is opened, but when I use a macro to
open this worksheets, the worksheet does not update the import external links
at all. Even though, the code .RefreshAll is included, this macro will close
this file, but the links do not update, does anyone have any suggestions why
RefreshAll coding does not perform?
Thanks in advance for any suggestions
Eric

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

With wkbk
.RefreshAll
.Close savechanges:=True
End With

Hi Eric,

Not certain the following will do what you want. Just FYI the first part
should identify the links and the second part should update them.

I will be interested in how it goes.

Sub UpDatingLinks()
Dim aLinks As Variant
Dim i As Long

aLinks = ActiveWorkbook.LinkSources
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

End Sub

--
Regards,

OssieMac


"Eric" wrote:

Thank you very much for suggestions
So how can I modify existing code to add xlUpdateLinksAlways option to
perform refreshAll?
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

"FSt1" wrote:

hi
it is to may understanding that the refreshall command applies to query
tables and refresh all external querys that have their background query
property set to true.
see vb help on refresh all.
links are updated. see this site...
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

regards
FSt1

"Eric" wrote:

Does anyone have any suggestions on why macro cannot refresh all link?
On file.xls, there are import external links on many worksheets, which will
automatically update all links once it is opened, but when I use a macro to
open this worksheets, the worksheet does not update the import external links
at all. Even though, the code .RefreshAll is included, this macro will close
this file, but the links do not update, does anyone have any suggestions why
RefreshAll coding does not perform?
Thanks in advance for any suggestions
Eric

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

With wkbk
.RefreshAll
.Close savechanges:=True
End With

Thank anyone for any suggestions
I get no idea what it is that
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

"OssieMac" wrote:

Hi Eric,

Not certain the following will do what you want. Just FYI the first part
should identify the links and the second part should update them.

I will be interested in how it goes.

Sub UpDatingLinks()
Dim aLinks As Variant
Dim i As Long

aLinks = ActiveWorkbook.LinkSources
If Not IsEmpty(aLinks) Then
For i = 1 To UBound(aLinks)
MsgBox "Link " & i & ":" & Chr(13) & aLinks(i)
Next i
End If

ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

End Sub

--
Regards,

OssieMac


"Eric" wrote:

Thank you very much for suggestions
So how can I modify existing code to add xlUpdateLinksAlways option to
perform refreshAll?
Do you have any suggestions?
Thanks in advance for any suggestions
Eric

"FSt1" wrote:

hi
it is to may understanding that the refreshall command applies to query
tables and refresh all external querys that have their background query
property set to true.
see vb help on refresh all.
links are updated. see this site...
http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

regards
FSt1

"Eric" wrote:

Does anyone have any suggestions on why macro cannot refresh all link?
On file.xls, there are import external links on many worksheets, which will
automatically update all links once it is opened, but when I use a macro to
open this worksheets, the worksheet does not update the import external links
at all. Even though, the code .RefreshAll is included, this macro will close
this file, but the links do not update, does anyone have any suggestions why
RefreshAll coding does not perform?
Thanks in advance for any suggestions
Eric

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

With wkbk
.RefreshAll
.Close savechanges:=True
End With

Hi Eric,

My apologies. I thought you would know how to translate it into your code.
Here is your code with the update links code included.

As I said before I will be interested in how it goes.

Public Sub UpdatingLists2()

Dim WkbkName As String
Dim wkbk As Workbook
On Error Resume Next

WkbkName = "D:\Documents\file.xls"

Set wkbk = Nothing
On Error Resume Next

Set wkbk = Workbooks.Open(Filename:=WkbkName, UpdateLinks:=3)
On Error GoTo 0

wkbk.UpdateLink Name:=wkbk.LinkSources

wkbk.Close savechanges:=True

--
Regards,

OssieMac