Excel Macros For Dummies, 3rd Edition

Chapter 10

Wrangling External Data with Macros

IN THIS CHAPTER

Working with external data connections

Connecting to external databases

Using macros to pull external data

Working with external text files

External data is exactly what it sounds like: data that isn’t located in the Excel workbook in which you’re working. Some examples of external data sources are text files, CSV files, Access tables, and even SQL Server tables.

There are numerous ways to get data into Excel. In fact, between the functionality found in the UI and the VBA/code techniques, there are too many techniques to focus on in one chapter. Instead, this chapter focuses on a handful of techniques that you can implement in most situations and that don’t come with a lot of pitfalls and gotchas.

Working with External Data Connections

Every Excel workbook has the ability to store syntax that allows the workbook to pull in data from external databases, files, and websites. This ability is made possible by Excel’s External Data Connection feature. You can create any number of data connections to retrieve the needed external ...