O'Reilly logo

Excel 2013 Power Programming with VBA by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 25: 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, or rename files. Excel 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 also supported the ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required