Excel Macro

[homer15]

hey everyone,

i was wondering if anyone would be interested in helping me write a macro. it involves taking comma seperated files, merging them into one excel spreadsheet and sorting them. the two files would be, like cost and revenue, for example. i would like the cost of an object to match up with its revenue, so i can see how much is being spent on something as apposed to its cost, percentage wise. i know the basics of basic, but i don't know how to get it to work in a spreadsheet; but it's probably some simple task that i don't know about. if nobody wants to help, that's cool, but it would be really nice if someone could show me a few tricks or something. if anyone is interested just pm me.

homer

[David_Jones]

Hi,

You could use a macro for this, but I don't think you really need to.

Making some assumptions here:

- This is a regular requirement, not a one off need.
- The files are always in a standard format, and can be given a standard name each period.

If so, then you should be able to query the CSV files directly from Excel, and bring in the data to, for example, two sheets in an 'analysis' spreadsheet.

From there, you could have a third sheet that pulls the revenue and cost data from the data sheets and matches them together.

In reality, you don't HAVE to have the data sheets, but I normally do it that way so that I can pull the data in once, and then just use the spreadsheet file without having links updating (speed / time issue only).

Hope that helps,

David.

[homer15]

thanks david_jones.
i do need a macro for it because i'm going to be doing it on a regular basis.

[homer15]

here's an example of a file i'd use. i know how to get them imported just fine, but beyond that i'm lost. if anyone could help, it would be greatly appreciated. (it's a zipped xls file.)

[PMich]

I quit writing excel macros a while ago because it is just as easy to record them. Go to tools--> Macro --> record new macro. Once you have begun recording the macro do what you need to do by hand and then stop recording when you are done. You may need to do a little tweaking to get it just right but I still think its quicker than writing them. Besides, you can go back and view the script associated with the macro after you have recorded it and learn the commands that way. That was how I learned, but I eventually abandoned writing for recording for most things.

[homer15]

thanks PMich

there are some things that i'm asking that i know can't be recorded, like if you look at the example, there are a couple of fields where some items didn't have any cost for the month, or didn't generate any revenue. since they cost one's don't have a revenue counterpart, it would be hard when recording a macro to have seperated properly.

it starts off like:

cost		rev
022a	25.00	022a	230.00
025a	17.00	025a	150.00
025b	33.00	029a	170.00
027a	13.00

and i would want something like:

cost		rev
022a	25.00	022a	230.00
_
025a	50.00	025a	150.00
_
027a	13.00	027a	000.00
_
029a	00.00	029a	170.00

where the cost of the like entries would be combined, since the revenue is only gauged on the A segments.

[PMich]

homer15
Give me a couple of days and I'm sure I can come up with something. Off the top of my head, their may be some filtering we can do to sort it. Probably won't have time to play with it until Friday night but bear with me and I'll get to it. If you could send me a pseudo file or files and a few more specifics via email at pmichene@uiuc.edu that would be a big help. Do you have any programs other than excel at your disposal?

[homer15]

thanks pmich, that would be really cool. anything to help me get started. i could mail you the files i'm using tomorrow. i have the whole office suite available, but what other programs would you suggest i try?