BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
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:
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:
'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 " "
'now loop over all of the files in the folder
Dim fil As File
For Each fil In fol.Files
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!
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.
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
Dim fldr As FileDialog
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
Set oWSHShell = CreateObject("WScript.Shell")
.Title = "Select a Source Folder"
.AllowMultiSelect = False
.InitialFileName = oWSHShell.SpecialFolders("Desktop")
If .Show <> -1 Then GoTo NextCode
SourceFldr = .SelectedItems(1)
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
Set oDir = Nothing
Set oShell = Nothing
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.