Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Files and paths and add insert into a table (2000)

    Hi all,

    I need to figure out how to find files on a network and populate a table with the file name, path, date modified, and size. The processing system at work places files in an unpredictable location each day. I need to get a list of a specific file that is created daily for many days so I can loop through each file and pull the records out that I need to do a trended analysis, but first I need to find these ramdomly placed files on the network!

    Any help is appreciated.

    Thanks,

    Richard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Files and paths and add insert into a table (2000)

    Unfortunately for you, the File Open dialog was only introduced in Access 2002 VBA. However, you can get it using Windows API calls. I have attached a zipped Access 97 database that contains a class module that acts a a wrapper for these calls, and a standard module containing some constants to be used in conjunction with the class module. Import both into your own database. (There is also a demo form)

    Once you have a file name, you can use the Scripting library to get the modified date etc. Here is a simple example that writes information to the Immediate window.

    Sub GetAFile()
    ' Uses the CommonDialog class module
    Dim dlg As New CommonDialog
    Dim fso As Object
    Dim f As Object

    With dlg
    ' Adapt the following to your needs
    .FileName = "*.txt"
    .InitDir = "C:Windows"
    .Filter = "Text Files (*.txt)|*.txt|All Files (*.*)|*.*"
    .Flags = cdlHideReadOnly Or cdlFileMustExist Or cdlPathMustExist

    ' Display File Open dialog
    If .OpenDialog() = True Then
    ' Use Scripting FileSystemObject
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFile(.FileName)
    Debug.Print f.Name, f.ParentFolder, f.DateLastModified, f.Size
    Else
    Debug.Print "No file selected"
    End If
    End With

    Set fso = Nothing
    Set dlg = Nothing
    End Sub
    Attached Files Attached Files

  3. #3
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Files and paths and add insert into a table (2000)

    Thanks Hans,

    This solves one of my other problems, however I am still not sure how to get the file information into a table. That will have a structure like this:

    File Name Path Size Modified

    The path would include all of the subdirectories of where the file is located for example I have a file called Accounts.csv that is placed in a reports a different subdirectory daily like this:

    O:reports20030704xyzaccounts.csv

    I know the path up until the day (20030704) but the subdirectory where the actual file is located is unknown, there are usually about fifty randomly named subdirctories (xyz,abc,123,456 etc.) What I would like to do is run a routine on O:reports and get a table in the above struture that lists the full path, size, and mod date for every accounts.csv file found under O:reports.

    thanks again

    Richard

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Find Files and paths and add insert into a table (2000)

    I have attached a small demo database that may help. It contains a table tblFiles, a form frmFileDemo and a module basFileCode.

    Open the form and click the button near the top. If all goes well, all Account.csv files in O:Reports will be listed.

    The initial path and the file name are specified in the code behind the command button. It calls more general code in the module. The file name may contain wildcards.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jul 2003
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Files and paths and add insert into a table (2000)

    Hans,

    Works great!

    Thank you very much

    Richard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •