ms access/SQL question

[silverwhisper]

i know there's a way to make access run a macro or something that will make it pull the filenames in a given folder and produce a list of text. how exactly does one go about doing that? i'm feeling enormously stupid here.

[sighs]

ed

 

[naughtygekgrl]

if you want a list of files in a folder can't you just use dos? dir > doc.lst
unless you want it formatted a certain way

 

[Hotcappucino]

Copy & paste the following function into an Access module. It's heavily commented, so it should be pretty easy to follow:

Function SaveFileList()
    Dim objFileSystem, objFolder, objFile, objFileCollection
    Dim strFiles As String
    Dim hdlFile As Integer
    Dim strOutPath As String
    
    ' Get next available handle for opening file
    hdlFile = FreeFile
    
    ' Get folder where database is located
    strOutPath = Application.CurrentProject.Path
    
    ' Open a text file in the same folder as the database to store our file names
    Open strOutPath & "\FILES.TXT" For Output As #hdlFile
    
    ' Create the filesystem object
    Set objFileSystem = CreateObject("Scripting.FileSystemObject")
    
    ' Create a folder object from the filesystem object, using the path
    ' where the database is located.
    Set objFolder = objFileSystem.GetFolder(Application.CurrentProject.Path)
    
    ' Get the collection of files from the folder
    Set objFileCollection = objFolder.Files
    
    ' Cycle through each file in the file collection
    For Each objFile In objFileCollection
        ' Print the name of each file to the text file.
        Print #hdlFile, objFile.Name
    Next
    
    ' Close the output file
    Close #hdlFile

End Function

Next, create a macro that will call this function using a RunCode command. When you execute the macro, a text file called FILES.TXT will be created that contains the names of all of the files in the same folder where the Access database is located. The code can be modified to pass in the name of the folder whose files you'd like to capture. Or, you can also modify the code to create one large string for outputting to a form field or a MsgBox.

If you'd like a sample database with the code and the macro already set up, let me know your e-mail address and I'll send it to you.

 

[silverwhisper]

hotcappuccino: many thanks, will try the code you've posted. if i need more help, i'll probably PM you. :>

naughty: yes, i could, but using the DOS command will only show me the files. it won't give me the filenames in a form i can utilize to perform some data manipulations.

ed

 

[silverwhisper]

hotcappuccino: that was very kind of you and i really appreciate it. thanks so much! it's saved me 3+ hours of work each week!

i really gotta learn VBA properly.

ed