Solved: Creating and checking the existence of directories in VBA

[sandam]

Can you, in VBA:
A. Check if a directory is already present on a drive
B. Create a directory with VBA if it doesn't?
C. Without any user input either?

I'm really hoping you can.

Thanks In Advance
Andrew;?

[Killian]

You can use the Scripting.FileSystemObject for this: GetFolder will set a folder path to an ojbect. If It doesn't exist there'll be a trappable error, but use the FolderExists property to test first. and then theres CreateFolder too. Perfect.
Here's a random snippet from a vbs script I have to hand
[VBA]Set filesys = CreateObject("Scripting.FileSystemObject")

set wdAddIn = filesys.GetFile(sourcefolder & "BB_PitchBook_AddIn.dot")
set wdTemp = filesys.GetFile(sourcefolder & "BB_PitchBook_Tables.dot")
set pptAddIn = filesys.GetFile(sourcefolder & "BB_PitchBook.ppa")
set pptTemp = filesys.GetFile(sourcefolder & "BB_PitchBook.pot")
set iniFile = filesys.GetFile(sourcefolder & "BB_PitchBook.ini")
set xlAddIn = filesys.GetFile(sourcefolder & "BB_ChartTools.xla")
set errlog = filesys.GetFile(sourcefolder & "ERROR.LOG")

If Not filesys.FolderExists(templates_path) Then
newfolder = filesys.CreateFolder (templates_path)
End If
If Not filesys.FolderExists(dev_sol_path) Then
newfolder = filesys.CreateFolder (dev_sol_path)
End If
If Not filesys.FolderExists(prog_files_path) Then
newfolder = filesys.CreateFolder (prog_files_path)
End If

filesys.CopyFile wdAddIn, wrkgrp_templates_path, True
filesys.CopyFile wdTemp, templates_path, True
filesys.CopyFile pptAddIn, prog_files_path, True
filesys.CopyFile pptTemp, templates_path, True
filesys.CopyFile iniFile, prog_files_path, True
filesys.CopyFile xlAddIn, xl_addin_path, True
filesys.CopyFile errlog, prog_files_path, True[/VBA]

oops
forgot to mention that creating scripting objects at runtime can cause a few anti-virus progs to panic. Not that I've ever come across it but it's worth noting

[sandam]

Thanks killian. I'll give it a bash tomorrow. i'm off home now :w00t:

Andrew;?

[JonPeltier]

I use these to check for the existance of files or directories. I snagged FileExists from a post by VB MVP Karl Peterson, and made a minor tweak to get DirExists.
[VBA]''================================================
Function FileExists(ByVal FileSpec As String) As Boolean
' Karl Peterson MS VB MVP
Dim Attr As Long
' Guard against bad FileSpec by ignoring errors
' retrieving its attributes.
On Error Resume Next
Attr = GetAttr(FileSpec)
If Err.Number = 0 Then
' No error, so something was found.
' If Directory attribute set, then not a file.
FileExists = Not ((Attr And vbDirectory) = vbDirectory)
End If
End Function
''================================================
Function DirExists(ByVal FileSpec As String) As Boolean
' Karl Peterson MS VB MVP
Dim Attr As Long
' Guard against bad FileSpec by ignoring errors
' retrieving its attributes.
On Error Resume Next
Attr = GetAttr(FileSpec)
If Err.Number = 0 Then
' No error, so something was found.
' If Directory attribute set, then it's a directory.
DirExists = ((Attr And vbDirectory) = vbDirectory)
End If
End Function
''================================================
[/VBA]

To create a directory, use MkDir:
[VBA] MkDir "C:\Test\Temp1"[/VBA]
If the new directory's parent directory does not exist, you will get an error.

These techniques are old-fashioned enough not to cause your Auntie Virus to panic.

[sandam]

Thanks Jon, I tested the code and it worked great. It was pretty much exactly what I was looking for. And thank you again Killian for your help as well. This problem is officially solved

WOOHOO

Andrew;?