Consolidating Data from >20 workbooks, each workbook Containing >70 worksheets

November 17, 2008 at 01:15 · Filed under Automation, VBA, VBA Code Library, VBA Examples

- Selva V Pasupathy, HSBC Global Resourcing, Hyderabad

Option Explicit
‘___________________________________________________________________________
‘***************************************************************************
‘*
‘* MODULE NAME:     Copy Data from all Excel Files in a Folder
‘* AUTHOR:          Selva V Pasupathy, HSBC Global Resourcing, Hyderabad
‘*
‘* CONTACT:         socko@rediffmail.com
‘* WEB SITE:        http://socko.wordpress.com
‘*
‘* DESCRIPTION:     The following visual basic code was written when
‘*                  I wanted to consolidate the data from 26 workbooks
‘*                  and in each workbook, there were are 77 sheets. It
‘*                  would have been a very difficult task, if I had to
‘*                  do it manually. I believe this procedure will be
‘*                  useful to somebody else
‘* NOTES:
‘*  Before using the codes, you can change all the variables like,
‘*  Folder, File , SearchString, SearchSubfolders (true / false),
‘*  and other variables.
‘*————————————————————————
‘* Other Information
‘*
‘* UPDATES:
‘*  DATE            COMMENTS
‘*  16 Nov 2008     You are free to use , change, and modify this code.
‘___________________________________________________________________________
‘***************************************************************************

Function ListFiles(sCount As Integer, ByVal sFldr As String, _
                bFldr As Boolean, sFileName As String) As String
Dim fCnt As Integer

    With Application.FileSearch
        .NewSearch
        .LookIn = sFldr
        .SearchSubFolders = bFldr
        .Filename = sFileName             ‘”*consolidated*.xls”
        .FileType = msoFileTypeAllFiles
        
        If .Execute() > 0 Then
            
            If .FoundFiles.Count > sCount Then
              
              For fCnt = 1 To .FoundFiles.Count
                  
                  If fCnt = sCount Then
                    
                    ListFiles = .FoundFiles(fCnt)
                  
                  End If
              
              Next i
            
            Else
            
            ListFiles = “”
            
            End If
        
        Else
            
            MsgBox “There were no files found.”
        
        End If
    
    End With

End Function

Sub Consolidate_Data()
Dim cFile As Integer
Dim sht, rw, cl, avLastRow As Long
Dim myFileName, myFolder, sFileName As String
Dim myFile As Workbook
Dim sFldr As Boolean

myFolder = “J:\GB Project\CONSOLIDATED”
sFldr = False
sFileName = “*consolidated*.xls”

With ThisWorkbook.Sheets(“data”)
  For cFile = 13 To 100
    myFileName = ListFiles(cFile, myFolder, True, sFileName)
    If myFileName = “” Then Exit Sub
      Set myFile = Workbooks.Open(myFileName)
      ThisWorkbook.Activate
      ThisWorkbook.Sheets(“data”).Activate
        For sht = 2 To myFile.Sheets.Count
          For rw = 1 To 100
            avLastRow = .Cells(65536, 1).End(xlUp).Row + 1
            ThisWorkbook.Sheets(“data”).Cells(avLastRow, 1).Activate
            .Cells(avLastRow, 1) = myFile.Sheets(sht).Name
            For cl = 1 To 18
            Application.StatusBar = “Writing File# ” & cFile & _
                    ”  Sheet# ” & sht
            .Cells(avLastRow, cl + 1) = myFile.Sheets(sht).Cells(rw, cl)
            Next cl
          Next rw
        Next sht
  myFile.Close False
  ThisWorkbook.Save
  Next cFile
End With
End Sub

Leave a Comment

You must be logged in to post a comment.