Hi, i have a report (Report 1) that requires information from another report (Report 2) to update correctly. Both are stored on shared drives on a network. I'm not sure if this has something to do with things....

This is SO SIMPLE to do manually but i'm really struggling to do it in VBA. I've tried it two ways to no avail, I'm on Excel 2010...

With the Report 2 CLOSED i have tried the following 

ActiveWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

But this just returns #value errors in all my linked cells

So i tried the below and the values still don't update. Anyone got any ideas? The end user isn't expected to have any real Excel ability so i'd prefer this to be a "one button" process rather than getting them to start mucking around in the "Edit Links" menu...

Dim Report2 as string
Dim R1 as Workbook
Dim R2 as Workbook

Set R1 = ActiveWorkBook

Report 2 = "\\uk.mycompany.com\data\group\etc...."

Workbooks.open filename:=Report2, Readonly:=False
Set R2 = Activeworkbook

R1.activate

activeworkbook.updatelink Name:=Thisworkbook.linksources


R2.close
R1.activate
All help greatly appreciated!