checking to see if a directory exists

philR

Active Member
Joined
Feb 25, 2002
Messages
257
Hi peeps.
I need to save a file ito a directory, but if the directory doesn't exist, I want to create it first.
Is there a simple way for Visual Basic to check for the existence of a directory? All help gratefully recieved.

Phil
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The GetAttr function returns an error if the path (its argument) does not exist.

Code:
Dim x as String
On Error Resume Next
x = GetAttr("ThePath") And 0
If Err <> 0 Then
'*** Your code to create the directory goes here***
End If
 

philR

Active Member
Joined
Feb 25, 2002
Messages
257
Thanks for that.
I was hoping to do this without using the on error stuff, because I wanted to use that for something else withing the same subroutine. I am sure I have seen something on these boards, but I can't find it. V.Frustrating.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can create a custom function:

Code:
Private Function PathExists(PName) As Boolean
    Dim x as String
    On Error Resume Next
    x = GetAttr("ThePath") And 0
    If Err = 0 Then PathExists = True Else PathExists = False
End Function

Then call the function from your subroutine:

Code:
If Not PathExists("YourPath") Then
'*** Your code to create the directory goes here***
End If
 

philR

Active Member
Joined
Feb 25, 2002
Messages
257

ADVERTISEMENT

That's much better! Thanks!
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Have a look at the FolderExists method of the FileSystemObject in VBA help. You can use it in something like this (you'll need to set a reference to MS Scripting Runtime from the Tools-References menu): -

Code:
Public Sub CheckFolder()
Dim myFileSystem As New Scripting.FileSystemObject
Dim myFolder As Scripting.Folder
Dim DestFolder As String
Dim wb As Workbook
Dim ans As Integer
Dim PathAndFile As String

Set wb = ActiveWorkbook
DestFolder = "C:My Document"
PathAndFile = DestFolder & "" & wb.Name

If Not myFileSystem.FolderExists(DestFolder) Then
    ans = MsgBox("Folder does not exist. Create it and save file?", vbYesNo + vbInformation)
    If ans = vbYes Then
        myFileSystem.CreateFolder (DestFolder)
        wb.SaveAs PathAndFile
    End If
End If

End Sub
 

Elemental

Board Regular
Joined
Jul 13, 2002
Messages
110
:)

mudface, that looks like what im after to help with somthing im working on,

but what steps do i need to take to use the myFileSystem and scripting bits ?
 

Forum statistics

Threads
1,144,363
Messages
5,723,919
Members
422,527
Latest member
JayTheKaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top