Beyond Excel: VBA and Database Manipulation

Dec 15 2009   6:13PM GMT

75% Done

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

As far as writing reports in Excel is concerned, using just what we’ve discussed up til now get’s us about 75% of where I want to take us.  The next 25% is more tedious to work through, but the advantages are worth it.

Quick Change SQL
The first advantage is simplifid SQL management.  This is achieved by listing database tables and fields/columns in an Excel table (called Fields), then using code to assemble the SQL for us.  With the field names in an Excel table, we can sit down with the user and quickly customize their report.  We can easily change the column order, include more fields, drop fields, change the column headings, change sort sequences, add formulas, add formats, and chose a field to freeze the panes on – all without coding – well – that is – without additional coding beyond the library of routines needed to support this.

Excel Formulas
Adding formulas is huge.  It plays to Excel’s unique strengths.  What if our customer wants to know what day of the week orders hit most?  That isn’t in the database.  The date is.  But to get the day of the week requires a formula, one that happily already exists in Excel.  What if our customer wants to know the aging of his receivables.  The age isn’t in the database.  But the date is.  And happily, subtracting the database date from today is a snap in Excel. 

One of the routines we will add facilitates inserting complex Excel formulas from our Fields table into our retrieved records.

SQL Formulas
I find that SQL formulas work best for making odd date fields standard.  I’ve seen Julian dates, seconds elapsed since some beginning date dates, year/month/day dates all crammed together in one numeric field, date and time stamp dates when all you want is the date, and the AS/400’s (iSeries, Power i, …)  system dates with a century flag.  Users don’t like them.  They want standard dates.  Excel doesn’t like them.  Excel’s formulas only work with standardized dates.  

One of the routines we will add facilitates inserting complex SQLformulas from our Fields table into our retrieved records.

Excel has rich formatting capabilities.  This can really improve the readability of our results.

We will add routines to apply numeric formatting, left/right/center align data, wrap data, hide columns, limit column widths, and conditionally grey out repetitive data in rows from values in our Fields Table.

Often SQL sorts data just fine.  But there are two reasons not to use it.   First, why burden your database server with that task when your PC’s processor is dedicated to you alone?  Let Excel do it.  Second, what if the sort order you want is based on a formula result?  Excel can sort that too.

We will add routines to sort in any combination of orders and with as many columns as you have fields identified in our Fields Table.

When working with large databases, it is important to keep the column headings always in view.  You may also want to keep some of the row keys in view as well.  Excel’s “Freeze Pane” function handles this nicely.

We will add a routine to apply freezing to any field in our Fields table.

Future Use
When we get into updating databases from Excel, (assuming your auditors allow such heresy) we will need some additional functionality from our Fields table, like:

  • Lock certain fields from changes
  • Make some fields required
  • Validate fields against tables
  • etc.

Next post will show what this table looks like, then we’ll get down to adding more routines to our library to deliver on its promise.

 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: