Beyond Excel: VBA and Database Manipulation:

October, 2009


October 31, 2009  10:05 AM

Wrapping things up

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Today I'm introducing a "wrapper" for our Pivot Table and Pivot Chart routines.  The wrapper isolates setting unique parameters from the main routine that extracts data.   Though the routine looks lengthy, it contains no logic, only parameters and instructions on how to modify it .  The...

October 29, 2009  4:26 PM

Building a Library of Routines – Setup_PivotChart

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In our last post we added a function that creates Pivot Tables.  Pivot Tables are fantastic for summarizing data and automatically adding "drill down" functionality.  But people usually like to see things graphically.  The creators of XL realized this and provide functions for graphing Pivot...


October 28, 2009  6:43 PM

Building a Library of Routines – Setup_Pivot

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

This next function is the heart and soul of creating Pivot Tables.  It's purpose is to reduce the many Pivot Table properties and methods down to just one function and as few meaningful parameters as possible to create every Pivot Table my customers have every asked for.  Hopefully, your...


October 23, 2009  4:45 PM

Building a Library of Routines – Settings

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Last post we added some small routines that tell us if various things exist so we can avoid trying to recreate what's already there.  That avoids errors and speeds things along.  Another speed enhancer for just about any VBA routine is to shut off screen updating and calculations.  And if you...


October 22, 2009  4:26 PM

Building a Library of Routines – ?Exists

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

We are early in the process of building a library of routines that will make creating analytical reports from database extracts a snap.  In our last post I explained the basic template that provides the skeleton for just about every function I write.  Today, we are adding our first functions...


October 21, 2009  4:36 PM

Building a Library of Routines – Template

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Up until now, I have been showing you how to record and modify macros to bring data into a spreadsheet, format it, display it in a pivot table, and chart it.  While this method works, it leaves some housekeeping problems to be solved everytime you do it.  Enough.  This post starts us on the...


October 17, 2009  4:15 PM

Pivots and Charts

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

So far, we have simplified listing data in XL.  For some XL users, that might be enough.  But for most users, this is only slightly better than printing data on green bar paper.  To really wow them, we need to leverage two of XL's built in functions, Pivot Tables and...


October 14, 2009  9:05 PM

A Little Polish

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post we cleaned up what the macro recorder generated.  Today we fix one more user interface (UI) problem.  Today we will replace the generic “Enter Parameter Value” with a more meaningful input box.  Here is what it will look like:


October 10, 2009  11:11 PM

Brain Surgery

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

In the last post we added an "easy" button to "easily" kick off our macro.  Today we're going to do a little brain surgery on the macro we recorded.  We will:

  • Change the way we clear the worksheet. 
  • Remove properties from the QueryTable statement that aren't...


October 6, 2009  7:04 PM

Code for “easy” Button

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

Put an "easy" button in your spreadsheets to facilitate launching any macro. Welcome new readers.  We've been talking about how to bring data into XL.  This post introduces the first of many standard routines I use to make assembling real data analysis tools in minutes.  It's a little...


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: