Excel 2019 Power Programming with VBA

CHAPTER 11Working 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 that don't come with a lot of pitfalls and gotchas.

The first of those techniques is to use Excel's Power Query feature.

Power Query Basics

Power Query offers an intuitive mechanism to extract data from a wide variety of sources, perform complex transformations on that data, and then load the data into a workbook.

To start this basic review of Power Query, let's walk through a simple example. Imagine that you need to import Microsoft Corporation stock prices into Excel using Yahoo Finance. For this scenario, you need to perform a web query to pull the data needed from Yahoo Finance.

To start your query, follow these steps:

1. In a new Excel workbook, select the Get Data command in the Get ...