Updating links with auto updating

[syndee]

My Control File contains several worksheets.
It links to data in other files in same folder.
Just having problem with updating links with auto updating and returning to Control File.xlsm
Tried this but need files to save and close except Control File.xlsm after updating.
Is that possible?

Private Sub Workbook_Open()

Application.ScreenUpdating = False

ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 2.csv"
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 1.csv"

Application.ScreenUpdating = True

End Sub

 

[richardtims]

Try This......

Private Sub Workbook_Open()
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 2.csv" _
, UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
Workbooks.Open Filename:= _
"C:\Users\Cindy\Desktop\Excel-Trial File to eliminate pasting\File 1.csv" _
, UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[syndee]

Thanks. It lags a bit but works.

 

[syndee]

Only problem when I put folder into another folder the location doesn't change. This folder will be used in many company folders. Anyway to tell it files are all in same folder regardless where it is located. Linked files will always be named File 1.cs & File 2.csv. Just location of folder will change.

 

[richardtims]

Are the files going to be on a local machines or in a shared drive?

 

[richardtims]

If the files are going to be in a shared location, you would simply update the file location with the shared drive info. If the files are going to be on each users desktop, try the following:

Private Sub Workbook_Open()
Workbooks.Open Filename:= _
"C:\Users\" & Environ("UserName") & "\Desktop\Excel-Trial File to eliminate pasting\File 2.csv" _
, UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
Workbooks.Open Filename:= _
"C:\Users\" & Environ("UserName") & "\Desktop\Excel-Trial File to eliminate pasting\File 1.csv" _
, UpdateLinks:=3
ActiveWorkbook.Save
ActiveWindow.Close
End Sub

 

[syndee]

They will be on local machines

 

[richardtims]

Did the above solution work for you?

 

[syndee]

It seems to do the trick.