Create folder (VBA – Excel XP)

Topic

New Reply

I am sort of an advanced beginner with VBA. I am needing to programmatically test for the existence of a Windows folder, and create it if it does not exist. I couldn’t find out how to accomplish this with the books I have. Does anybody have code that would accomplish this?

Reply | Quote

Replies

You need to use the Dir and mkDir command.

Dim strDir As String
‘Directory to check
strDir = “C:test”
‘Does directory exist
If Dir(strDir, vbDirectory) = “” Then
‘No, so create it
MkDir strDir
End If

Reply | Quote

I believe the FileSystemObject has a FolderExists method/function.

Reply | Quote

Bryan–

Thanks for your piece of code. It worked for creating a directory.

Mike–

There is a FolderExists and a FileExists method. For the little function I’m creating, I also need to determine if a certain file name exists. The documented syntax for FileExists is object .FileExists(filespec) where Object is always the name of a FileSystemObject. How do I determine the name of the FileSystemObject that I need to use?

Reply | Quote

Randall,,
You can use the Dir function to see if a file exists as well.

If Dir(“c:FullPathtoFile.ext”) = “” then
‘File does not exist
End If

If the file does exist, the dir command will return the File Name.

Reply | Quote

You can use the FileSystemObject from the scripting runtime as follows :

Dim oFs
Set oFs = CreateObject(“Scripting.FileSystemObject”)
If oFs.FileExists(“FilepathFileName”) Then
‘do whatever
End if
Set oFs = Nothing

You can also use oFs.FolderExists to check for folders.

You could also use something like

Function fFileExists(strFullName As String) As Boolean
fFileExists = Len(Dir(strFullName, vbNormal))
End Function

Andrew C

Reply | Quote

You’ll need to set a reference to Microsoft Scripting Runtime. It’s possible that you’ll need to install the Windows Scripting Host, but it’s usually installed on most systems unless you specifically didn’t install it.

Dim oFSO as Scripting.FileSystemObject

Set oFSO = New Scripting.FileSystemObject

If oFSO.FolderExists(“folderpath”) = True…

I hope this helps.

Reply | Quote

Just a warning about the FileSystemObject. It is part of the scripting model, and some network administrators disable it, so if your code is going to be used at locations you don’t have control over, it is a better idea to use the code Bryan posted (carbonnb).

Reply | Quote