Capture the file names of a specified drive

Topic

New Reply

Good Morning everyone,

I need code to capture the file name(s) from a specified drive and append the names to an existing table in Access 2010. Also, a nuance to this code, would determine if the file name already exists in the existing table. If it does, don’t append it.

I think the code will have something to do with GetFileObject function but not certain. Any assistance with code is greatly appreciated.

Reply | Quote

Replies

Roberta,

Here’s some VBA code written for Excel but should work, with appropriate modification, in Access. Please note not all the variables are declared {dim} because they are Global or Module level variables and declared elsewhere. You should always declare your variables in your code.

Code:

'                    +------------------------------+             +----------+
'--------------------|      bLocate_Files()         |-------------| 03/15/10 |
'                    +------------------------------+             +----------+
'Parameters: zPath - A string with the DirectoryPath information to search.
'Returns:    True:  Files found
'            False: No matching files found.

Function bLocate_Files(zPath As String) As Boolean

   iListCntr = 0
   zFileList(iListCntr + 1, 1) = Dir(zPath & "" & zBaseFName & "*.xls*")
   
   Do While zFileList(iListCntr + 1, 1)  ""
    iListCntr = iListCntr + 1
    zFileList(iListCntr, 2) = zPath
    zFileList(iListCntr + 1, 1) = Dir()
   Loop    'Loop while there are files in the directory

   bLocate_Files = IIf((iListCntr > 0), True, False)
   
End Function         'bLocate_Files()

Hope this helps. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks RG, I will give this a try and update then.

Reply | Quote