Linking to Files in other Folders using Excel VBA

[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

[Ron de Bruin]

Try this
http://www.rondebruin.nl/summary2.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Jrew23" <jrew23@yahoo.com> wrote in message news:1121407487.902817.234780@z14g2000cwz.googlegroups.com...
> 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
>