Check if folder exists, if not create one... 1

[SQLBI]

Hi,

I have some Excel code that saves a workbook onto a network drive, however the folder it needs to go into is month specific and i'd like to remove any chance of someone running the code and the month folder not being there.

Therefore i'd like the code to initially check if the folder exists and if it doesn't, create it.

I'm certain this can be done, but don't have the first clue where to start.

Thanks in advance.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[robcarr]

Leigh,

Someone created this for me a while ago, I specify what folder to search, ie september and if it finds it in the drive location it will create an october in the same place, if october already exists it doesnt create a folder, hope this helps.

Sub Create_Foldersm()
' Add reference to Microsoft Scripting Runtime before running this code
Dim fso As FileSystemObject
Dim drvCollection As Drives
Dim drv As Drive
Dim fld As Folder

Set fso = CreateObject("Scripting.FileSystemObject&quot

Set drvCollection = fso.Drives
For Each drv In drvCollection
If drv.DriveType = Fixed Then 'Or drv.DriveType = Remote Then
Set fld = fso.GetFolder("M:\Management&quot
Call FindSubFolderm(fso, fld)
End If
Next
Mainform.Show
End Sub
Private Sub FindSubFolderm(fso, fld)
Dim fldCollection As Folders
Dim subFld As Folder
Dim createdFld As Folder
curmonth = [c3].value
nmonth = [d3].value
Set fldCollection = fld.SubFolders
If fldCollection.Count <> 0 Then
For Each subFld In fldCollection
If UCase(subFld.Name) = curmonth Then
If Right(subFld.ParentFolder, 1) = &quot;\&quot; Then
If fso.FolderExists(subFld.ParentFolder & nmonth) = False Then
Set createdFld = fso.CreateFolder(subFld.ParentFolder & nmonth)
End If
Else
If fso.FolderExists(subFld.ParentFolder & &quot;\&quot; & nmonth) = False Then
Set createdFld = fso.CreateFolder(subFld.ParentFolder & &quot;\&quot; & nmonth)
End If
End If
End If
Call FindSubFolderm(fso, subFld) 'This is a recursive Call
Next
End If
End Sub

Thanks Rob.

 

[SQLBI]

Rob,

This looks spot on, thanks for your help.

Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk