database

Using ADO to Read a Database

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...

Delivering a Finished Product

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...

Wrapping things up

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...

Building a Library of Routines – Setup_PivotChart

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...

Building a Library of Routines – Setup_Pivot

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...

Building a Library of Routines – Settings

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...

Building a Library of Routines – ?Exists

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...

Building a Library of Routines – Template

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...

Pivots and Charts

      0 Comments     RSS Feed     Email a friend

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:

      0 Comments     RSS Feed     Email a friend