Linking to Files in other Folders using Excel VBA

  • Thread starter Thread starter Jrew23
  • Start date Start date
J

Jrew23

Hi,

I want to create a macro in a workbook that automatically links to
other worksheets. For instance, if i run the following code below, cell
E12 will automatically link to cell E12 in sheet "Accounting Data" of
file "6460.xls" in folder "C:\My Documents\Accounts\


Range("E12").FormulaR1C1 = "='C:\My
Documents\Accounts\[6460.xls]Accounting Data'!RC"

I could repeat this code over and over again in reference to other cell
ranges, but I'd like to clean it up. I've attempted to do so, with the
following code:

'------------------------------
Sub GetDataFromFolder()
Dim myFolder As String
Dim myFile As String
Dim mySheet As String

'Range("E12").FormulaR1C1 = "='C:\My
Documents\Accounts\[6460.xls]Accounting Data'!RC"
myFolder = "C:\My Documents\Accounts\"
myFile = "[6460.xls]"
mySheet = "Accounting Data'!RC"

Range("E12").FormulaR1C1 = "=&myFolder & myFile & mySheet"

End Sub
'-----------------------------

BUT - i get the following error

Run-time Error 1004
Application - Defined or Object Error

can some let me know how to fix this? I'm not sure if i need to use
"formulaR1C1" references or not. Thanks in advance
 
Back
Top