Posted by Andy Brown on 13 January 2012 | no comments
Creating a FileSystemObject
You won't care what a FileSystemObject is, but you will care what it can do for you. You can use FileSystemObjects to:
- get access to the files and folders on your hard disk (to move, copy, rename, delete or open them); and
- create and open TextStreams to read text files in line by line, or to write to them
Each of these topics is described in separate blogs - the only purpose of this blog is to show how to start you off by creating a FileSystemObject. You'll need to do two things:
- Create a reference to the Microsoft Scripting Runtime object library.
- Create a new FileSystemObject variable.
Here's how to do this!
Referencing the Microsoft Scripting Runtime Object Library
This is hard to describe, but easy to do. First, you need to choose to create a reference within your Excel workbook (or Word document, PowerPoint presentation or Access database). To do this:
Select the menu option shown above
- Within VBA, select Tools References from the menu as shown above.
- Tick the Microsoft Scripting Runtime library as shown below, and click OK.
Select the object library shown above
If you now select Tools -> References from the menu again, you should see the option you selected near the top:
The object libraries that you are referencing appear near the top of the list.
Now that you've referenced this library, it's time to create a FileSystemObject.
Creating a FileSystemObject Variable
The easiest way to do this is to create and instantiate a public variable to refer to a FileSystemObject in a single line.
If that sentence didn't make much sense to you, don't worry! In practice all that you need to do is to type in a single line of code, and everything else will fall into place!
Here's a line of code to do this:
The only reason FileSystemObject appears in the list is because you have referenced the object library containing it.
A Sample Routine Using a FileSystemObject
'ensures you have to declare all variables used
'create a public variable to refer
'to a file system object
Public fso As New FileSystemObject
'some example code to list out all
'of the files in a given folder
Dim fol As Folder
Dim fil As File
Set fol = fso.GetFolder("C:\wise owl\")
For Each fil In fol.Files
Running the ListFiles routine shown above would create a listing of all the files in the wise owl folder of your C drive in the immediate window.