VBA - working with files videos | Excel VBA Part 22 - Files and Folders

Posted by Andrew Gould on 03 March 2014

The Scripting Runtime Object Library allows you to easily write code in an Excel VBA project which can manipulate the file and folder structure of your computer. It's and incredibly useful, although potentially quite dangerous thing to be able to do and this video will show you how. You'll learn how to reference the Scripting Runtime Library, what a FileSystemObject is and how to use it and how to perform various methods such as create folders, copy and move files and even how to delete them. Towards the end of the video you'll see how to loop over a collection of files in a single folder and then, as an encore, how you can loop through the complete set of folders and subfolders from a given starting point.

You can increase the size of the video:

Full screen mode for YouTube

You can view the video in full screen mode as shown on the left, using the icon at the bottom right of the frame.

You can also increase the quality of the video:

Changing resolution

You can improve the resolution of the video using another icon at the bottom right of the frame. This will slow down the connection speed, but increase the display and sound quality. This icon only becomes visible when you start playing the video.

Finally, if nothing happens when you play the video, check that you're not using IE in compatibility view.

This page has 1 thread Add post
19 May 20 at 05:57

Dear Andrew,

I'm a music lover. I have more than 1750 songs in my computer. For taking my learning to next level, I want to have the Name, Author, Album and Title of each and every file of these files; that are kept in my my "D" Drive ("D:\Music\Files..."); through VBA. I wrote a code. But, it only gave me the File Name of these music files. But, I don't know, how will I get the other attributes of these files like their Authors, Albums and Titles. Can you please help me.... I'm attaching my written code in below..
 

Option Explicit

Sub ImportingFileDetails()

On Error Resume Next

Dim FDB As Office.FileDialog
Set FDB = Application.FileDialog(msoFileDialogFolderPicker)

With FDB
.ButtonName = "Select Folder"
.InitialFileName = "Choose Desired Folder"
.InitialView = msoFileDialogViewPreview
.Title = "Get Files' Names"
.Show
End With

Dim SelectedFolder As String
SelectedFolder = FDB.SelectedItems(1)

Dim FSO As Scripting.FileSystemObject
Set FSO = New Scripting.FileSystemObject

Dim ChoosenFolder As Scripting.Folder
Set ChoosenFolder = FSO.GetFolder(SelectedFolder)

If ChoosenFolder Is Nothing Then
MsgBox "No Folder is selected"
Exit Sub
Else
Dim FilesUnderChoosenFolder As Scripting.File

Dim SerialNumber As Integer
SerialNumber = 1

Dim i As Integer
i = 5

Dim FilesCountUnderChoosenFolder As Integer
FilesCountUnderChoosenFolder = 0

Range("B5", Range("B5").End(xlDown).Resize(1, 2)).ClearContents

For Each FilesUnderChoosenFolder In ChoosenFolder.Files
ImportFileDetails.Cells(i, 1).Value = SerialNumber
ImportFileDetails.Cells(i, 2).Value = FilesUnderChoosenFolder.Name
ImportFileDetails.Cells(i, 3).Value = FilesUnderChoosenFolder.Attributes("Author")
ImportFileDetails.Cells(i, 4).Value = FilesUnderChoosenFolder.Attributes("Album")
ImportFileDetails.Cells(i, 5).Value = FilesUnderChoosenFolder.Attributes("Title")

SerialNumber = SerialNumber + 1
i = i + 1
FilesCountUnderChoosenFolder = FilesCountUnderChoosenFolder + 1
Next FilesUnderChoosenFolder
End If

Dim FirstBlankRow As Integer

If FilesCountUnderChoosenFolder > 1 Then
FirstBlankRow = Range("A5").Offset(0, 1).End(xlDown).Offset(1, 0).Row
Range("A" & FirstBlankRow).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
ElseIf FilesCountUnderChoosenFolder = 1 Then
FirstBlankRow = Range("A5").Offset(1, 1).Row
Range("A" & FirstBlankRow).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Else
FirstBlankRow = Range("A5").Offset(1, 1).Row
Range("A" & FirstBlankRow).Select
Range(Selection, Selection.End(xlDown)).EntireRow.Delete
Range("A5").Value = ""
End If

ImportFileDetails.Range("B4").Value = "File Names"
ImportFileDetails.Range("B5").Activate

End Sub

19 May 20 at 09:16

Sadly we don't have time to reply to enquiries like this, but I've posted your query in case anyone else can help.