I have been trying to create a Excel VB report from a Crystal Reports X program but not having a lot of success. I am new to Visual Basic and trying to learn this new program, Here are the details of my data:
I have data importing first into my Crystal Reports X to give me sales history and sales forecast results and have created a report with 3 Group Headers:
Part No. (Group)
The detail data in Crystal is in this style as follows from left to right column display:
Date Due - Part Description -Customer No.- Qty Del- Total Sales
05/07/2011 - BB-5x1x349mm - 123456789 - 1,200 - £100.01
25/07/2011 - BB-5x1x349mm - 123456789 - 1,200 - £100.01
06/07/2011 - BB-6x2x349mm - 234567891 - 1,234 - £110.02
26/07/2011 - BB-6x2x349mm - 234567891 - 1,234 - £110.02
07/07/2011 - BB-7x3x349mm - 345678912 - 1,345 - £100.03
27/07/2011 - BB-7x3x349mm - 345678912 - 1,345 - £100.03
08/07/2011 - BB-8x4x349mm - 456789123 - 1,678 - £100.04
28/07/2011 - BB-8x4x349mm - 456789123 - 1,678 - £100.04
09/07/2011 - BB-9x5x349mm - 567891234 - 1,123 - £100.05
29/07/2011 - BB-9x5x349mm - 567891234 - 1,123 - £100.05
This is an example of the data I am exporting in Excel and has a Total Sum under the Total Sales Column £1,020.32
This is all fine as the way it works, but my problem I am trying to solve is taking this data that I show, there are 5 different part numbers in this example in Group Header format, each part number here in this example is sold two times in this month. My sales team want the same part number to show only once for that month, with only each header showing once with, then only total qty and total sales for the month to show.
They don't want the same part number to be shown for each and every due date and qty. They only want the total cumulative value. Because in Crystal it tends to default into each single item and in groups.
Then I have exported this into excel, but to show each part no, going down in each row of the excel spreadsheet, all details showing only once for each part and the Sum total of qty and sales?
I have attached the link for the "Format Style Needed - Manual Input Of Data - Original Report.pdf". (http://www.mediafire.com/?wek6ypk7cx44ley
This shows what I am trying to resemble in the Excel report, as close as I can get it and currently this excel report that they use is manually inputted for each and every part ordered and sold.
Also attached is the excel file with the Crystal export data.
So to summarize the my question is how to take each part number and transfer the summary results for each month and create another worksheet in this same file.
Please can anyone help and let me know what additional info you need in how to do this with VB or VB script in order to achieve this.