The Excel® Analyst's Guide to Access®

Chapter 15. Using VBA to Move Data between Excel and Access

Throughout the first few chapters of this book, you have discovered several ways to move data between Access and Excel. Although many of those techniques will suit your needs just fine, each one retains an aspect of manual involvement. That is to say, each one involves manual setup, management, and maintenance. In this chapter, you explore how to leverage VBA (along with some data connection technologies) to make your life even easier by making your data transfer processes virtually hands free.

Note the phrase, "along with some data connection technologies." The reality is that VBA, in and of itself, does not have the capability to connect and manipulate external data. You need to combine VBA with a helper technology to work with external data sources. Although many technologies allow you to automate your data processes, you will focus on using ADO (ActiveX Data Objects) and SQL (Structured Query Language)—commonly pronounced "sequel."

Why bother using VBA when the manual processes work just fine? First, VBA allows you to process data without the need to create and maintain multiple queries and macros. Also with VBA, you can perform complex, multi-layered procedures that involve looping, record-level testing, and If...Then...Else checks without the need to inundate your processes with many queries and temporary tables. Finally, the one-two-three combination of VBA, ADO, and SQL is extremely powerful and relatively easy to ...