consolidate data from multiple excel files into single file

[hkk]

Dear Friends,

Can someone help me with below? How can I modify below code to consolidate 8 data files into 1 file using a similar setup as "SplitRpt" .
Please help me in this context since I am incompetent in VBA programming.

Private Sub SplitRptByPackage()
    Dim fXLSFile As String
    'Dim nRow As Integer
    Dim iRow As Integer
    Dim custcode As String
    Dim colvalue As String
    Dim coltitle As String
    Dim site_cnt As Integer
    Dim site_name As String
    Dim colDest As String
    Dim ext_rpt As Worksheet
    Dim wip_rpt As Worksheet
    Dim CustWIP As String, tCustWIP As String
    ' Loop for 2 sites
    For site_cnt = SiteStart To SiteCount
        If site_cnt = 1 Then
            site_name = "M"
            webwipext_txt = WEBWIPEXT_TXT_M
            If Not fMOK Then GoTo skipNextSite
        Else
            site_name = "S"
            webwipext_txt = WEBWIPEXT_TXT_S
            If Not fSOK Then GoTo skipNextSite
        End If
        ' WIP report name
        If vTestRun Then
            fXLSFile = site_name & pfName & "_" & TestRunSession & ".xls"
        Else
            fXLSFile = site_name & pfName & ".xls"
        End If
        
        ' Workplace worksheet
        Worksheets("SplitRpt").Select
        Set ext_rpt = ActiveWorkbook.ActiveSheet
        
        ' Get the Split info customer list start row
        iRow = 2
        nVal1 = 0
        ' Loop until end of the list
        Do Until ext_rpt.Cells(iRow, 1) = ""
            ' Get setup info from SplitRpt worksheet
            custcode = ext_rpt.Cells(iRow, 1)
            coltitle = ext_rpt.Cells(iRow, 2)
            colvalue = ext_rpt.Cells(iRow, 3)
            colFilename = ext_rpt.Cells(iRow, 4)
            nVal1 = InStr(1, colvalue, ",")
            If nVal1 > 0 Then
               colPkg = Mid(colvalue, 1, nVal - 1)
            Else
               colPkg = Trim(colvalue)
            End If
            ' Skip other customer code if not in cust list (manual run only)
            If manual_by_cust <> "" And InStr(manual_by_cust, custcode) = 0 Then GoTo skipNextCust
            
            ' Open Customer WIP which need to split if exist otherwise skip to next customer
            CustWIP = FDIR & custcode & "\" & fXLSFile
            If Not FileExists(CustWIP) Then GoTo skipNextCust
            Debug.Print "UpdExtInfo:: " & custcode & " for " & site_name & "-Site"
            ' Open customer WIP
            Workbooks.Open filename:=CustWIP
            ' WIP worksheet
            Set wip_rpt = ActiveWorkbook.ActiveSheet
            
            colDest = "*" & colPkg & "*"
            If findColumn(colDest, wip_rpt) > 0 Then
               Application.DisplayAlerts = False
               SaveFileName = site_name & pfName & colFilename & ".xls"
               SaveAsFileName = FDIR & custcode & "\" & SaveFileName
               ActiveWorkbook.SaveAs filename:=SaveAsFileName, FileFormat:=xlExcel5, _
                  Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
                  
               Windows(SaveFileName).Activate
               Set cust_rpt = ActiveWorkbook.ActiveSheet
                
               '-- Loop to remove unwanted pacakge
               l_cnt = 11
               Do Until cust_rpt.Cells(l_cnt, 1) = "DEFINITIONS OF TERMS:"
                  If (cust_rpt.Cells(l_cnt, 3) Like colDest) Or _
                     (cust_rpt.Cells(l_cnt, 3) = "") Or _
                     (cust_rpt.Cells(l_cnt, 3) = "Grand Total") Then
                     Cells(l_cnt, 30) = ""
                  Else
                     If (cust_rpt.Cells(l_cnt, 3) = "TBA") Then
                       nVal1 = 0
                       nVal1 = InStr(1, cust_rpt.Cells(l_cnt, 4), "-" & colPkg)
                       If nVal1 > 0 Then
                          Cells(l_cnt, 30) = ""
                       Else
                         nVal1 = 0
                         nVal2 = 0
                         nVal1 = InStr(1, cust_rpt.Cells(l_cnt, 4), "-SC70")
                         nVal2 = InStr(1, cust_rpt.Cells(l_cnt, 4), "-SOT")
                         If (nVal1 = 0 And nVal2 = 0) And colPkg = "SC70" Then
                            Cells(l_cnt, 30) = ""
                         Else
                            Cells(l_cnt, 30) = "DEL"
                         End If
                       End If
                     Else
                       If (cust_rpt.Cells(l_cnt, 3) = "TBA Total") Then
                          Cells(l_cnt, 30) = ""
                       Else
                          Cells(l_cnt, 30) = "DEL"
                       End If
                     End If
                  End If
                  l_cnt = l_cnt + 1
               Loop
               
               '-- Perform deletion
               l_cnt = 11
               Do Until cust_rpt.Cells(l_cnt, 1) = "DEFINITIONS OF TERMS:"
                  If cust_rpt.Cells(l_cnt, 30) = "DEL" Then
                     vAdd = l_cnt & ":" & l_cnt
                     Rows(vAdd).Select
                     Selection.Delete Shift:=xlUp
                     l_cnt = l_cnt - 1
                  End If
                  l_cnt = l_cnt + 1
               Loop
               
               'Close the wip repot
               cust_rpt.Select
               ActiveWorkbook.Save
               ActiveWorkbook.Close
            Else
               ActiveWorkbook.Close
            End If
