- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
LINKING TO THE MICROSOFT SCRIPTING RUNTIME LIBRARY
In order to work efficiently with files and folders you first need to create a FileSystemObject. This blog explains how to do this!
You can find more articles on this subject in our Excel VBA blog. However, the best way of learning is to attend our two-day Excel Visual Basic course, or the two-day advanced VBA course.
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
There is much more on FileDialogs (and on reading/writing to/from text files) in separate blogs, but here's some sample code to give a flavour:
'ensures you have to declare all variables used
Option Explicit
'create a public variable to refer
'to a file system object
Public fso As New FileSystemObject
Sub ListFiles()
'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
Debug.Print fil.Name
Next fil
End Sub
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.
Comments on this blog
This blog currently has no comments.