Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

COPY, RENAME, DELETE AND MOVE FILES AND FOLDERS

Part one of a three-part series of blogs

By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples.

  1. Working with Files and Folders (this article)
  2. Worked Example 1 - Finding Text within Workbooks
  3. Worked Example 2 - Recursively Listing Files

This blog is part of a complete Excel VBA tutorial. We can also provide training in person, either on one of our Excel courses or on one of our VBA courses.

Posted by Andy Brown on 23 January 2012 | 1 comment

Working with Files and Folders

One surprising aspect of writing Visual Basic for Applications (VBA) in Excel - or any other Microsoft applicaiton for that matter - is that you can also rename, delete, copy and move files and folders on your hard disk.  This blog shows you how!

Remember that in Excel the only built-in file-related collection that you can work with is Workbooks, which are the Excel workbooks already open in memory.  You have no default way of working with files stored on your hard disk.

Creating a FileSystemObject

To play about with files and folders on your hard disk, you'll need to create a reference to the Microsoft Scripting Runtime object library, and then create a FileSystemObject.  Because this is quite a common requirement, I've put it in a separate blog.

Getting a Folder and Listing its Files

Once you've got a variable referencing this weird thing called a FileSystemObject, you can use it as a prefix to do almost anything with the files and folders on your hard disk.  One example is getting a folder and listing its contents.

For the example code below, imagine that we have a folder set up as follows:

Folder on hard disk

The Wise Owl folder contains 3 files.

 

We want to list out in the Immediate window all of the files in this folder (perhaps because we then want to do something else with them):

Example debug

We want to list out the name of the folder, and then the names of all of the files in it.

 

Code to get the above to work could look like this:

Sub ListFiles()

'create a new file system object (must first

'have referenced the Microsoft Scripting Runtime

'library, as described in separate blog)

Dim fso As New FileSystemObject

'now get a pointer to a particular folder

Dim fol As Folder

Set fol = fso.GetFolder("C:\wise owl\")

'print out the name of this folder

Debug.Print "Folder name:", fol.Path

Debug.Print "============="

Debug.Print " "

'now loop over all of the files in the folder

Dim fil As File

For Each fil In fol.Files

Debug.Print fil.Path

Next fil

End Sub

Out of all the Microsoft applications which support VBA, the Microsoft Scripting Runtime object library is one of the most intuitive to use - as the above example shows.

Typical Methods and Properties for a File

The table below shows some of the standard things you'll want to do to a file:

What you want to do How to do it
Copy a file Apply the Copy method, giving the destination as an argument
Move a file Apply the Move method, again giving the destination as an argument
Delete a file Apply the Delete method
Rename a file Change the file's Name property

The second part of this blog gives a couple of other examples to give you the idea of what's possible!

 

COPY, RENAME, DELETE AND MOVE FILES AND FOLDERS

Part one of a three-part series of blogs

By referencing the unintuitively named Microsoft Scripting Runtime object library you can write VBA code to access files and folders on your hard disk. This blog explains how, and gives a couple of worked examples.

  1. Working with Files and Folders (this article)
  2. Worked Example 1 - Finding Text within Workbooks
  3. Worked Example 2 - Recursively Listing Files

This blog is part of a complete Excel VBA tutorial. We can also provide training in person, either on one of our Excel courses or on one of our VBA courses.

Comments on this blog

This blog has one comment:

Comment added by escapeopia on 02 October 2012 at 06:14 GMT
hmmm I'm not sure what I'm doing wrong, but nothing happens after I run this macro...like absolutely nothing happens. Can you possibly try thinking what went wrong?
Reply from Andy Brown (blog author)
Can't imagine!  Go into Tools References and check that there's a tick next to the Microsoft Scripting Runtime object library, and check that this reference appears at the top of the list.  Otherwise, I suspect it might be a security issue.  Are you running tihs on your own laptop, or on a corporate networked machine?

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.