Code to Link to an unknown source

[judgejustin]

I don't know if what I want to do is possible. I have a workbook (#1 ) that I need to link to other workbooks (#2,3,4,etc.). They cell references will always be the same but the actual workbook names are completely unknown until they are received.
My vision is to have a button located on #1 and the macro that it runs will allow the user to select the which new workbook to link to. Each cell that needs to be linked can already have the formula in it such as (='the selected sheet'A1)
I hope this makes sense and any suggestions would be appreciated.

 

[rpaulson]

Can we use VBA??

 

[judgejustin]

Absolutely, that's what I intended to use.

 

[rpaulson]

Here you go.

Create a new sheet in your workbook and name it WB2 you can hide this sheet if you want to.

in cell A1 on your main sheet enter this formula
='WB2'!A1

add a button to link to the macro below.

Sub Import()

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim rs As Worksheet
Set rs = Worksheets("WB2")

Dim myFile As String
myFile = Application.GetOpenFilename(Title:="Please choose a file to open", _
FileFilter:="Excel Files *.xls* (*.xls*),")

If myFile = vbNullString Then Exit Sub

Workbooks.Open fpath & myFile
Cells.Copy
rs.Range("A1").PasteSpecial Paste:=xlValues
ActiveWorkbook.Close False

End Sub

hth,

Ross

 

[judgejustin]

This code works great!!!, I have been out hence the long time in responding. I just have one more teak that I need. This copies the active sheet in the file that is selected. Can I set the specific sheet names (They will always be the same, but there are multiples) in the selected workbook to copy?