PDA

View Full Version : consolidate data from multiple excel files into single file



hkk
08-21-2013, 08:12 PM
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
08-24-2013, 08:18 AM
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.