Excel / VBA: How to copy data from multiple Excel files

Question

I would like to loop through all Excel files in a folder in order to do something with each file (all files have the same layout and only data on Sheet1).

So far I have the following code which gives me a list of the Excel files in a specific folder. What I couldn't figure out myself is how I can copy data from each file - specifically I would need to copy the data in range A10:E50 from each file and then paste it on a page in my current file (all below each other).

Can someone help me with this ?

My current code:

Sub FindFiles()
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add

    Set objFolder = objFSO.GetFolder("C:\Users\mo\Desktop\Test-Import\")
    'ws.Cells(1, 1).Value = "The folder " & objFolder.Name & " contains the following Excel files:"

    For Each objFile In objFolder.Files
        ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = objFile.Name
    Next

    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
End Sub

Many thanks in advance for any help, Mike

Siva · Accepted Answer · 2016-02-04 15:35:40Z

1

Try the below...

Sub FindFiles()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As File
Dim ws As Worksheet
Dim srWS As Worksheet
Dim wb As Workbook
Dim path As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
path = "  " 'Enter your path here
Set objFolder = objFSO.GetFolder(path)
'ws.Cells(1, 1).Value = "The folder " & objFolder.Name & " contains the following Excel files:"

Set ws = Worksheets.Add

For Each objFile In objFolder.Files
    rowCount = ws.UsedRange.Rows.Count
    If (objFile.Type = "Microsoft Excel Worksheet" Or objFile.Type = "Microsoft Excel Macro-Enabled Worksheet") Then
     Set wb = Application.Workbooks.Open(path & objFile.Name)
     Set srWS = wb.Sheets(1)
     srWS.Range("A10:E50").Copy
     ws.Activate
     If rowCount = 1 Then
        ws.Cells(1, 1).Value = objFile.Name
        ws.Cells(rowCount + 1, 1).Select
     Else
        ws.Cells(rowCount + 1, 1).Value = objFile.Name
        ws.Cells(rowCount + 2, 1).Select
     End If
     ActiveSheet.Paste
     Application.DisplayAlerts = False
     wb.Close
    End If

Next
Application.DisplayAlerts = True
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub

edited Feb 4, 2016 at 15:35

answered Feb 4, 2016 at 12:36

Siva

1,1491 gold badge8 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

keewee279 Over a year ago

Thanks a lot for this - I will test and report back shortly.

Siva Over a year ago

Your are Welcome. If your problem is solved. please mark the question as answered. If not let me know where you are stuck at.

Siva Over a year ago

I tested in my machine it worked fine. Try you declaration i.e., Dim objFile As Object

Siva Over a year ago

Are you sure you have excel files in the path you are assigning to 'path'? If So do you have data in Your workbooks-sheet1 in the range you mentioned in the quaetion

Siva Over a year ago

The file from which you are trying to fetch data should be of type 'Microsoft Excel Worksheet' as i coded it that way if your files are.xlsm then you should change it to "Microsoft Excel Macro-Enabled Worksheet"

|

mauek unak · Accepted Answer · 2016-02-04 11:22:12Z

1

try:

Sub FindFiles()
Dim objFolder As String, objFile As String, r As Integer, c As Integer    'r=row, c=column
Dim ws As Worksheet

Set ws = Worksheets.Add
objFolder = "C:\Users\mo\Desktop\Test-Import\"
objFile = Dir(objFolder)
r = 10: c = 1

While objFile <> vbNullString And c < 6
    ws.Cells(r, c).Value = objFile
    r = r + 1
    If r = 51 Then
        r = 10
        c = c + 1
    End If
    objFile = Dir
Wend

End Sub

notice this will list only number of files that fit into A10:E50 (200 files). If you have more than 200 files, macro will not include them. You can remove condition "And c < 6" if you'd like to see more, or edit "r" value to list files on more rows

edited Feb 4, 2016 at 11:22

answered Feb 4, 2016 at 10:29

mauek unak

7922 gold badges11 silver badges31 bronze badges

5 Comments

keewee279 Over a year ago

Thanks for this. I don't see the solution to copy and paste the data here (ranges A10:E50) ? ;)

mauek unak Over a year ago

do you know how many files will be in that folder ? you can't fit variable number of files into static range.

keewee279 Over a year ago

Thanks - it will usually be between 1 and 5, never more than 10 files in the folder.

mauek unak Over a year ago

I updated my answer anyway, let me know if it works for you

keewee279 Over a year ago

Thank you so much - I will test and report back shortly.

Collectives™ on Stack Overflow

Excel / VBA: How to copy data from multiple Excel files

2 Answers 2

15 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

15 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related