11 Working with External Data and Files

In This Chapter

  • Working with external data connections
  • Using ActiveX data objects to get external data
  • Performing common file operations
  • Working with text files

Working with External Data Connections

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

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, then, in this chapter we’ll focus on a handful of techniques that can be implemented in most situations and don’t come with a lot of pitfalls and gotchas.

The first of those techniques is to use an external data connection.

Manually creating a connection

Excel has made it easy to manually connect to external data sources such as Microsoft Access, SQL Server, or any other ODBC connection you regularly use. For example, you can connect to an Access database by following these steps:

  1. Open a new Excel workbook ...

Get Excel 2016 Power Programming with VBA now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.