Macro for Opening files and copy the contents of the File

**ravichandavar** · 08-13-2012, 08:09 PM

Hi Team,

I need a macro which opens the files from given path and copy and paste the contents in one file.
For example I have 30 files in a folder and the path is mentioned in macro file, Using the given path macro will consolidate the data in a file.In case if one file is missing out of 30 it should go to next file...

Please help me in this regard.

Regards,
Ravi.C

**Excel Fox** · 08-13-2012, 08:50 PM

**ravichandavar** · 08-13-2012, 09:05 PM

Originally Posted by Excel Fox

You might want to give a little more detail. Are all these files Excel files? Do you want to consolidate them in to a single Excel file (in one worksheet, or multiple worksheets)? Do you want to copy the entire content? Should the headers be copied all the time if they are available?

Hi,

Yes all files are excel files, and i want a consolidated data in one worksheet including the Headers.i.e all contents of the files.

Regards,
Ravi.C

**Admin** · 08-13-2012, 09:55 PM

Hi Ravi,

Try this one. Adjust sheet name.

Code:

Dim dic             As Object
Dim Counter         As Long
Sub kTest()
    
    Dim r           As Long
    Dim c           As Long
    Dim n           As Long
    Dim j           As Long
    Dim Fldr        As String
    Dim Fname       As String
    Dim wbkActive   As Workbook
    Dim wbkSource   As Workbook
    Dim Dest        As Range
    Dim d, k()
    
    '// User settings
    Const SourceFileType        As String = "xls*"  'xls,xlsx,xlsb,xlsm
    Const DestinationSheet      As String = "Sheet1"
    Const DestStartCell         As String = "A1"
    '// End
    
    Application.ScreenUpdating = False
    Counter = 0
    With Application.FileDialog(4)
        .Title = "Select source file folder"
        .AllowMultiSelect = False
        If .Show = -1 Then
            Fldr = .SelectedItems(1)
        Else
            GoTo Xit
        End If
    End With
    
    
    Set dic = CreateObject("scripting.dictionary")
        dic.comparemode = 1
    Set wbkActive = ThisWorkbook
    ReDim k(1 To 50000, 1 To 100)
    Set Dest = wbkActive.Worksheets(DestinationSheet).Range(DestStartCell)
    Fname = Dir(Fldr & "\*." & SourceFileType)
    Do While Len(Fname)
        If wbkActive.Name <> Fname Then
            Set wbkSource = Workbooks.Open(Fldr & "\" & Fname)
            With wbkSource.Worksheets(1)
                d = .Range("a1").CurrentRegion
                UniqueHeaders Application.Index(d, 1, 0)
                For r = 2 To UBound(d, 1) 'skips header
                    If Len(d(r, 1)) Then
                        n = n + 1
                        For c = 1 To UBound(d, 2)
                            If Len(Trim$(d(1, c))) Then
                                j = dic.Item(Trim$(d(1, c)))
                                k(n, j) = d(r, c)
                            End If
                        Next
                    End If
                Next
                Erase d
            End With
            wbkSource.Close 0
            Set wbkSource = Nothing
        End If
        Fname = Dir()
    Loop
    
    If n Then
        Dest.Resize(, dic.Count) = dic.keys
        Dest.Offset(1).Resize(n, dic.Count) = k
        MsgBox "Done", vbInformation, "ExcelFox.com"
    End If
Xit:
    Application.ScreenUpdating = True
    
End Sub
Private Sub UniqueHeaders(ByRef DataHeader)
    
    Dim i   As Long
    Dim j   As Long
    
    With Application
        j = .ScreenUpdating
        .ScreenUpdating = False
    End With
    
    For i = LBound(DataHeader) To UBound(DataHeader)
        If Len(Trim$(DataHeader(i))) Then
            If Not dic.exists(Trim$(DataHeader(i))) Then
                Counter = Counter + 1
                dic.Add Trim$(DataHeader(i)), Counter
            End If
        End If
    Next
    
    Application.ScreenUpdating = j
    
End Sub

**Rick Rothstein** · 08-13-2012, 10:00 PM

Originally Posted by ravichandavar

Yes all files are excel files, and i want a consolidated data in one worksheet including the Headers.i.e all contents of the files.

1. Is the structure for each file identical (same headers in the same order)?

2. Do all the files consist of only a single worksheet each?

3. Is the order the files are consolidated in important (in other words, is the final order of the data important)?

4. The Headers... are the all on Row 1 only?

**ravichandavar** · 08-13-2012, 10:19 PM

Originally Posted by Rick Rothstein

1. Is the structure for each file identical (same headers in the same order)?

2. Do all the files consist of only a single worksheet each?

3. Is the order the files are consolidated in important (in other words, is the final order of the data important)?

4. The Headers... are the all on Row 1 only?

Hi Rick,

Yes,all the points you mentioned above is right.

Regards,
Ravi.C

**ravichandavar** · 08-15-2012, 10:59 AM

Hi ,

Thanks for the quick reply.I just copied the codes you have provided it runs fine but I'm not able to get the consolidated data in one file.
Where these data are getting copied and one more thing is i need data from first sheet of every file in the folder in a sheet.

Thanks & Regards,
Ravi.C

**Admin** · 08-15-2012, 11:33 AM

Hi Ravi,

Have you changed the required changes in the code?

By default it consolidates all the data in Sheet1 of the workbook where you placed the code.

**ravichandavar** · 08-15-2012, 01:36 PM

Hi Yes,

I did that but still its not working.One more thing Suppose my folder contain 3 or 4 files and from these files data from second file should come down the data's of first file subsequently 3d and forth.

For example: Files of the folder contains data in A1:T100 And i need to paste this in my consolidation file as A1:T100 contains first file data and A101 to T200 should take data from second file and so on...

Hope you might have understood my requirement.

Please find the attached file....

**Admin** · 08-15-2012, 03:14 PM

Originally Posted by ravichandavar

Hi Yes,

I did that but still its not working.

No. You didn't.

Place the following 2 lines outside the procedure like I posted above.

Code:

Dim dic             As Object
Dim Counter         As Long

and try to run again.

Thread: Macro for Opening files and copy the contents of the File

Thread Tools

Search Thread

Display

Macro for Opening files and copy the contents of the File

Similar Threads

Macro for copying the contents of a cell to the Windows clipboard

Macro to copy data from a set of excel files

Macro to create files as per the contents in a column

Protect Excel File From Opening Only In Designated Computers

Opening a Pdf file using VBA

Bookmarks

Bookmarks

Posting Permissions