import data from multiple closed files to master file

**majime01** · 02-22-2017, 02:05 AM

Greetings all,

I have been searching and have not quite found an answer for my project. I want to import data from multiple closed excel files into one master file that is open.
1. All files (source and target) will reside in one folder.
2. I would like to import from all files regardless of their name if that is possible.
3. I want to import data from cells A1:C40.
4. I want to import data to one sheet that will add the subsequent data below the previous.

Your help is greatly appreciated.

Ken

**jolivanes** · 02-22-2017, 02:41 AM

You could try this.

Sub Maybe_Like_So()
    Dim wb As String
    
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
            With Workbooks(wb).Sheets("Sheet1")
                .Range("A1:C40).Copy ThisWorkbook.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End With
            Application.CutCopyMode = False
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    
    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Or maybe so

Sub Maybe_Like_So_Values_Only()
    Dim wb As String

    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    wb = Dir(ThisWorkbook.Path & "\*")
    Do Until wb = ""
        If wb <> ThisWorkbook.Name Then
            Workbooks.Open ThisWorkbook.Path & "\" & wb
                ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(40, 3).Value = Workbooks(wb).Sheets("Sheet1").Range("A1:C40").Value
            Workbooks(wb).Close False
        End If
        wb = Dir
    Loop
    
    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
End Sub

**majime01** · 02-22-2017, 08:57 AM

Thank you for your post. I used the second code and it worked great...thank you so much.

**apo** · 02-22-2017, 04:22 AM

Another possibility..

* Build a 2d array from the source data from each workbook in the folder and dump it at the end..

bah.. can't post the code as Succuri firewall thingo is blocking it.. see attached file...

Change strpath as needed..

**majime01** · 02-22-2017, 08:58 AM

Thank you for your post.

**jindon** · 02-22-2017, 05:33 AM

majime01
ブックは閉じたままで...

Sub test()
    Dim fn As String, wbName As String
    Const myRng As String = "A1:C40", myWs As String = "Sheet1"  '<- 適時変更
    fn = Dir(ThisWorkbook.Path & "\*.xls")
    Do While fn <> ""
        If fn <> ThisWorkbook.Name Then
            With ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2).Range(myRng)
                .Formula = "='[" & Mid$(fn, InStrRev(fn, "/") + 1) & "]" & myWs & "'!" & Split(myRng, ":")(0)
            End With
        End If
        fn = Dir
    Loop
    With ThisWorkbook.Sheets(1).Range(myRng).EntireColumn
        .Value = .Value
    End With
End Sub

**majime01** · 02-22-2017, 08:59 AM

Arigato tomadachi

import data from multiple closed files to master file

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Re: import data from multiple closed files to master file

Thread Information

Users Browsing this Thread

Similar Threads

Combine data from multiple files into a master file using VBA

Help with data import from files to master file

Vba macro help to pull data from multiple closed files to master file !!!

[SOLVED] Import data from excel files to append master file

Consolidate Data from Multiple Files (Different File Names) into a Master File

[SOLVED] A macro to Import data from many unique files into one master file

Searching other files to import to specified cells in a master file

Bookmarks

Bookmarks

Posting Permissions