Thread: Collect data from several excel files

Hi folks,

I am working on my thesis and I want to extract some data from 2-300 excel files.

The excel files have the same structure, so what I need is a macro that allow me to copy the content of the cell B3 from all the excel files and to past these in a list.

The excel files are in .xls format and are named like 1.xls; 2.xls....300.xls

So the output should be like this:
A1 = cell B3 from 1.xls
A2 = cell B3 from 2.xls
.....
A300 = cell B3 from 300.xls

Forgive me for my bad english! I hope my problem is clear, do not hesitate to ask me more details.

Thanks for your time

PS: The excel files are imported from a database. So, if when you open one of these, excel can show a message about risk & blah blah blah, don't worry!! That's because files were downloaded as "excel - data export" .xls

Use this code -

Sub cons_data()

Dim Master As Workbook
Dim sourceBook As Workbook
Dim souceData As Worksheet
Dim CurrentFileName As String
Dim myPath As String

'The folder containing the files to be recap'd
myPath = "D:\DWS\Test" 'PUT UR PATH HERE

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xls")

'Create a workbook for the recap report
Set Master = ThisWorkbook

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set SourceData = sourceBook.Worksheets("Page 1")
    
        With Master.Worksheets("Sheet1")
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("A" & lrow + 1).Value = SourceData.Range("B3").Value
        End With
       
    sourceBook.Close
  
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

End Sub