Beyond Excel: VBA and Database Manipulation:

odbc


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


September 17, 2009  6:44 AM

Data Sources



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

In my last post, I shared how you can explore just about any database using MS Query, a useful, friendly, and free tool included with every version of XL.  To use it with your database, you must point it to the appropriate data source. 


September 10, 2009  7:28 AM

Manually Linking to Data – MS Query



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

Before tackling automated methods for incorporating data into XL, we should look at a surprisingly useful and free component included with every version of XL, MS Query.  MS Query provides a graphical interface into databases.  You can use it to explore database tables, examine columns and...


September 10, 2009  6:59 AM

Forward



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

Everyone knows Microsoft Excel is a powerful database manipulation engine, but data lives in databases and only a few Excel experts know how to exploit this natural synergy. If you are one of tens of thousands of business developers asked each day to:

  • Produce reports from systems...


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: