"COMSPEC" Problem

[Masoud]

Hello

I used the code for creating a table of file names that there is in
www.rogersaccesslibrary.com (DirectoryList.mdb), but it does not work in my
computer .My computer has windows vista, Access 2007.
Please help me.
Thanks.

 

[Albert D. Kallal]

I not sure what that code is/was (I can't see it nor find it).

but, I use:

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

lets printout the stuff into debug window for a test

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i

End Sub

Sub FillDir(startDir As String, strFil As String, dlist As Collection)

build up a list of files, and then
add add to this list, any additinal
folders

Dim strTemp As String
Dim colfolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir & strFil)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

now build a list of additional folders
strTemp = Dir(startDir & "*.*", vbDirectory)

Do While strTemp <> ""
If (GetAttr(startDir & strTemp) And vbDirectory) = vbDirectory Then
If (strTemp <> ".") And (strTemp <> "..") Then
colfolders.Add strTemp
End If
End If
strTemp = Dir
Loop

now process each folder (recursion)
For Each vFolderName In colfolders
Call FillDir(startDir & vFolderName & "\", strFil, dlist)
Next vFolderName

End Sub

Sub FillDir(startDir As String, dlist As Collection)

build up a list of files, and then
add add to this list, any additional
folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

now build a list of additional folders
strTemp = Dir(startDir & "*.", vbDirectory)

Do While strTemp <> ""
If (strTemp <> ".") And (strTemp <> "..") Then
colFolders.Add strTemp
End If
strTemp = Dir
Loop

now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist)
Next vFolderName

End Sub



If you wanted to pull the names to a table, then just go:

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer
dim rstData as dao.RecordSet

startDir = "C:\access\"
Call FillDir(startDir, dlist)

MsgBox "there are " & dlist.Count & " in the dir"

lets printout the stuff into debug window for a test
set rstData = currentdb.OpenRecordSet("tblMyFiles")
For i = 1 To dlist.Count
rstData.AddNew
rstData!FileName = dlist(i)
rstData.Update
Next i
rstData.Close

msgbox "done"

 

[Masoud]

thank you for your code.

the code i used before is like below (it works in xp,access 2003 but it does
not work in vista, access 2007):

Sub ReadDesktopFolder()
Dim lPid As Long
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

lPid = Shell(Environ("COMSPEC") & " /c dir " & Chr(34) & "C:\Documents and
Settings\carlrj\Desktop\TestFolder" & Chr(34) & "\*.* /a:-d /b

c:\myfiles.txt")

cnn.Execute "Delete * from FolderNames"
DoCmd.TransferText acImportDelim, "Myfiles Import Specification",
"tblFiles", "c:\myfiles.txt", False, ""
End Sub