Code to Link to an unknown source

judgejustin

Board Regular
Joined
Mar 3, 2014
Messages
139
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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.

Code:
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,408
Messages
6,113,538
Members
448,494
Latest member
alecto3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top