WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 520 reviews for our classroom and online training
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.

Posted by Andy Brown on 13 January 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Tools References menu

Select the menu option shown above

  1. Within VBA, select Tools References from the menu as shown above.
  2. Tick the Microsoft Scripting Runtime library as shown below, and click OK.
Selecting Microsoft Scripting Runtime

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:

Tools references again

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:

Creating a new FSO variable

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.


This blog has 0 threads Add post