How to Create VB Report From Exported Data in Excel

[bstafford21]

Hi,
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:

Customer (Group)
Date (Group)
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.
(

http://www.mediafire.com/?mcsq6n40uis4653)

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.

 

[SkipVought]

hi,

So are you coding this is VB5 or 6 or are you coding in Excel VBA?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW,

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.

This can quite easily be done in Excel (without any code) using the PivotTable feature.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bstafford21]

Hi Skip,

Really appreciate the response, originally was going to use VB Script, then tried in Excel VB.

I have briefly thought about using pivot table, just was not sure if I could get the same results. I might need a little coaching, using Excel 2007 and have done a little bit of experimenting, but could not see getting into the same format as the example I showed .

Would visual basic do a better job than pivot or pivot better?

Thanks

 

[SkipVought]

Many of us cannot download data during the day due to company restrictions.

If you are coding in VB Script, then you ought to be posting in forum329.

If you are coding in any of the MS Applications (VBA), then you ought to be posting in forum707.

If you want some help with native Excel features, then you ought to be posting in forum68.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bstafford21]

Okay, maybe why I have not had any replies since I posted. Knowing the difference btwn the different types of coding would have helped, feel a bit ignorant, but just starting out.

Not realized there were that many different code development using similar names, so have much to learn. This is probably an obvious, maybe stupid question, but how do you know what type you should use for the result? Programmer preference, software being used with your data, maybe all of the above?

Thanks for the links and trying to guide me to the right place.

Bill

 

[SkipVought]

Depends on your prefernce, many times.

But I would ask the following questions.

Where does the data that CR gets, massages & exports to Excel, reside?

If CR can get to it, why not get it in another application or program to eliminate a step?

Where should the data finally reside for the ultimate consumer, and/or analyzer/consumer?

If the latter is Excel, then I would do the thing in Excel. You could access the same database that CR accesses and produce the desired result all in one place.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bstafford21]

So with the database that my data resides in, which is Magic e-developer and I could use ODBC connection instead. I only am using the CR because it was recommended to me when I started using our Magic program. So I know CR pretty well, just easier for me.

I would like to learn Pivot tables better if that would be the best way to achieve the results as that is the program of choice for the Sales team.

Thanks again for your help.

Bill

 

[SkipVought]

Try posting your question in forum68. Then if it becomes apparent that code will be required, it can be accomodated either there of in forum707.

You will get help ahd advice regarding PivotTable and MS Query (ODBC) in Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

I need to add, since you stated: "I am new to Visual Basic and trying to learn this new program" that you may be talking about VB.NET (yet another language, since 'calssic' VB6 is not new any more...). If that's the case, you may get better help at forum796

Sorry for adding more to the confusion, but the more you know....

Have fun.

---- Andy

 

[bstafford21]

Thanks to both for the input, will start looking for the best way, I do like learning new ways, hope I can accomplish it.

Cheers

Bill

 

[SkipVought]

Also attached is the excel file with the Crystal export data.

I almost think that either CR ought NOT be able to export reports of this kind to Excel, an analysis tool, or have the report designer hanged by his thumbs, for not exporting data that can be analyzed in Excel without mucho incantations!

That report will be a mess to wack and cajole into a useable table format. I would DEFINITELY be looking for a 'get the data myself' solution and ignoring THAT CR report! Or design the CR report to produce EXACTLY what your user wants!

The requirement as stated is fool-hearty IMHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]