Test if a folder exists

  • Thread starter Thread starter Jeff Marshall
  • Start date Start date
J

Jeff Marshall

Hi,

I am making a folder using VBA called "SeptInv" in C:\Billing\Invoices\ .

However before the folder is made I don't know how to;

1. Test if the SeptInv folder has already been made and
2. Once it has been made that my VBA macro doesn't try to make it again.

Thanks in advance for any help.
Jeff
 
You could use an error trapper as mine that I use to backup from current
directory

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub
 
Jeff,

Two possibilities.

1. Use DIR to test, and FileSystemObject to create it

Dim myDir, myFile

myFile = "C:\Billing\Invoices\SepInv\"
myDir = Dir(myFile)
If myDir <> "" Then
MsgBox "Directory already exists"
Else
myDir =
CreateObject("Scripting.FileSystemObject").createFolder(myFile)
End If


2. Use FileSystemObject for both

Dim myDir, myFile

myFile = "C:\Billing\Invoices\SepInv\"
myDir = CreateObject("Scripting.FileSystemObject").FolderExists(myFile)
If myDir = True Then
MsgBox "Directory already exists"
Else
myDir =
CreateObject("Scripting.FileSystemObject").createFolder(myFile)
End If




--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Here are three different approaches gathered from previous postings.

-----------------------------------------------------------

Public Function IsDir(ByRef strPath As String) As Boolean
'' Does the directory exist?

If Not Dir(strPath, vbDirectory) = vbNullString Then IsDir = True

End Function

-----------------------------------------------------------

Sub MakeDirectory()
Dim Dirname As String
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Dirname = "C:\Data"
If Not fs.FolderExists(Dirname) Then
fs.CreateFolder Dirname
' your code
Else
' your code
End If
End Sub

Courtesy of a posting by Ron De Bruin

-----------------------------------------------------------

An easy approach is to just attempt to create the folder.

If it exists, the attempt fails, so you ignore the error - otherwise
it is created:

On Error Resume Next
MkDir ThisWorkbook.Path & "\" & "Temp"
On Error goto 0

Tom Ogilvy
 
Jeff,

I went to Google. You should find this post by Stephen Bullen to be very
helpful.
http://groups.google.com/groups?q=e...e=UTF-8&selm=VA.0000043d.02cd6784@mars&rnum=1

I will save you the trouble and simply paste it here.

Best regards,
Kevin



Hi Rajko,
I have a problem if I want to write a file to a folder which not exist.
How can I check if folder exists, then create it and write a file.
Click to expand...

There are actually four outcomes to consider:

1. The folder exists - we're OK
2. The folder doesn't exist and there is no file of the same name - we
can create the folder and be OK
3. The folder doesn't exist and there IS a file of the same name - we
can't create the folder
4. A disk error ocurred during 1-3.

I might be tempted <g> to write a routine for it like:

Sub Test()

MsgBox CheckFolder("C:\Test")

End Sub

Function CheckFolder(sPath As String) As Boolean

On Error GoTo ERR_DISKERROR

'Dir checks files as well as directories
If Dir(sPath, vbDirectory) <> "" Then

'Is it a directorythat we found, or a file?
If GetAttr(sPath) And vbDirectory Then

'It's a directory, so we're OK
CheckFolder = True
Else
'What to do if you can't create the directory
End If
Else
'The directory doesn't exist, nor does a file of the same name
MkDir sPath
CheckFolder = True
End If

Exit Function

ERR_DISKERROR:

'Handle the error how you like, and terminate the function
'examples are:
' Drive doesn't exist/not accessible (e.g. A:/Test without a disk in)
' Invalid path string (e.g. contains invalid characters)
' Disk full when creating folder
' Disk not writable
' Path too long
' etc.

End Function


Regards

Stephen Bullen
Microsoft MVP - Excel
http://www.BMSLtd.co.uk
 
You macro will raise an error if it tries to make the folder and it already
exists. However, it is easiest to just trap the error and move on.

On Error Resume Next
mkdir "C:\Billing\Invoices\SeptInv"
On Error goto 0

' at this point you know it exists.

this assumes "C:\Billing\Invoices" exists. If you can't assume that

On Error Resume Next
mkdir "C:\Billing"
mkdir "C:\Billing\Invoices"
mkdir "C:\Billing\Invoices\SeptInv"
On Error goto 0
 
Hi All

Can I add to this by asking once you have established if a folder exists,
can you do the same for a file that may or may not be in that folder? For
example if "MyFile" was found to be in the folder already, could the new
file be saved as "MyFile_02" and then the next time the procedure is run
"MyFile" would be saved as "MyFile_03" and so on.

Thanks

Mick
 
Back
Top