skipNextCust:
            iRow = iRow + 1
        Loop
skipNextSite:
    Next site_cnt
End Sub

**************************
"SplitRpt" Sheet Content

CUST SPLIT BY VALUE APPEND_FILENAME REMARK
AVG PKG SC70 _SC70
AVG PKG SOT _SOT

Suggesting setup as below :
Every cust has one folder each with its own data. Need to consolidate all 8 cust excel sheet into 1 sheet by maintaining the existing folders and data for each cust. Which means every cust folder will still have separate cust data but under each folder will also have an additional sheet of the consolidated version.How can I modify the above code to achieve this?

CUST APPEND_FILENAME
SGC _ST
SGG _ST
SGF _ST
SGS _ST
SGT _ST
SGE _ST
SGU _ST
SGR _ST

Thank you in advance.

[SamT]

hkk,

Welcome to the forum, I hope we give you more help than you expect. Sometimes we get so busy that a new thread "falls off" the first index page, but someone will always go through the older pages looking for un-answered threads like yours. In the future, If you don't see your thread on the main index page, AND, it has been more than 24 hours since you posted it last, you are allowed to make a "bumping" post to it. A personal reply to your own post that simply says "Please" will be enough to put it back on the main index page.

Every cust has one folder each with its own data. Need to consolidate all 8 cust excel sheet into 1 sheet by maintaining the existing folders and data for each cust. Which means every cust folder will still have separate cust data but under each folder will also have an additional sheet of the consolidated version.How can I modify the above code to achieve this?

By "sheet," I assume that you really mean Workbook. A Workbook can contain many Sheets. A Sheet is sometimes called a "Tab" in a Workbook. Technically a Sheet can be a Worksheet or a Chart (sheet,) but unless otherwise specified, we all use "Sheet" to just mean "Worksheet."

For now, we'll ignore the code you posted because it is not doing the job for you. We may come back to it later.


___________________________________________________________________________ _____________
In order to code for the job, we need to know exactly what the job is because code only does exactly what it is "told."

You have many customer folders. We'll need to know the path to the folder that contains all the customer folders. Each Customer Folder contains many xl files (8 ATT) that you want to extract data from. We will also need to know the Naming Pattern for the Customer folders.

We will need to know how the data is laid out in a sample workbook. We need to see how you want the data laid out in the consolidation Workbook. We also need to know the Naming Pattern for the Customer Workbooks.

Please prepare a sample Workbook one Worksheet, Tab named "Result" showing how you want the consolidated data laid out and copies of all Customer Worksheets that data is to be extracted from. Color the cells that contain data to be extracted.

Please remove all personal and proprietary information from the Sample.

In your reply, Use the "Go Advanced" button and "Manage Attachments" to upload the Sample Workbook.
Include in your post, the Path to the Main Customers folder and a sample Name of an individual Customer folder, Use "Sample" instead of the actual customer name. Also give us an example of a Customer Workbook Name, including extension.