Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update
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" wrote: 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update
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 "Alex" wrote in message... 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" wrote: 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
links update
Thanks Jim.
It's working great. "Jim Cone" wrote: 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 "Alex" wrote in message... 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" wrote: 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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Update links box gives Continue or Edit Links dialog | Excel Discussion (Misc queries) | |||
Update Links | Excel Programming | |||
Excel 2003 - Update or Don't Update Links Problem | Excel Programming | |||
can't update links...can't find links | Excel Discussion (Misc queries) | |||
Update Links | Excel Programming |