Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

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

Why macro cannot refreshall?
 

Thank you very much for suggestions
I have tried it, the worksheet performs recalucation on all worksheets
without refreshing any external links from internet. On this workbook, each
sheet will update all imported links automatically, once this file is opened.
After I add following code, there is no internet access signals and the text
information does not update at all, and only perform recalculation functions.
Do you have any suggestions on what is going on?
Thank everyone very much for any suggestions
Eric

"OssieMac" wrote:

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

Why macro cannot refreshall?
 

Hi Eric,

Your last post is the first time in this thread that you have mentioned
Internet. Your initial code sample gave no indication that you wanted link
updates from the internet and that is probably the reason you are not getting
the answers you require.

I now suggest that you post the question again with the heading "How to
update Excel workbook links via the internet" and include your code for
accessing these workbooks via the internet and hopefully you will get answers
from those that have experience with these problems. (You should be able to
blank out actual internet links/addresses with asterisks or something for
security purposes.)


--
Regards,

OssieMac


"Eric" wrote:

Thank you very much for suggestions
I have tried it, the worksheet performs recalucation on all worksheets
without refreshing any external links from internet. On this workbook, each
sheet will update all imported links automatically, once this file is opened.
After I add following code, there is no internet access signals and the text
information does not update at all, and only perform recalculation functions.
Do you have any suggestions on what is going on?
Thank everyone very much for any suggestions
Eric

"OssieMac" wrote:

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