CODEWITHSUNDEEP
ms-accessms-access-2007vba
noelmcg
noelmcg

Reputation: 1067

Automatically close open files in a specific directory

Is it possible using VBA to close all open files in a given directory, with out knowing filename/extension etc...

EDIT .....

I have directories linked to records, for example the record for Joe Bloggs has a directory created related to the name eg Bloggs, Joe

If the user changes the records names, the folder name therefore has to reflect this.

Currently I can change the directory name no problem if all associated files are closed. Also if these files are open I can prompt the user to close the associated files.

I was wondering would it be possible to close/ prompt to save the files?

EDIT AGAIN .....

To further complicate matters there are further directories in the directory tree that relate to each record. (I should have been clearer at the beginning, the database stores info on household insurance claims)

So you have a directory structure like so:

User Name _ID \ 1st line of Property Address _ID \ Claim No _ID

Thanks in advance for any help

Cheers

Noel

Upvotes: 1

Views: 1389

Answers (1)

HansUp
HansUp

Reputation: 97111

I think you can uncomplify this thing. Your Users table should have a primary key, user_id. Say Joe Bloggs' user_id is 27. Create the folder for him as C:\userdirs\27. If Joe's name is later changed, his user_id and user folder can stay the same.

If your users need access to those folders by user name rather than user_id, create shortcuts for them.

Public Function CreateUserDirShortcut(ByVal pLinkFolder As String, _
        ByVal pLinkName As String, _
        ByVal pTargetFolder As String) As Boolean

    Dim objShell As Object
    Dim objLink As Object
    Dim strMsg As String
    Dim blnReturn As Boolean

On Error GoTo ErrorHandler

    Set objShell = CreateObject("WScript.Shell")
    Set objLink = objShell.CreateShortcut(pLinkFolder & Chr(92) & pLinkName & ".lnk")
    objLink.Description = pLinkName
    objLink.TargetPath = pTargetFolder
    objLink.Save
    blnReturn = True

ExitHere:
    Set objLink = Nothing
    Set objShell = Nothing
    CreateUserDirShortcut = blnReturn
    On Error GoTo 0
    Exit Function

ErrorHandler:
    strMsg = "Error " & Err.Number & " (" & Err.Description _
        & ") in procedure CreateUserDirShortcut"
    MsgBox strMsg
    blnReturn = False
    GoTo ExitHere
End Function

Then you can create a shortcut to Joe Bloggs' user directory like this:

CreateUserDirShortcut "C:\shortcuts", "Bloggs, Joe", "C:\userdirs\27")

Upvotes: 2

Related Questions