creating a table of file names

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a folder on the desktop with serval hundred files in it. If I make a
table in Access called FolderNames with one field called Names, is there some
code that I could run that would import the name of every file name from that
folder in that table?
thanks,
ck
 
This works for me

You will need Reference to Microsoft Office Object Library

Sub LoadMinis()

Dim myRootPath As String, myFileName As String, intCount As Integer
Dim db As Database, rs As Recordset
Dim myTable As String

myRootPath = "D:\DSI\Data\MiniPlans\" 'Your Folder Location
myTable = "MiniBuilderAddOn" 'Your Table
With Application.FileSearch
.NewSearch
.LookIn = myRootPath
.SearchSubFolders = False
.FileName = "*.*"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Set db = CurrentDb
Set rs = db.OpenRecordset(myTable)
For intCount = 1 To .FoundFiles.Count
myFileName = Mid(.FoundFiles(intCount ), Len(myRootPath)
+ 1, (Len(.FoundFiles(intCount )) - Len(myRootPath) - 4))
If myFileName = "" Then GoTo OutOfThere
With rs
.AddNew
!PictureName = myFileName
.Update
OutOfThere:
End With
Next intCount
rs.Close
Set db = Nothing
Else
End If
End With

End Sub
 
Well, here is a decidedly low-tech alternative to the above.
'----------------------------
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")
Click to expand...

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

Basically, it executes a DOS command to write the filenames to a text file
on your C: drive. Then it empties your table and uses the Transfer Text to
import them into the table. Put it in a Module and run it. The first time
you do, you will get an error because you don't have an Import Spec.
Manually import the file once and save the import spec. From that point, it
should run.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "DirectoryList.mdb" which illustrates how to do this and shows
different alternatives including code that will suspend the execution of
your program until the DOS command finishes.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Dim strFile As String
Dim strFolder As String
Dim strSQL As String

strFolder = ..... ' Make sure there's a \ at the end of it

strFile = Dir$(strFolder & "*.*")
Do While Len(strFile) > 0
strSQL = "INSERT INTO FolderNames(Names) " & _
"VALUES('" & strFile & "')"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop

That'll strictly write the name of the file to the table. If you want the
full path to the file, replace the assignment to strSQL with

strSQL = "INSERT INTO FolderNames(Names) " & _
"VALUES('" & strFolder & strFile & "')"
 
Back
Top