How can I use VBA to copy data from the closed workbook to the open workbook on Microsoft Excel 2010?

I am trying to use a VBA to copy data from the closed workbook to the open workbook on microsoft excel 2010? When the user will open macro-enabled Excel file then there should be an imediate prompt displaying for the user to enter or select file path of the desired workbooks. After that they have to choose 2 files and the file names might not be consistent? Can anyone tell me how should I do it? Thanks

You can try to use the basic code that is given below. The below code will usually ask the user to choose 2 files and then import the relevant sheet into the current network. Just take a look at the below code:

Code:

---

Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2

    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub

    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select Second file")
    If Ret2 = False Then Exit Sub

    Set wb2 = Workbooks.Open(Ret1)
    wb2.Sheets(1).Copy Before:=wb1.Sheets(1)
    ActiveSheet.Name = "Blah Blah 1"
    wb2.Close SaveChanges:=False

    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).Copy After:=wb1.Sheets(1)
    ActiveSheet.Name = "Blah Blah 2"
    wb2.Close SaveChanges:=False

    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub

---

The VBA does not include any method to retrieve the value from the closed file. However you can use the benefit of the Excel so that you can link the files. I recommend that you should simply use the GetValue function to retrieve the value from the closed workbook. It exactly happens when you are supposed to do the calling by the XLM macro.

You can also check the below code which is yet another option that you can use:

Code:

---

Option Explicit

Sub Sample()
    Dim wb1 As Workbook, wb2 As Workbook
    Dim Ret1, Ret2

    Set wb1 = ActiveWorkbook

    '~~> Get the first File
    Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select first file")
    If Ret1 = False Then Exit Sub

    '~~> Get the 2nd File
    Ret2 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
    , "Please select Second file")
    If Ret2 = False Then Exit Sub

    Set wb2 = Workbooks.Open(Ret1)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(1).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Workbooks.Open(Ret2)
    wb2.Sheets(1).Cells.Copy wb1.Sheets(2).Cells
    wb2.Close SaveChanges:=False

    Set wb2 = Nothing
    Set wb1 = Nothing
End Sub

---