This page has 1 thread | Add post

Blogs

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

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 blog) 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.

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 blog)
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.

Posted by Andy Brown on 23 January 2012

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!

Parts of this blog
Working with Files and Folders (this blog) Worked Example 1 - Finding Text within Workbooks Worked Example 2 - Recursively Listing Files

Some other pages relevant to the above blogs include:

This blog has 1 thread

Add a new post

From:	Vinod Kumar
When:	04 Dec 17 at 15:31

Reply to Vinod Kumar

I want to list the names and durations of all videos in a folder and its subfolders using Excel VBA. From the code below I can get the duration of video files, but I can't exclude all other files. This gives me a list of all the file names, which I don't need. Also I am failing to loop through subfolders.

What I want to acheive is for the macro to loop through all subfolders in the the given root folder and list only video names and duration in coloumns A and B.

Some help with this is truly appreciated.

Option Explicit

Dim i As Long, SourceFldr
Dim c As Range, rng As Range
Dim sFile As Variant
Dim oWSHShell As Object
Dim WS As Worksheet
Dim lRow As Long

Sub GetDuration()

Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
Set oWSHShell = CreateObject("WScript.Shell")

With fldr
.Title = "Select a Source Folder"
.AllowMultiSelect = False
.InitialFileName = oWSHShell.SpecialFolders("Desktop")
If .Show <> -1 Then GoTo NextCode
SourceFldr = .SelectedItems(1)
NextCode:
End With

Dim oShell: Set oShell = CreateObject("Shell.Application")
Dim oDir: Set oDir = oShell.Namespace(SourceFldr)

i = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row + 1

For Each sFile In oDir.Items
Cells(i, 1).Value = oDir.GetDetailsOf(sFile, 0) 'File Name
Cells(i, 2).Value = oDir.GetDetailsOf(sFile, 27) 'File Lenght
i = i + 1
Next sFile

Set oDir = Nothing
Set oShell = Nothing

End Sub

From:	Andy B
When:	04 Dec 17 at 16:42

Reply to Andy B

I'm not going to give an answer, but wlll give some hints.

You're making life hard for yourself by not referencing and using FileSystemObjects. Please read the rest of this blog, and you should see that there's no need to create a complex shell application. The other advantage of using file system objects is that you can create strongly-typed code (so when you type in the name of an object, for example, such as a file or folder, Intellisense will suggest all of its methods and properties).

In the same vein, avoid dimensioning anything as Object (be specific and say what sort of an object it is), and also avoid using CreateObject for the same reason.

To omit certain files you will need to have an IF condition testing whether the name of each file contains particular text, and to loop over subfolders you'll need to use recursion (again, this blog contains an example).

What I would do is read through this blog getting the examples to work. When you've done this, go back to your code and edit it in line with what you've learnt!

Best of luck ... but please note that we won't enter into any more correspondence on this.

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