Linking multiple individual folders

[d-mcc56]

Hello all

I am using Access 2010 and I am fairly new to it (started using it for work).

I have a table with a bunch of a agreement numbers (134) and I need to link each one to its folder on our company's shared G drive. The agreement numbers all follow the format A12.## (Where ## is the number of the agreement going from 1-134).

So I have the path to get to the location of all the agreement files, and I know there has to be a faster way than to edit each individual hyperlink, I myself just don't know how to do it.

Also the files on the shared drive start with the A12.## but then also continue with the name of the agreement. I was thinking that somehow for each record you could apply all at once the same path to the agreements and then for each individual record get it to search for its closest matching agreement number?? Any help on this would be great, thanks!

 

[MarkK]

Within VBA there is the Dir() function that you can use to enumerate the files and folders in a folder. I think it is a little faster too, if the number of files you are working with is large.

A little tidier to code is the FileSystemObject which provides a number of methods for file system manipulation.

Use the words in red as search terms for further information.

 

[d-mcc56]

VBA is very foreign to me and I've spent the last little bit reading up on FSO but it's all pretty confusing to me. Is there any insight you can provide? Thanks

 

[MarkK]

Sorry, I don't have time to answer such a general question. I have no idea what is foreign to you, or what you find confusing. Can you be more specific about the difficulties you've encountered?

 

[d-mcc56]

Okay so I have this code below and it opens up to the location of all the E12.## agreements. I also have a table in my database with a list of all these agreement numbers. I am unsure of how to make the code link with the table in the database and pair up the E12.## agreements with the table field

Option Compare Database
Public FSO As New FileSystemObject
Sub Sample()
    Dim Ret

    '~~> Specify your start folder here
    Ret = BrowseForFolder("G:\Planning\Development_Planning_Engineering\Development_Engineering\4) CONSTRUCTION\Commercial Projects\MIA's\E2012's")
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
     'Function purpose:  To Browser for a user selected folder.
     'If the "OpenAt" path is provided, open the browser at that directory
     'NOTE:  If invalid, it will open at the Desktop level

    Dim ShellApp As Object

     'Create a file browser window at the default folder
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)

     'Set the folder to that selected.  (On error in case cancelled)
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0

     'Destroy the Shell Application
    Set ShellApp = Nothing

     'Check for invalid or non-entries and send to the Invalid error
     'handler if found
     'Valid selections can begin L: (where L is a letter) or
     '\\ (as in \\servername\sharename.  All others are invalid
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Invalid
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Invalid
    Case Else
        GoTo Invalid
    End Select

    Exit Function

Invalid:
     'If it was determined that the selection was invalid, set to False
    BrowseForFolder = False
End Function

 

[MarkK]

But don't you already know the name of the folder in which these files are stored?

Have you read the VBA help article on the Dir() function? In Access there is help, but if you open a code window and hit F1, or click the toolbar->Help item, you get VBA help, which is more specific to programming. Find the article in VBA help about the VBA.Dir() function, which allows wildcard characters to do pattern matching so you can do partial matches of filenames in folders.