Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Code to Link to an unknown source

  1. #1
    Board Regular
    Join Date
    Mar 2014
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code to Link to an unknown source

    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.

  2. #2
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    953
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to Link to an unknown source

    Can we use VBA??

  3. #3
    Board Regular
    Join Date
    Mar 2014
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to Link to an unknown source

    Absolutely, that's what I intended to use.

  4. #4
    Board Regular rpaulson's Avatar
    Join Date
    Oct 2007
    Location
    Fremont, OH USA
    Posts
    953
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to Link to an unknown source

    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

  5. #5
    Board Regular
    Join Date
    Mar 2014
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code to Link to an unknown source

    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?

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •