Beyond Excel: VBA and Database Manipulation:

excel


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:


October 10, 2009  11:11 PM

Brain Surgery



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

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



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

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


October 6, 2009  7:03 PM

Improving the User Interface – Adding a Button



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

[caption id="attachment_129" align="alignright" width="403" caption="Adding Buttons"]

Bookmark and Share     0 Comments     RSS Feed     Email a friend


October 1, 2009  10:02 AM

Intro to XL Objects in VBA



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

In our last post we looked at running a query that accepts user input to determine the result set loaded into XL.  I want to emphasize that although we used an Access database as our source, these same tools and processes work with Oracle, SQL Server, AS/400/iSeries/IBM i/Power i/System i (however...


September 24, 2009  4:18 PM

Automating Queries



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

We’ve been looking at using MS Query from XL to get at data stored on mainframes, servers, or you PC.  Hopefully you have experimented with MS Query.  MS Query represents what I refer to as a “manual method” because it involves no coding.  So let’s look at how we can...


September 22, 2009  9:06 AM

Creating a Query



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

So far we’ve discussed MS Query and its uses.  Today we’re going to see just how easy it is to use in 9 simple steps. Step 1 – Find Data You probably have a copy of a sample MS Access database called "Northwind" on your PC.  We will use it for this exercise.  The most...


September 18, 2009  3:53 PM

MS Query – Example Use



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

So far I’ve talked about how to use MS Query.  It’s easy.  It’s useful.  It’s free.  It’s included with every version of XL.  It works with just about any database provided you have the (usually free) ODBC driver.  Though we won’t be using MS Query in any of the spreadsheets we...


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: