Excel: Datestamp for Updating Links

[raa44634]

Excel workbooks that have links to external references pose the question "Update links?" upon opening. Is it possible to have a datestamp appear in say cell A1 stating "Last Updated on mm/dd/yy" and is dependent on clicking Yes. Naturally, the date wouldn't update on a NO response...
Thanks in advance!

-- Tony --

 

[chandlm]

Tony,

There is probably a better way to do this but you could try this.

Open the file that contains the links.

On the menubar select "edit" > "links"
Make sure that the updates links is set to manual.

In the locals window in vba editor enter

application.AskToUpdateLinks = false 'this will turn off the automatic dialog asking you if you want to update the links.

then paste this code into the open event of the workbook remembering to change the linksource file which I have inserted in green. .

resp = MsgBox("update links now", vbQuestion + vbYesNo, "update?&quot
If resp = vbYes Then
ActiveWorkbook.UpdateLink Name:= _
"Excel.Sheet.8|C:\\My Documents\linksourcefile.xls!'" _
, Type:=xlOLELinks

range("a1&quot.Value = "Links Last Updated on " & Format(Date, "dd/mm/yy&quot


End If


Save the file and close Excel.



HTH

Matt