MrExcel Publishing
Your One Stop for Excel Tips & Solutions

LET A MACRO CHECK AND CREATE A DIRECTORY!!!


Posted by RONALD on October 08, 2001 10:42 PM

How can you let Excel see if there's a directory? For example:

Let a macro check if C:\CHECKDIR\ exists. If it does exists that it will exit the macro and if it doesn't exists that it will create this directory.

Hope someone can help me?

RONALD


Posted by Dank on October 09, 2001 12:15 AM

Ronald,

There are several ways you could do this. This method uses just built in VBA functions to achieve the objective and is simpler, although less tidy than other methods (e.g. using the Scripting filesystem object).

Sub CheckDirectory()
Dim sFolder As String, sExistence As String
On Error Resume Next

sFolder = "C:\Temp\Doesnt exist"

'Attempt to change the current Windows directory to sFolder. If an error
'occurs it will be trapped and the folder will be created.
ChDir sFolder
If Err.Number = 76 Then 'Folder doesn't exist
MkDir sFolder 'so create it
End If

'Rest of macro

End Sub

Regards,
Daniel.

Posted by Ronald on October 09, 2001 2:01 AM

Thanks A Million!!! It works!!!

Ronald