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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

snafus87@ptd.net - 24 Feb 2007 19:18 GMT

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.

Reply to this Message

Ken Snell (MVP) - 24 Feb 2007 19:51 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Hello,
> I have a recordset that has a list of file names and folder names. I
[quoted text clipped - 14 lines]
>
> Thoughts? Thanks in advance.

Reply to this Message

snafus87@ptd.net - 24 Feb 2007 21:37 GMT

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

Reply to this Message

Ken Snell (MVP) - 25 Feb 2007 01:47 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> Ken,
>
[quoted text clipped - 24 lines]
>
> Loop

Reply to this Message

snafus87@ptd.net - 26 Feb 2007 05:07 GMT

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

Reply to this Message