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

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.

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!

 

This blog has 1 thread Add post
04 Dec 17 at 15:31

Hi

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

 

04 Dec 17 at 16:42

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.