Beyond Excel: VBA and Database Manipulation:

excel


March 11, 2010  8:43 PM

Updating Databases from Excel



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

It seems almost every web portal that lists data includes an export to Excel, CSV, or XML, or combinations thereof.  What we are going to start exploring will enable you to capture that data, massage it, save it, and merge it with existing systems - using nothing more than Excel and...

March 4, 2010  6:32 PM

Drill Down – Part 2



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

Before we get to coding, let's look at the final product.  When we click our 'easy' button, our spreadsheet prompts us for dates, customers, and products.  If we just take the defaults, everything will be loaded from the NorthWinds database - like this: [caption id="attachment_544"...


February 24, 2010  6:40 PM

Drill Down – Part 1



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

Last post I showed how we can present summarized data when what our users want is supported by detail that would fill up tens of thousands of rows.  When we summarized the data, in a sense, we duplicated the functionality of a simple "pivot table."  Pivot Tables have one feature we didn't...


February 19, 2010  5:38 PM

Group By and Aggregate SQL Functions



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

At this point, we have everything we need for about 95% of user reporting and analytical requests.  On rare occasions we need to summarize the data prior to putting it into our Excel spreadsheets.  This happens when the raw detail for an expected time frame exceeds Excel's normal 65,536 row limit...


February 9, 2010  9:23 PM

Adding Excel Formulas – Part II



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

Last post showed the Fields Table column holding Excel Formulas to embed into your database result sets.  We also talked about how to enter formulas that reference database fields.  This is truly powerful stuff that turns basic data extracts into analytical information.  So let's start adding...


February 6, 2010  5:37 PM

Adding Excel Formulas



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

It's great that we can download and display data in Excel where users can sort it, filter it, print it... whatever they want to it.  It's great that we can summarize that data in interactive pivot tables.  It's great that we can display those pivot tables in interactive charts.  Those of you...


February 2, 2010  5:57 PM

Setting Column Widths or Hiding Them



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

Sometimes we want to include large fields in our results but don't want them hogging screen realestate.  We can limit those results using the "Width" colum in our Fields Table (See below).  If you couple this with the Format "W" (Wrap), you can display comments and notes...


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


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: