Automatic Update

[gmazza76]

Good Morning All,

Just a quick question.

I have a spreadsheet that reads info from other spreadsheets and produces an overview of several areas. Due to this when i open the overview spreadsheet it asks me if i want to update the external links.

Is there anyway i can write this in VBA so it automatically updates without asking people to click the "Update Links" button when it loads.

Many Thanks

 

[Richard Schollar]

The Open method of the Workbooks object includes an argument to UpdateLinks so if you control opening of the workbook thru VBA yes you can do this:

Workbooks.Open FileName:="C:\Overview.xls",UpdateLinks:=True

 

[TiaXL]

Good Morning All,

Just a quick question.

I have a spreadsheet that reads info from other spreadsheets and produces an overview of several areas. Due to this when i open the overview spreadsheet it asks me if i want to update the external links.

Is there anyway i can write this in VBA so it automatically updates without asking people to click the "Update Links" button when it loads.

Many Thanks

workbooks.open("YourWorkbook.xls"),updatelinks:=true

 

[gmazza76]

Cheers,

Would i have to put this in the initialise part in the VB

Gavin

 

[TiaXL]

Cheers,

Would i have to put this in the initialise part in the VB

Gavin

This is the problem you are going to have

as the code is initilized and run AFTER this message is displayed, regardless of where you put it.

The only option is to open the workbook from another workbook that way the code that disables the updatelinks prompt can run before the file is opened.

 

[Richard Schollar]

How are you opening the workbook currently? If it is from the user interface (eg someone clicks the file in Explorer or File>Open then you won't be able to enforce automatic updating of links. If you currently open it via VBA then you can use the code given above.

 

[gmazza76]

People open it via a link or just through excel

 

[TiaXL]

People open it via a link or just through excel

I would either change your link to open a different file and include the above code in the "This Workbook" section in VBA and select the

workbooks_Open from the declarations and put the code in there and then include a workbook.close statement in your other file to close the dummy file you used to open this one, if that makes sense ?

or just click the button to update