How to write VBA code to Link to another workbook.

[Jodeyarce]

Hi all,

I currently have tonnes of files which I need to link a single cell from all of them to a single workbook. For E.g.

Current Workbook
(A2:A25) (B2:B25)
Jan 2011
Feb 2011
........

Is there a VBA code to link the values into B2:B25? My files are all saved as the names of A2:A25,i.e. Jan 2011.xlm, Feb 2011.xlm and so on and so forth. As i will be continually adding more months into my A-column, is there a VBA code that will allow my corresponding cells in B-column to automatically link to the specified cell of the new months added in A-column?

Thanks in advance!

[Jodeyarce]

I've tried something like this but to no avail

[VBA]
Sub Button1_Click()

For Each Cell In Range("B2:B13")
Cell.Formula = "=Excel.SheetMacroEnabled.12|'C:\Documents\[Cells.Offset(-1,0).xlsm]Sheet1!R16C1'"
Next Cell
End Sub
[/VBA]

PS: I'm totally new to VBA so my code may look like a joke

[mdmackillop]

You mean as in =['Jan 2011.xlsx']Sheet1!$B$2 ?
Where are the sheet and cell specified?

Also check out the Indirect function.

[mdmackillop]

You could also try this method from here.
[VBA]Option Explicit
Sub TestClosedWB()
Dim v As Variant
v = GetInfoFromClosedFile("C:\Test", "ClosedWorkbook.xlsx", "Sheet1", "A1")
MsgBox v
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String

GetInfoFromClosedFile = ""

If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"

If Dir(wbPath & wbName) = "" Then Exit Function

arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)

On Error Resume Next

GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function
[/VBA]

[Jodeyarce]

Well,

Something like that. I'm trying to write a code which automatically create links in column B.

For example,
If A2 reflects "Jan 2011", then B2 will automatically be linked to the file entitled "Jan 2011" in my documents folder. Therefore, if i were to add more entries to column A, the corresponding cells in column B will just update the values from the file specified in column A.

As to which cell the current workbook is extracting from, it is the same cell for all my data files (all Jan 2011, Feb 2011.....Dec 2011 -> linked to Cell B16)

Hope i make sense.