CODEWITHSUNDEEP
vbaexcelxlm
basilbc
basilbc

Reputation: 69

Merging first sheets of multiple excel file into another sheet

We have three files, File 1 & 2 contain data at Sheet 1. In File 3, Sheet 2 has a button and a table (containing file 1 & 2 details such as filename and path):

File 1
ABC 123

File 2
BAC 321

After clicking the button at Sheet 2, a macro should fetch the data from Sheet 1 of File 1 and 2 and should place them in Sheet 1 of File 3 as shown below:-

File 3
ABC 123
BAC 321

I have found few codes using following functionality to do the same, but they will work only at the sheet the code is in.

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
      Range(ref).Range("A1").Address(, , xlR1C1)
'   Execute an XLM macro
    GetValue = ExecuteExcel4Macro(arg)

Upvotes: 0

Views: 309

Answers (1)

Automate This
Automate This

Reputation: 31394

Here is a good link for what your trying to do. Get info from closed file

Here is some code to get you started. Right now it's set up to take the first file path from sheet2 'A1' and workbook name from 'A2' and return the value to sheet1 'A1'. Once you have this working you'll need to loop through your range (depends on how you have that setup) and display the results.

Sub ReadDataFromAllWorkbooksInFolder()
    Dim FolderName As String, wbName As String, r As Long, cValue As Variant
    Dim wbList() As String, wbCount As Integer, i As Integer

    'Path from sheet two in macro book
    FolderName = Worksheets(2).Range("A1").Text

    'File Name from sheet two in macro book
    Filename = Worksheets(2).Range("A2").Text
    wbName = Worksheets(2).Range("A2").Text

    cValue = GetValue(FolderName, wbName, "Sheet1", "A1")
    Worksheets(1).Cells(1, 1).Formula = cValue
End Sub

Function GetValue(Path, File, Sheet, Ref)
     'Retrieves a value from a closed workbook
    Dim Arg As String
     'Make sure the file exists
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Dir(Path & File) = "" Then
        GetValue = "File not  Found"
        Exit Function
    End If
     'Create the argument
    Arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & Range(Ref.Range("A1").Address(, , xlR1C1))
     'Execute XLM macro
    GetValue = ExecuteExcel4Macro(Arg)
End Function

Upvotes: 1

Related Questions