URGENT HELP REQUIRED

[ads_3131]

Hi all!

I need help with the following:

I have a list of names of workbooks in column A1 to A7

I then have the following example code in C1 to C7.....

='C:\test\[2011.xls]Sheet1'!B49

this looks up the location, workbook name, sheetname and the reference of the cell.................to finally show the value In my current spreadsheet from that spreadsheet......

Trying to explain the best i can :/

So what im asking for help with is ... instead of manually inputting the workbook name into my function e.g (2011) , how can i reference that to cell A1 to take the value which already has the workbook name.....

ANY HELP WOULD BE GREAT !

[Bob Phillips]

Try

="='C:\test\["&A1&"]Sheet1'!B49"

[ads_3131]

Hey !

Thanks for the quick response, that does pull in the filename out of A1.... and place it into that function.... however the code wont now pull up that cells data from that workbook.... its just showing me the code and not doing anything :S

[Bob Phillips]

Sorry missed that.

You have a problem here in that if you reference it directly, you can work with a closed workbook. But, if referencing it indirectly, you cannot (easily) work with a closed workbook.

[ads_3131]

:(

So im doomed?.... sooo close too :(

[ads_3131]

So because all the workbooks are generic and within the same directory ill have to manually change the workbook name within the code every cell

='C:\test\[2011.xls]Sheet1'!B49

??

Isnt there a simple VBA solution?

[Bob Phillips]

There is, should be, but we need a trigger event. What would you like?

[ads_3131]

......... a button?

[Bob Phillips]

assign this macro to the button



Public Sub SetLinks()
Const path As String = "'C:\test\"
Const targetcell = "Sheet1'!B49"
Dim cell As Range

For Each cell In Range("A1:A7")

If cell.Value <> "" Then

cell.Offset(0, 2).Value = "'" & path & "[" & cell.Value & "]" & targetcell
cell.Offset(0, 2).Formula = "=" & cell.Offset(0, 2).Value
End If
Next cell
End Sub