Test for existence of folder from Excel in VBA

[Bung]

How do I test for the existence of a folder from a VBA module in Excel? GetAttr only works if the folder exists. There appear to be a whole heap of ways for testing for the existence of a file, but I can't figure out how to do it for a folder. The help on my MSDN disk I got with VB6 says to use "FileSystemObject.FolderExists(folderspec)" but that does not appear to be available to me in Excel '97.

Anybody got any suggests (apart from inelegant "on error ..." approaches!)



Bung
Life is non-linear...

 

[Bung]

It's easy when you know how. I found it after a bit more digging .. it requires turning on reference to the Microsoft Scripting components then:

Dim strFolderName As String
Dim objFso As New FileSystemObject
blah
strFolderName = "C:\whatever"
blah
If Not objFso.FolderExists(strFolderName) Then
MkDir (strFolderName)
End If
etc

But why it isn't available almost as a default thing beats me!


Bung
Life is non-linear...

 

[bltseattle]

I found this procedure in a reference that lists all the *.xls files in a directory. You could probably modify it with a conditional to find the file you are looking for and turn it into a function to returen a true or false value.

Sub listfiles()
Dim myrow As Integer
Dim myfile As String

myrow = 25
' retrieve a filename using Dir function
myfile = Dir("*.xls")
Do Until myfile = ""
Cells(myrow, 1) = myfile
myrow = myrow + 1
myfile = Dir
Loop
End Sub

 

[bltseattle]

I thought that the following code would be another way of checking for the existence, but it doesn't work correctly. I thought this should work, anyone know why it doesn't?

Function file_exists(whatfile) As Boolean
Dim myfile, whatfile2 As String
file_exists = False
' retrieve a filename using Dir function
myfile = Dir("*.xls")
Do Until myfile = ""
If myfile = whatfile Then file_exists = True
myfile = Dir
Loop
End Function

 

[Bung]

The only problem is that file checking as suggested by bltSeattle doesn't work for checking the existence of a folder. I specifically need to check for folder's existence, so I can create it if it doesn't exist, or not create it if it does exist. So I have to know if the object in question is a file or a folder, not just that an object with the name path\folder etc exists.


Bung
Life is non-linear...

 

[VBArrrgh]

Bung--

Actually, it does exist.

if len(dir(Folder2Find,vbdirectory))>0 then folder exists

Quick, easy and mostly painless.

--VBArrrgh