Copy and Paste Selected File in a Cell

[Jeridfruge]

I have a piece of code used to on a button to grab an excel file, copy a few bits of data from it, and paste to my current open file and sheet to particular cells. How do I take the path of the selected file and paste it as a link in my currently open workbook and sheet in cell A35. I just started trying to learn some VBA code last week and I'm kind of stuck here, so any advise I would appreciate!

Sub Get_Data_From_File()
    Dim FileToOpen As Variant
    Dim OpenBook As Workbook
    Application.ScreenUpdating = False
    FileToOpen = Application.GetOpenFilename(Title:="Browse for PDL File", FileFilter:="Excel Files(*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
        OpenBook.Sheets(11).Range("B34").Copy
        ThisWorkbook.ActiveSheet.Range("B23").PasteSpecial xlPasteValues
        OpenBook.Sheets(11).Range("B35").Copy
        ThisWorkbook.ActiveSheet.Range("B24").PasteSpecial xlPasteValues
        OpenBook.Close False
    
    End If
    Application.ScreenUpdating = True

End Sub

 

[Wookiee]

Does this work for you?

Sub Get_Data_From_File()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
Dim strPath As String

Application.ScreenUpdating = False

FileToOpen = Application.GetOpenFilename _
  (Title:="Browse for PDL File", FileFilter:="Excel Files(*.xls*),*xls*")

If FileToOpen <> False Then

  Set OpenBook = Application.Workbooks.Open(FileToOpen)

  OpenBook.Sheets(11).Range("B34").Copy
  ThisWorkbook.ActiveSheet.Range("B23").PasteSpecial xlPasteValues

  OpenBook.Sheets(11).Range("B35").Copy
  ThisWorkbook.ActiveSheet.Range("B24").PasteSpecial xlPasteValues

  ThisWorkbook.ActiveSheet.Range("A35").Select
  ThisWorkbook.Hyperlinks.Add Anchor:=Selection, Address:=OpenBook.Path

  OpenBook.Close False

End If

Application.ScreenUpdating = True

End Sub