How to get the directories on a drive in a string

  • Thread starter Thread starter tbrundel
  • Start date Start date
T

tbrundel

I am working in Access 2000 and have the following
problem.

I want to get a string with all the directories on one
drive (v:) to use in a listbox. I already tried to get it
using the Filesearch and SearchFolders objects but that
doesn´t seem to work...
 
How to Add Directory File Names to an Access Table:

Create a table named tblDirectory with 2 fields:
FileName (Text 250)
FileDate (Date/Time)

Call the code below by pressing Ctrl-G to open the debug window and type:
GetFiles("c:\windows\")

Paste this code into a regular module:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub

--
Joe Fallon
Access MVP



I am working in Access 2000 and have the following
problem.

I want to get a string with all the directories on one
drive (v:) to use in a listbox. I already tried to get it
using the Filesearch and SearchFolders objects but that
doesn´t seem to work...
 
Hi,

If you want *all* the folders and subfolders on a drive you can use a
command like this at a command prompt ("DOS window") to get a list of
them in a textfile that Access can import

DIR /B/ /AD /S V:\ > C:\Folder\Filename.txt

To show the format, here's a fraction of the listing of my C:\ drive:

C:\temp\xml
C:\temp\xx
C:\temp\DsoFile\VBTest
C:\temp\OffAutmn\C++
C:\temp\OffAutmn\MFC
C:\temp\OffAutmn\VB


Alternatively, if you're only interested in the top level of folders,
you can use this function, which uses the VBScript FileSystemObject
object:

Function GetFoldersAsValueList(FolderPath As String) As String
'Returns subfolders of specified folder as a
'list suitable for the rowsource of a listbox
'By John Nurick 2002
Dim oFS As Object 'Scripting.FileSystemObject
Dim oF As Object 'Scripting.Folder
Dim oS As Object 'Scripting.Folder
Dim strList As String
Const strDELIMITER = ";"

Set oFS = CreateObject("Scripting.FileSystemObject")

Set oF = oFS.GetFolder(FolderPath)

If oF.SubFolders.Count > 0 Then
For Each oS In oF.SubFolders
strList = strList & oS.Name & strDELIMITER
Next
'Delete superfluous delimiter from end
GetFoldersAsValueList = Left(strList, Len(strList) - 1)
Else
GetFoldersAsValueList = ""
End If
Set oS = Nothing
Set oF = Nothing
Set oFS = Nothing
End Function
 
Back
Top