Friday, March 11, 2011

Recording Macros - Excel 2003



This is a very nice intro to recording excel macro's. By recording excel macro's you can save a lot of time doing repetitive tasks that require identical actions. This video is a good starting point.

In the past I have used a combination of Access and Excel. For example I created a set of queries to pull data from a SQL server. Once the data was pulled from the SQL server access did some complex calculations involving multiple sub-queries.

Once the calculation was completed Access opened an Excel file I created as a template.  Access would dump query output to the template file. Once the Excel file was opened an autoexec marco would run and format the report.  Formatting takes a good bit of time to code using VBa, so I recorded the formatting part and pasted it into the autoexec module.

This saved a ton of time, and all I have to do it press a button to create a report that used to take 3-4 hours to complete. Now it takes 20 minutes.

That's what I call doing more with less (time).

8 comments:

  1. ctrl plus one through nine switches tabs in FF

    ReplyDelete
  2. Great tips Ragnar. Thanks a lot!

    ReplyDelete
  3. Sweet. I actually needed something like this.

    ReplyDelete
  4. Giving javascript a try and creating a simple, yet customizable and interactive html page to fulfill such tasks, seems more useful though. Unless you know your way around VBscript.

    ReplyDelete
  5. This is very helpful. Followed! alphabetalife.blogspot.com

    ReplyDelete