excel and updating links on another excel sheet
Posted on 2012-04-02
i have three workbook a, b,c a workbook is updated daily and gets renamed each day, eg a24-4-2012 then a25-4-2012 this has numbers that i need to access on workbook c.
so i was going to use workbook b to store the numbers from a for the time that when excel workbook c wants them then it will be linking to the same range names on workbook b all the time.
now what i am thinking of doing is to have range names set up on workbook a, which the name will change and rows and colums will be inserted, but these wil not change the range name positions, and when they are finished the workbook will then up date the data on workbook B so when workbook a has its name changed it will not affect the data as it will have updated the data on workbook b whos name will remain the same.
end of day sheet a saves to workbook b
workbook c picks up its data from workbook b
and all the data arrives correctly
so what i need is a reverse link, the save updates the range name on workbook range name b and this is the code that i am looking for
workbook.b.rangename "rbr-45" = workbook.a.rangename "rbr-45"
any ideas on the code woudl be very helpfull or alternate ideas if this is going down the wrong track
this get statment gets the value from the correct sheet but what i need is the opposite a put value on the page
Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String
' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)