Extract data from multiple excels, and macro to create repeated vlookup

[nth]

hi guys,

Wanna know how to extract data from the multiple excel files,
in vba or macro.

i have tried this

Sub GetDataDemo()
     
    Dim FilePath$, Row&, Column&, Address$
     
     'change constants & FilePath below to suit
     '***************************************
    Const FileName$ = "Book1.xlsx"
    Const SheetName$ = "Sheet1"
    Const NumRows& = 1000
    Const NumColumns& = 1
    FilePath = ActiveWorkbook.Path & "\"
     '***************************************
     
    DoEvents
    Application.ScreenUpdating = False
    If Dir(FilePath & FileName) = Empty Then
        MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
        Exit Sub
    End If
    For Row = 1 To NumRows
        For Column = 1 To NumColumns
            Address = Cells(Row, Column).Address
            Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
            Columns.AutoFit
        Next Column
    Next Row
    ActiveWindow.DisplayZeros = False
End Sub


Private Function GetData(Path, File, Sheet, Address)
    Dim Data$
    Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
    Range(Address).Range("A1").Address(, , xlR1C1)
    GetData = ExecuteExcel4Macro(Data)
End Function

but only effect with small amount of data.
large amount of data will keep me waiting and waiting.

and after extract the data from example into masterexample, wanna know how to repeated the vlookup with using macro in simple click.

Thank you so much!

 

[Deepak]

Hi,

Warm welcome to the forum.
Request you to pls be specific about the query with some more examples/samples.