Digital File Database

[Scott13]

I work in a QA Department and am responsible for thousands of documents. We are going through a System renovation and I'm looking for some help.

I have an access database that lists all the documents produced by my shop that we have just started using.

What we are looking to do is to do is to apply the same to previous created files.

Where I need help with is I need the ability to import the file locations of all the digital files in a directory and then, through the use of a form, apply the new category and naming conventions, then upon saving the form, moving the file to the new location and renaming it to the specified name format.

I hope that makes sense. I can attach a mostly blank database for what I currently have if that will help any.

Thanks!

 

[alansidman]

Here is some VBA that will allow you to go to a specific folder and pull in all files into an excel spreadsheet. You can then import/append the excel spreadsheet(s) into your db table(s) and add what ever data you need in your form.

Sub ListAllFile()
     
    Dim objFSO As Object
    Dim objFolder As Object
    Dim objFile As Object
    Dim ws As Worksheet
    Dim sPath As String
    Dim lrA As Long
    Dim lrB As Long
     
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set ws = Worksheets.Add
     
     'Get the folder object associated with the directory
    sPath = InputBox("What is the full Path to Search?")
    Set objFolder = objFSO.GetFolder(sPath)
    ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"
    ws.Cells(1, 2).Value = "The files found have modified dates:"
    ws.Cells(1, 3).Value = "The file Size is:"
     
     'Loop through the Files collection
    For Each objFile In objFolder.Files
        lrA = Range("A" & Rows.Count).End(xlUp).Row
        lrB = Range("B" & Rows.Count).End(xlUp).Row
        ws.Range("A" & lrA + 1).Value = objFile.Name
        ws.Range("B" & lrB + 1).Value = objFile.DateLastModified
        ws.Range("C" & lrB + 1).Value = objFile.Size
        Next
        'ws.Cells(2, 1).Delete
     'Clean up!
    Set objFolder = Nothing
    Set objFile = Nothing
    Set objFSO = Nothing
     
End Sub

 

[Scott13]

Alan,

Thanks for that. I've used something similar in excel and it helped immensely.

What I would ideally like to have is the ability to do the same from within Access, then (hopefully through a form) evaluate each file, determine if it needs to kept or deleted.

If it is meant to be kept, I would like the file to be renamed based on field values then moved to the new directory.

Is something like possible from within access or am I stepping into a realm where I need to convince IT to buy some 3rd party software?

 

[alansidman]

I don't have another solution for you. If I were to do this, I would do it as I indicated earlier. However, you may be able to amend the code I provided and have the same data imported directly into an Access Table. There is no generic way to do this.
,

 

[welshgasman]

I'm not an expert, but the way I would approach this problem would be

Give the user the ability to browse to the folder in question
Browse For Folder

Use the DIR() function to get all filenames in a selected folder and insert into a table
Help with checking if files exist - Access World Forums

The above uses a query. A VBA recordset could also do the job.

Now you have the data in a table build a form that allows the user to select a record. Use the same browse folder method to get a new location and a button to copy/move the file to that new location.

VBA to copy a file from one directory to another - Stack Overflow
ASP MoveFile Method

HTH