Display sub-directory on a list box.

[Raymond To]

Hello.

I am try to write a code for the list box to
display files on the current directory and
files on all sub-directories.

Can anyone help me please?

Here are the code I wrote for display *JPG file from
current directory. And now I want to do something
which able to display all sub-directory.

Private Sub cmdDISPLAY_JPG_Click()
Dim MyPath, MyName
Dim sLIST As String
Dim sSQL As String
Dim rsDISPLAY

MyPath = Nz(Me.txtDIR, "") ' Set the path.
MyName = Dir(MyPath, vbDirectory)
' Retrieve the first entry.
sSQL = "DELETE * FROM tbl_PICTURE"
dbPICTURE.Execute sSQL, dbSeeChanges

Do While MyName <> "" ' Start the loop.
If UCase(Right(MyName, 4)) = ".JPG" Then

sSQL = "INSERT INTO tbl_PICTURE"
sSQL = sSQL & " (P_DIR, P_FILE"
sSQL = sSQL & " ) VALUES ("
sSQL = sSQL & Chr$(34) & Nz(MyPath, "")
& Chr$(34) & ", "
sSQL = sSQL & Chr$(34) & Nz(MyName, "")
& Chr$(34)
sSQL = sSQL & " )"
dbPICTURE.Execute sSQL, dbSeeChanges

End If
MyName = Dir ' Get next entry.
Loop
sLIST = "SELECT P_FILE, P_DIR FROM tbl_PICTURE"
Me.lstFILES.RowSource = sLIST
Me.lstFILES.Requery

End Sub

 

[Bryan Reich [MSFT]]

Break the code into a seperate function or subroutine so it can be called
recursievly:
Example code:

**** BEGIN CODE *****

Private Sub cmdDISPLAY_JPG_Click()

Dim MyPath, MyName
Dim sLIST As String
Dim sSQL As String
Dim rsDISPLAY

sSQL = "DELETE * FROM tbl_PICTURE"
dbPICTURE.Execute sSQL, dbSeeChanges

MyPath = Nz(Me.txtDIR, "") ' Set the path.

Call AddImagesRecursively MyPath

sLIST = "SELECT P_FILE, P_DIR FROM tbl_PICTURE"
Me.lstFILES.RowSource = sLIST
Me.lstFILES.Requery

End Sub

Private Sub AddImagesRecursively( directory as String )
Dim MyName As String
Dim sSQL As String
Dim dirs as Collection ' Need to keep dirs in separate collection
because
' Dir can't be called recursively

MyName = Dir(directory, vbDirectory)

Do While MyName <> ""
if GetAttr(MyName) = vbDirectory then
dirs.Add MyName
else
' Add the image here
If UCase(Right(MyName, 4)) = ".JPG" Then

sSQL = "INSERT INTO tbl_PICTURE"
sSQL = sSQL & " (P_DIR, P_FILE"
sSQL = sSQL & " ) VALUES ("
sSQL = sSQL & Chr$(34) & Nz(MyPath, "")
& Chr$(34) & ", "
sSQL = sSQL & Chr$(34) & Nz(MyName, "")
& Chr$(34)
sSQL = sSQL & " )"
dbPICTURE.Execute sSQL, dbSeeChanges

end if
end if

MyName = Dir
Loop

For Each MyName in dirs
AddImagesRecursively MyName
Loop

End Sub

***** END CODE *****

Hope this makes sense. Let me know if you have questions.

 

[rto]

Thanks Bryan, I will try the code.

Raymond

-----Original Message-----
Break the code into a seperate function or subroutine so it can be called
recursievly:
Example code:

**** BEGIN CODE *****

Private Sub cmdDISPLAY_JPG_Click()


Call AddImagesRecursively MyPath



Private Sub AddImagesRecursively( directory as String )
Dim MyName As String
Dim sSQL As String
Dim dirs as Collection ' Need to keep dirs in separate collection
because
' Dir can't be called recursively

MyName = Dir(directory, vbDirectory)

Do While MyName <> ""
if GetAttr(MyName) = vbDirectory then
dirs.Add MyName
else
' Add the image here
If UCase(Right(MyName, 4)) = ".JPG" Then
end if
end if

MyName = Dir
Loop

For Each MyName in dirs
AddImagesRecursively MyName
Loop

End Sub

***** END CODE *****

Hope this makes sense. Let me know if you have questions.
--
Bryan Reich
Microsoft Office
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm






.