Search root directory for subdirectory and create if it doesn't exist

[snafus87]

Hello,
I have a recordset that has a list of file names and folder names. I
have the parent directory fixed. What I need to do is search through
the parent directory. If this folder with the name from the recordset
doesn't exits I need to create it. I can't figure out how to do this
in code. I'm using Access 2003 VBA.
Some more specific details:

recordset is query opened with 2 fields (FileName and FolderName)
myParentFolder = "G:\RR\Prod Info\Scanned Data\"
mySubFolder = rs.FolderName

I want to search myParentFolder for mySubFolder. If mySubFolder
doesn't exits, I need to create it.
I was thinking something along the line of a With
Application.Filesearch.SearchFolders

Thoughts? Thanks in advance.

 

[Ken Snell \(MVP\)]

Try this:

myParentFolder = "G:\RR\Prod Info\Scanned Data\"
mySubFolder = rs.FolderName
Dim strFolderName As String
strFolderName = Dir(myParentFolder & mySubFolder, vbDirectory)
If strFolderName = "" Then MkDir myParentFolder & mySubFolder

 

[snafus87]

Ken,

Thanks a million,

Except it works just fine for the first record. Then it stops. There
are 72 records in the recordset, and unless I open it as a Dynaset and
then moveLast and MoveFirst, I only get a count of 1.

However, when opened as Dynaset the loop below gets stuck on the first
record.

How can I correct this?

Set query = Access.CurrentDb.QueryDefs(myQuery)
Set rs = query.OpenRecordset(dbOpenDynaset)
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF
Dim mySearchFolder As String
mySearchFolder = Dir(myParentFolder & mySubFolder,
vbDirectory)
If mySearchFolder = "" Then
MkDir myParentFolder & mySubFolder
End If
rs.MoveNext

Loop

 

[Ken Snell \(MVP\)]

You're not resetting the mySubFolder variable within the loop. I've also
made a few more changes to your code.



Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim mySearchFolder As String, myParentFolder As String
Dim mySubFolder As String
myParentFolder = "G:\RR\Prod Info\Scanned Data\"
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset(myQuery, dbOpenDynaset)


Do Until rs.EOF = True
' I assume that FolderName is a field in the rs recordset, so use ! not .
mySubFolder = rs!FolderName
mySearchFolder = Dir(myParentFolder & mySubFolder,
vbDirectory)
If mySearchFolder = "" Then
MkDir myParentFolder & mySubFolder
End If
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
dbs.Close
Set dbs = Nothing

 

[snafus87]

Thanks for the mod to the code...works like a charm!!