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.
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.
Previous versions of Excel also supported the use of the
FileSearch object. That feature has been removed from Excel ...