Excel® 2007 Power Programming with VBA

Chapter 27. Manipulating Files with VBA

In This Chapter

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.

• A basic overview of VBA text file manipulation features

• Performing common file operations

• Various ways to open a text file

• Displaying extended file information, such as details for media files

• Examples of reading and writing a text file with VBA

• Sample code for exporting a range to HTML and XML format

• Zipping and unzipping files

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 export a range of cells to a simple HyperText Markup Language (HTML) file.

Performing Common File Operations

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.

New

Previous versions of Excel also supported the use of the FileSearch object. That feature has been removed from Excel ...