Beyond Excel: VBA and Database Manipulation:

odbc


November 7, 2009  10:35 PM

Using ADO to Read a Database



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

Up till now, we've used XL's QueryTable object to retrieve records from databases.  This post breaks away from the QueryTable to leverage the power of ADO.  One of the advantages of ADO is that we can create user defined functions to use directly in XL just like any normal XL function. As...

November 3, 2009  6:07 PM

Delivering a Finished Product



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

Last post I introduced the wrapper for creating PivotTables and PivotCharts.  This wrapper isolates the changes you must make to add PivotTables and PivotCharts to just one routine.  By isolating these changes, your job is simplified and setting up PivotTables and Charts can...


October 31, 2009  10:05 AM

Wrapping things up



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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



Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, vba

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:


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: