links update

[Alex]

Whenn I have the Sheet1 opened and open the Sheet2 from the Excel window all
links in the Sheet1 are updated.
But, when I'm using the code below to update the links in the Sheet1
openning the Sheet2 programmaticaly it's not working.
What can be wrong with this code? Why the values in the Sheet1 are not
updated?

Sub Update_Links()
On Error GoTo Update_Links_Err
Dim xlapp As Object
Dim xlbook As Object
Dim strFileName As String
Dim logFlag As Boolean

logFlag = False

strFileName = "Sheet2.xls"

Set xlapp = CreateObject("Excel.Application")

xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False

Set xlbook = xlapp.Workbooks.Open(strFileName, UpdateLinks:=1)
logFlag = True

xlapp.AskToUpdateLinks = True
xlapp.DisplayAlerts = True

xlbook.Close savechanges:=True
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing

Update_Links_exit:

Exit Sub
Update_Links_Err:
xlapp.DisplayAlerts = True
xlapp.AskToUpdateLinks = True
If logFlag Then
xlbook.Close savechanges:=False
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing
End If
MsgBox Err.Description
Resume Update_Links_exit
End Sub

 

[Jim Cone]

It appears that you have two instances of Excel open.
Sheet1 is open in the first instance and Sheet2 is open in the new instance.
A couple of things to try...
1. UpdateLinks:=3
-or-
2. Set a reference to the existing Excel object instead of opening a new one...
Set xlapp = Getobject(, "Excel.Application")

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Alex" <[email protected]>
wrote in message
Whenn I have the Sheet1 opened and open the Sheet2 from the Excel window all
links in the Sheet1 are updated.
But, when I'm using the code below to update the links in the Sheet1
openning the Sheet2 programmaticaly it's not working.
What can be wrong with this code? Why the values in the Sheet1 are not
updated?

Sub Update_Links()
On Error GoTo Update_Links_Err
Dim xlapp As Object
Dim xlbook As Object
Dim strFileName As String
Dim logFlag As Boolean

logFlag = False

strFileName = "Sheet2.xls"

Set xlapp = CreateObject("Excel.Application")

xlapp.DisplayAlerts = False
xlapp.AskToUpdateLinks = False

Set xlbook = xlapp.Workbooks.Open(strFileName, UpdateLinks:=1)
logFlag = True

xlapp.AskToUpdateLinks = True
xlapp.DisplayAlerts = True

xlbook.Close savechanges:=True
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing

Update_Links_exit:

Exit Sub
Update_Links_Err:
xlapp.DisplayAlerts = True
xlapp.AskToUpdateLinks = True
If logFlag Then
xlbook.Close savechanges:=False
xlapp.Quit

Set xlbook = Nothing
Set xlapp = Nothing
End If
MsgBox Err.Description
Resume Update_Links_exit
End Sub

 

[Alex]

Thanks a lot, Jim.
It's working well.
I've applied the both:
UpdateLinks:=3 and
Set xlapp = Getobject(, "Excel.Application")

Just the one tiny thing - the file (Sheet2) is being appeared for a moment
during the updating.
Is it possible to make the openning file invisible?

 

[Jim Cone]

Before opening the file...
xlapp.ScreenUpdating = False

Then change it back to true, if you don't close the application.
Jim Cone
San Francisco, USA



Thanks a lot, Jim.
It's working well.
I've applied the both:
UpdateLinks:=3 and
Set xlapp = Getobject(, "Excel.Application")
Just the one tiny thing - the file (Sheet2) is being appeared for a moment
during the updating.
Is it possible to make the openning file invisible?

 

[Alex]

Thanks Jim.
It's working great.

Before opening the file...
xlapp.ScreenUpdating = False

Then change it back to true, if you don't close the application.
Jim Cone
San Francisco, USA



Thanks a lot, Jim.
It's working well.
I've applied the both:
UpdateLinks:=3 and
Set xlapp = Getobject(, "Excel.Application")
Just the one tiny thing - the file (Sheet2) is being appeared for a moment
during the updating.
Is it possible to make the openning file invisible?