Linking Cells in Excel

[Jumpercables]

I wan to link cells in my Source.xls into my Recieving.xls workbook. I kow you can do this by the following (without code, i.e in Excel)

Open both workbooks.
Select the range on the "sending" worksheet.
edit|copy
select the range on the "receiving" worksheet that should be linked.
edit|Paste special|paste link

But can this be done withing VBA, VB or VB.Net?

[mikeyc1204]

Some assumptions:
Sheet that will contain the link = "NewSheet"
Cell that will contain the link = "D2"
Workbook that contains source info = "LinkedBook.xls"
Sheet in above workbook that you are linking to = "LinkedSheet"
Cell that will contain the source info = "A1"

Sheets("NewSheet").Range("$D$2").Formula = "='[LinkedBook.xls]LinkedSheet'!$A$1"

[DKenny]

mikeyc - You will also need the full path to the source workbook

Sub CreateLink()
Dim sSourcePath As String
Dim sSourceBook As String
Dim sSourceSheet As String
Dim sSourceRange As String
Dim sFullAddress As String
 
    sSourcePath = "C:\Files\" 'Put the complete path to you source workbook here
    sSourceBook = "YourSourceWorkbook.xls" 'put the name of your source workbook here
    sSourceSheet = "YourSheetname" 'put the name of your sdource worksheet here
    sSourceRange = "B12" 'put the address of the source cell here
        
    sFullAddress = "='" & sSourcePath
    sFullAddress = sFullAddress & "[" & sSourceBook & "]"
    sFullAddress = sFullAddress & sSourceSheet & "'!"
    sFullAddress = sFullAddress & sSourceRange
    
    Range("C1").Formula = sFullAddress 'Put the cell where you want the link here
End Sub

[zaza]

Although you don't need the full path if the workbook you're looking for is in the same folder or a subfolder of the folder from which you're running the spreadsheet.

zaza

[Jumpercables]

So if I have a bunch of Spreadsheets in the same folder named EXCELLINKS I dont need the full path just the workbook name?

[zaza]

Try it!