I need to merge data from 100+ excel files into single file using VBA where:

my repository (D:\rep) contain 100+ files with name of ( file1, file2 ...etc) every excel file consist from 18 rows fixed in all excel files and having different number of columns, following sample of my excel files

sample of excel files

i need to merge data from all those file into one file and combine the data as follow:

rows is still same in new file , column 1 is(file 1) column and its value refer to the sum of rows values as shown in the following figure

sample of merged file

closed as too broad by DavidPostill, nc4pk, mdpc, G-Man, nKn Dec 14 '16 at 21:06

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Can you clarify exactly how you want the data to be combined? In your example single file, each tool has multiple columns for different things (cal, speedr, val etc.), but in your example combined file there is just one column per tool per file. So should the cell B2 in your combined file example be the sum of the range B2:[x]2 from the individual file (where [x] is the furthest right column containing data)? – 3N1GM4 Dec 10 '16 at 17:54

There is a method much easier and better than VBA if the Excel 2010 version contains the right option ...

I've not used this with Excel 2010 but PowerQuery should be available as a free addin from Microsoft. If the 2010 addin version is anything like the current version of PowerQuery, you can easily create a query that loads all or some files from a folder and merges them.

PowerQuery is a point and click ETL (Extract, Transform and Load) tool for Excel and is both (fairly) easy to use and extremely powerful.

To do this in VBA is a lot more complex.

However, Microsoft provide some example code that might be used as a starting point

Sub MergeAllWorkbooks()
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim NRow As Long
    Dim FileName As String
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range

    ' Create a new workbook and set a variable to the first sheet. 
    Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

    ' Modify this folder path to point to the files you want to use.
    FolderPath = "C:\Users\Peter\invoices\"

    ' NRow keeps track of where to insert new rows in the destination workbook.
    NRow = 1

    ' Call Dir the first time, pointing it to all Excel files in the folder path.
    FileName = Dir(FolderPath & "*.xl*")

    ' Loop until Dir returns an empty string.
    Do While FileName <> ""
        ' Open a workbook in the folder
        Set WorkBk = Workbooks.Open(FolderPath & FileName)

        ' Set the cell in column A to be the file name.
        SummarySheet.Range("A" & NRow).Value = FileName

        ' Set the source range to be A9 through C9.
        ' Modify this range for your workbooks. 
        ' It can span multiple rows.
        Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")

        ' Set the destination range to start at column B and 
        ' be the same size as the source range.
        Set DestRange = SummarySheet.Range("B" & NRow)
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _

        ' Copy over the values from the source to the destination.
        DestRange.Value = SourceRange.Value

        ' Increase NRow so that we know where to copy data next.
        NRow = NRow + DestRange.Rows.Count

        ' Close the source workbook without saving changes.
        WorkBk.Close savechanges:=False

        ' Use Dir to get the next file name.
        FileName = Dir()

    ' Call AutoFit on the destination sheet so that all 
    ' data is readable.
End Sub

See here for the full information.

There is also an example already on Stack:


  • Julian Knight, thanks for your suggested code and tool, power query looking good but i'm not familiar, i will search about power query to perform my task – Ray ben Dec 12 '16 at 12:26
  • I've just updated another answer with similar details so you might want to look at that although that's for Office 2016. – Julian Knight Dec 12 '16 at 15:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.