automatic update from external source

[maksinx]

hello everyone,

I have a main excel file which has external links to another excel file which is located in a company share folder and being used by 3-4 people.

when my colleagues enter new data my main file updates itselt but to do that i do close the excel file and reopen again
my question is is there any way to click on a button or command button which enables me to update the file rather that reopening file again.

thank you for your help.

kind regards

[moa]

Go to the Edit menu, choose Links, highlight the links then click the update values button.

[maksinx]

dear glen,
thanks for the tip which is much better than reopening the file
however is there any chance to create a button which makes the updating files( brief vb code/macro etc...)

please advice

[malik641]

Try recording a macro while performing Glen's suggestion. I came up with the following:
Sub Macro1()
ActiveWorkbook.UpdateLink Name:="C:\SomeWorkbook.xls", Type:=xlExcelLinks
End Sub
For multiple workbooks you would just explicitly call out the other workbooks.

Hope this helps.

[maksinx]

hello malik,
i have tried to do what you have suggested but seems either dont work
or i have done something wrong, can you explain me in steps what shall i do...

thanks in advance

[moa]

Hello again,

Steps for recording your Macro:

In your workbook go Tools -> Macro... -> Record New Macro...
Name your Macro something meaningful if you like (updateLinks for instance).
Click Okay and a small dialog box should pop up with a stop button.
Follow the steps in my first post (do nothing else in the workbook or it will be recorded in the macro) then click the stop button on the little dialog box.
You now have the macro.

Add a button to your tool bar:

You can right-click anywhere on the toolbar and choose Customize...
Click on the Commands tab and click on Macros in the Categories list box.
You should then have the choice of a custom button or custom menu Item.
Choose the button and drag it to the tool bar.
Back on the Commands tab you will see that Modify Selection is enabled. Click this and choose Assign Macro.
Choose the macro you have just created and that's it.