Chapter 27: Manipulating Files with VBA


Getting a basic overview of VBA text file manipulation features

Performing common file operations

Opening a text file

Displaying extended file information, such as details for media files

Reading and writing a text file with VBA

Exporting a range to HTML and XML format

Zipping and unzipping files

Using ActiveX Data Objects to import data

Performing Common File Operations

Many applications that you develop for Excel require working with external files. For example, you might need to get a listing of files in a directory, delete files, rename files, and so on. Excel, of course, can import and export several types of text files. In many cases, however, Excel's built-in text file handling isn't sufficient. For example, you might want to paste a list of filenames into a range, or export a range of cells to a simple HyperText Markup Language (HTML) file.

In this chapter, I describe how to use Visual Basic for Applications (VBA) to perform common (and not so common) file operations and work directly with text files.

Excel provides two ways to perform common file operations:

Use traditional VBA statements and functions. This method works for all versions of Excel.

Use the FileSystemObject object, which uses the Microsoft Scripting Library. This method works for Excel 2000 and later.

caution.eps Some earlier versions of Excel ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.