- Dedicated trainers
- Maximum 6 per class
- Superb courseware
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.
- Working with Files and Folders (this article)
- Worked Example 1 - Finding Text within Workbooks
- 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.
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:
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):
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.
- Working with Files and Folders (this article)
- Worked Example 1 - Finding Text within Workbooks
- 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: