BY CATEGORY▼
- VIDEOS HOME PAGE
- .NET (14)
- Business Intelligence (40)
- Integration Services (19)
- Macros and Programming (81)
- Microsoft Excel (63)
- Microsoft Office (85)
- Miscellaneous (1)
- Power BI (31)
- Python (29)
- Report Builder (106)
- Reporting Services (112)
- SQL (42)
- Visual Basic for Applications (212)
- Visual C# (14)
VBA CATEGORIES▼
- Excel VBA - Basics (24)
- VBA User Forms (22)
- Excel VBA - pivot tables (9)
- Excel VBA - charts (6)
- VBA - advanced (13)
- VBA - working with files (12)
- VBA - linking applications (11)
- VBA - working with Outlook (14)
- Built-in VBA functions (9)
- VBA - working with data (57)
- VBA - scraping websites (25)
- VBA - working with shapes (4)
- VBA - classes and structures (6)
VBA - WORKING WITH FILES VIDEOS▼
- Excel VBA Part 22 - Files and Folders
- Excel VBA Part 23 - Text Files
- Excel VBA Part 24 - File Dialogs
- Excel VBA - Save as PDF
- How do I copy sheets from multiple Excel files into one workbook in VBA?
- How do I export multiple PDF files from Excel when looping over cells?
- How do I copy sheets from multiple Excel files into one worksheet?
- How do I find a folder with a partial name in VBA?
- How do I loop through folders and subfolders in Excel VBA?
- How do I copy data from multiple worksheets in other workbooks?
- How do I unzip files using VBA?
- How do I list all properties of a file using VBA?
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.
See our full range of VBA training resources, or test your knowledge of VBA with one of our VBA skills assessment tests.
There are no files which go with this video.
There are no exercises for this video.
Making a video bigger
You can increase the size of your video to make it fill the screen like this:
Play your video (the icons shown won't appear until you do), then click on the full screen icon which appears as shown at its bottom right-hand corner.
When you've finished viewing a video in full screen mode, just press the Esc key to return to normal view.
Improving the quality of a video
To improve the quality of a video, first click on the Settings icon:
Make sure you're playing your video so that the icons shown appear, then click on this gear icon at the bottom right-hand corner.
Choose to change the video quality:
Click on Quality as shown to bring up the submenu.
The higher the number you choose, the better will be your video quality (but the slower the connection speed):
Don't choose the HD option unless you have a fast enough connection speed to support it!
Is your Wise Owl speaking too slowly (or too quickly)? You can also use the Settings menu above to change your playback speed.
Thank you and to Wiseowl for these fantastic videos, they truly help with getting good quality instruction on how to write good, rounded routines. I highly recommend these. I have a question on the FSO. I've taken your tutorial and can get everything to work exactly as described. Brilliant!.
I would like to expand my knowledge about this topic but have hit a stumbling block. I've moved this on to start moving files with fso.filemove from one folder to another and while it is fine if I hard write the file name into the sub but if I use a variable to name the saved file I get an error.
My question is, can this be accomplished using a variable to name the new workbook?
Hi Phil,
You certainly can use variables to rename the moved file, for example:
Sub MoveFileWithVariable()
Dim fso As Scripting.FileSystemObject
Dim OldFileName As String
Dim NewFileName As String
OldFileName = "C:\Old folder\Original file name.xlsx"
NewFileName = "C:\New folder\New file name.xlsx"
Set fso = New Scripting.FileSystemObject
If fso.FileExists(OldFileName) Then
fso.MoveFile _
Source:=OldFileName, _
Destination:=NewFileName
End If
End Sub
If you’re constructing the file name with some form of expression, make sure that you haven’t forgotten to include the full folder path and filename extension. I hope that helps!
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
Sadly we don't have time to reply to enquiries like this, but I've posted your query in case anyone else can help.