×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Test for existence of folder from Excel in VBA

Test for existence of folder from Excel in VBA

Test for existence of folder from Excel in VBA

(OP)
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...

RE: Test for existence of folder from Excel in VBA

(OP)
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...

RE: Test for existence of folder from Excel in VBA

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

RE: Test for existence of folder from Excel in VBA

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

RE: Test for existence of folder from Excel in VBA

(OP)
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...

RE: Test for existence of folder from Excel in VBA

Bung--

Actually, it does exist.

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

Quick, easy and mostly painless.

--VBArrrgh

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources