Refrancing xlExcelLinks - VBA

[iainmartin100]

Hi,
I have the following simplified array formula in cell A1 Sheet1, If I wanted to update the link within excel itself I would simply click Edit>Links>Change Source and job done.

The issue is that this formula needs to be within a macro and just the result displayed in cell A1 Sheet1
{=INDEX('C:\link.xls]tab1'!$F$1:$F$100,MATCH($H$7&$C$68,'C:\link.xls]tab1'!$C$1:$C$100&'C:\link.xls]tab1'!$D$1:$D$100))}

The user could change the link at any time so I need to somehow refrance the exterenal link? I have tried the following but it doesn't come anywhere close to working, any ideas?

Sub macro3()
Dim WW As Variant
WW = ThisWorkbook.LinkSources(xlExcelLinks)

Range("A1").Formula = "='WW Sheet1'!$A$1"
End Sub

 

[Andrew Poulsom]

This worked for me with a single link in the workbook:

Sub macro3()
    Dim WW As Variant
    Dim wbName As String
    WW = ThisWorkbook.LinkSources(xlExcelLinks)
    wbName = Right(WW(1), Len(WW(1)) - InStrRev(WW(1), "\"))
    Range("A1").Formula = "='[" & wbName & "]Sheet1'!$A$1"
End Sub

 

[iainmartin100]

That seems to do the trick, thank you so much for your help.

 

[iainmartin100]

Hi, One more issue now
When I run the below Macro it places the full formula in the cell as text, if I remove the array signs and then press ctrl+Shift+enter then the formula works, any ideas on how to fix this?

Sub Formula()
Dim WW As Variant
Dim wbName As String

WW = ThisWorkbook.LinkSources(xlExcelLinks)
wbName = Right(WW(1), Len(WW(1)) - InStrRev(WW(1), "\"))

Range("H68").FormulaArray = "{=INDEX('[" & wbName & "]MS Groups'!$F$2:$F$6000,MATCH($H$7&$C$68,'[" & wbName & "]MS Groups'!$C$2:$C$6000&'[" & wbName & "]MS Groups'!$D$2:$D$6000))}"

End Sub

 

[Andrew Poulsom]

You need to use R1C1 style references with the FormulaArray property.

 

[iainmartin100]

Hi, If possible can I request your help one last time?

My original VB formula was:

'Range("H68").FormulaArray = "{=INDEX('[" & wbName & "]MS Groups'!$F$2:$F$6000,MATCH($H$7&$C$68,'[" & wbName & "]MS Groups'!$C$2:$C$6000&'[" & wbName & "]MS Groups'!$D$2:$D$6000))}"

And my attempt at changing to R1C1 is below, unfortunatly it doesnt work, have you any ideas?

Range("h68").FormulaArray = "{=INDEX('[" & wbName & "]MS Groups'!r[6]c[2]:r[6]c[6000],MATCH(r[8]c[7]&r[3]c[68],'[" & wbName & "]MS Groups'!r[3]c[2]:r[3]c[6000]&'[" & wbName & "]MS Groups'!r[4]c[2]:r[4]c[6000]))}"

Many thanks in advance.