Beyond Excel: VBA and Database Manipulation:

January, 2010

1

January 25, 2010  6:06 PM

Adding Formatting



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

Now it's time to add basic formatting.  Formatting is important.  Accountants must see currency values formatted their special way.  Dates must be presented according to regional standards.  Fortunately, adding formatting to your results is as easy as adding one more column to our...

January 21, 2010  5:58 PM

Adding Sorting



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

Another common requirement for reports is sorting.  While this can be done in SQL with an ORDER BY clause, I like to use Excel to sort because the desktop PC usually has a much higer CPU-Power-to-Number-of-Users ratio than a database server.  Today, we add sort columns to the...


January 18, 2010  6:15 PM

Preserving Column Headers and Key Values



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

When retrieving large amounts of data, we need to preserve displaying column headers and key values.  Key values should be on the left of your extract.  With that in mind, today we add column "Freeze" to our Fields Table. 
[caption id="attachment_470"...


January 14, 2010  6:02 PM

Adding SQL Formulas to the Table



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

In the last post we linked the Fields Table to some code that translated the table into an SQL Select clause.  Most often, that's enough.  But sometimes we need to manipulate the data before loading it into our result spreadsheet.  We can do that by adding

Bookmark and Share     0 Comments     RSS Feed     Email a friend


January 9, 2010  12:04 PM

Adding Tables and Columns to the Fields Table



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

In my last post I provided some simple tools to manage Excel tables.  This was in preparation for creating and incorporating a "Fields Table" into our reporting system.  The advantages of the Fields table are enumerated in the prior posts.  So lets start using it. The first portion of our...


1

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: