O'Reilly logo

Excel 2016 Power Programming with VBA by Richard Kusleika, Michael Alexander

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required