Excel® 2010 Power Programming with VBA

Chapter 27: Manipulating Files with VBA

IN THIS CHAPTER

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

Some earlier versions of Excel ...