It’s friggin’ magic. FileSystemObject is how you run scripts on your files and folders in VBA. You want to create 100 files on your desktop automatically? It’ll let you do that. You want to delete files with specific names in a directory? It’ll let you do that too. It’s also pretty crucial to viruses like Anna Kournikova and the Love Bug, which I’ve been looking at recently. So here’s how to make a FileSystemObject.
It’s a quick 2-liner.
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
A side note for beginners: Part of the barrier to entry with Visual Basic is not having a friendly interface to write practice scripts. What a lot of people don’t know is that you can practice all this in a Word document if you have the Developer tab. That’s where we’ll start off.
Where Do I Write the Script?
Open up Word. Go to File, Options, Customize Ribbon, and make sure that the Developer check-box is checked.
Then you can click into Developer on your ribbon and hit the Visual Basic Editor. I have no idea why this isn’t loaded when you initially install Word.
Warning: You need to be in a Macros-Enabled workbook in order to run Visual Basic from that editor. Make sure you’re in a document with an extension .docx.
What’s a FileSystemObject Method?
Methods are how you get shit done. FileSystemObject has a lot of methods. You can see a more complete list here. For our purposes, I want to focus on the ones I find most helpful. Let’s look a CreateFolder for a moment.
After you stash your FileSystemObject in a variable (in this case FSO), you can just append the method to the object with a dot to make it happen. Try out this example:
Sub VBA()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CreateFolder ("C:\Users\Desktop\Test\Test")
End Sub
Cool, right?
Now, notice if you try to run it again—you get an error.
Visual Basic errors are kind of rude. I have a love/hate relationship with them. On one hand, they help you figure out where you’ve gone wrong. On the other, they’re abrupt as fuck and sometimes too vague to help you track the problem.
This one’s pretty straightforward though. So how can you avoid it in the future?
There’s a method for that called FolderExists. Don’t get this confused with FileExists. Looks really similar. Checks for different things.
FolderExists returns true if the folder’s already there and false if not. That makes it really easy to work into an If Statement. Try out this new updated script with that worked in:
Sub VBA()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists("C:\Users\Desktop\Test\Test") Then
MsgBox ("Hi, You've already got that file!")
Else
FSO.CreateFolder ("C:\Users\Desktop\Test\Test")
End If
End Sub
That basically tells the computer that you want a nicer message if the folder is already there. Otherwise, the script will create the folder anew.
What about the FileSystemObject Properties?
A complete list is available here. Properties don’t do things to your files. They tell you things about them. The last time they were modified, their type, size, parent folder, and so on.
So say you’re looking at the above code, and you decide that if the folder already exists, you want to know when it was made. You can use the DateCreated property to display that in your message box.
Now, it’s important to note that properties work off of objects. You’ll need to use the GetFolder method to return your folder as an object. Then you can get VBA to look up its details.
Like in this updated script:
Sub VBA()
Dim FSO
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists("C:\Users\Desktop\Test\Test") Then
Set folder = FSO.GetFolder("C:\Users\Desktop\Test\Test")
MsgBox ("That folder was created: " & folder.DateCreated)
Else
FSO.CreateFolder ("C:\Users\Desktop\Test\Test")
End If
End Sub
This should be your result:
That’s enough VBAing for one evening. I only have 3 pounds of M&M’s to get me through my taxes, so I may be on hiatus next week.
More Visual Basic to come after.
gifGuide2Code 