PDA

View Full Version : Excel record aggregation with VBA macro



balaece25
03-14-2014, 08:18 AM
Guys,

I need your help to achieve the below task in an easy way using vba macro.

Requirement details
1. Need to have a report which should list the customer purchase details for each and every day.
(Each sheet should represent one customer)
2. Input file will be given daily with all the customer details.
3. Need a macro file to collate all the input files for a month in input folder and it should update the output file with one stretch.

Lets take if the customer details were given on Feb 15, then we should have atleast 12 input date files in input folder. Our system should process all these files and put an entry for 12 dates in the impacted customer sheets in output file.

The input file will be in below format.(Separate file for each day)

Code:
Customer Id, Product Name, Recived Qty, Rejected Qty
The output file will be in the below format

(Each worksheet represents each customerId)

Each sheet contains the below strtucture
Code:
Row1: CustomerName:
From Row 2:

Date,ProductName,TotalReceivedQty,TotalRejectedQty (*ColumnName Assumptions)

Thanks in Advance.

Cheers
Bala

westconn1
03-15-2014, 04:41 PM
this is incomplete partly because i ran out of time and partly because i was unsure of desired result
i thought i would post anyway as it may give some starting point for you

Set target = ThisWorkbook ' assumes code is in the output file
mypath = "c:\temp\" ' change to suit
fname = Dir(mypath & "*.xls")
Do While Len(fname) > 0
Set wb = Workbooks.Open(mypath & fname)
Set ws = wb.Sheets(1)
For Each cel In ws.Range("a:a")
If IsEmpty(cel) Then Exit For ' cease on empty row
If Not cel.Row = 1 Then 'do not process headers
dt = Format(ws.Name & " " & Year(Now), "dd/mm/yyyy") ' change format to suit
For Each sht In target
If sht.Cells(1).Value = cel Then Exit For ' find sheet to match customer ID
Next
If sht Is Nothing Then
Set sht = target.Sheets.Add
sht.Name = cel ' sheet names not specified in post, change to suit
sht.Cells(1) = cel
End If
With sht
' here you would need too determine where to insert the data from the day sheet



End With
End If
Next
wb.close
fname = Dir
Loop
the workbooks may not be opened in any specific order, if you want the day to be added in date order, it maybe necessary to make a sorted (by date) list of workbooks in an array, the loop through the array

assuming you can have multiple lines in a day file for same customer, same product, do you want them summarised to a single line?

SamT
03-16-2014, 12:35 PM
To sort by date. open by date


Sub Test()
Dim i As Date
StartDate = DatePicker
StartCnt = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
EndCnt = DateSerial(Year(Date), Month(Date), Day(Date))

For i = StartCnt To EndCnt
Filename = Path & Format(i, "MMMd") & ".xlsx"
Dir Filename
'
'
'
Next i
End Sub


@ Balaece,
You have a daily Report Folder "Input folder". Is the Name a constant or is it like "2014Apr"?

What would the result of these two Sheets/Workbooks look like"



Feb1






CustomerId

Product Name
Received Qty
Rejected Qty


1
AAA
3
1


2
BBB
5
2


1
CCC
6
3


1
AAA
7
4



2
AAA
8
5








Feb2





CustomerId
Product Name
Received Qty
Rejected Qty


3
AAA
6
5


2
BBB
7
4


1
CCC
8
3


4
AAA
9
2


5
AAA
10
1