October 29, 2009 4:26 PM
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaIn 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaThis 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaLast 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaWe 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaUp 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaSo 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaIn 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaIn 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
Posted by: Craig Hatmaker
database,
development,
excel,
Microsoft Excel,
ms query,
odbc,
sql,
vbaPut 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...
