Macros?

[Brien]

I have been asked to streamline some time consuming data analysis (as I
have mentioned with a different thread about a different question) and
I'm wondering if Macros can help me.

The manual process involves creating Excel documents from 4 CSV
documents, then lots of cutting and pasting of values from one location
to another. Then a few calculations are made and put into pretty graphs
before being exported to word as a finished document.

How much of this can be automated? Are macros the way to go? As far as I
can tell, Macro's are embedded in a document aren't they? How can I
create a macro of the entire process and then let a new document be made
to do the whole thing again with 4 different CSV files? And then
again, from scratch etc.

I hope this is making sense. I'm new to this and been thrown in at the
deep end. My brain is hurting.

Brien

 

[Bernie Deitrick]

Brien,

The short answer is "Yes", though your brain will likely hurt a bunch more
before you are done. I do such things as that all the time, and macros can
convert what might have started as a day's worth of work into - literally -
a few seconds of run time. Macros can do anything that you can, though
translating your workflow to a workable macro requires some experience and
specific knowledge. Given that, I would recommend that you consider hiring
an experienced consultant - there are quite a few MVPs (not me, but others)
who do just that sort of thing all the time. Post back if you are
interested, and I will send you their email addresses.

If you are interested in doing it yourself, then you have a wonderful
opportunity to learn a lot of VBA in a short time. The macro recorder will
get you code to look at, and then you can post very specific questions here
along the lines of "How do I get Excel to open four user-selected CSV files
at a time using a macro?"

HTH,
Bernie
MS Excel MVP