This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 one of our Excel VBA courses.

Posted by Andy Brown on 13 January 2012

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.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC