Access VBA, open a windows folder and filter the contents-CodePudding

i am trying to open a windows folder from access (2016) and only display some files. The folder contains 1000's of files, (client files) the all start with the client ref number EG G12345 or H12456 then a space then what the file is. I want to open the folder and only show the files for the current client on access.

I want d:\data\clients\correspondence\G12345*.*

I currently have a button on the client page that does this (cRef = Client Ref)

Foldername = "d:\data\clients\correspondence\" & cRef & "*.*"
Shell "C:\WINDOWS\explorer.exe """ & foldername & "", vbNormalFocus

But it just opens my default file location (C:\documents) what am I doing wrong. Thanks

CodePudding user response：

As @June7 has suggested, you can list matching files in a listbox. A small piece of VBA code can be used to create a string of file names:

Function fGetFileList(strFolder As String, strFileStart As String) As String
    On Error GoTo E_Handle
    Dim strFile As String
    Dim lngFileLen As Long
    If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
    lngFileLen = Len(strFileStart)
    strFile = Dir(strFolder)
    Do
        If Left(strFile, lngFileLen) = strFileStart Then fGetFileList = fGetFileList & strFile & ";"
        strFile = Dir
    Loop Until strFile = ""
    If Right(fGetFileList, 1) = ";" Then fGetFileList = Left(fGetFileList, Len(fGetFileList) - 1)
fExit:
    On Error Resume Next
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fGetFileList", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

You can then create a form that has two text boxes (txtFolder to contain the search folder, and txtStartName to contain the starting part of the file names to search for), a list box (lstFiles) that has its RowSourceType set to "Value List", and a command button (cmdSearch) that runs the search:

Private Sub cmdSearch_Click()
    On Error GoTo E_Handle
    Me!lstFiles.RowSource = fGetFileList(Me!txtFolder, Me!txtStartName)
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "frmFiles!cmdSearch_Click", vbOKOnly   vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub

CodePudding user response：

I was thinking about using fso to get to the folder and go through the files. When the program finds a file where the function Left matches the cRef it opens the folder. This program doesn't look through the underfolders of this folder. But You can use this code to change it to look for folders names in the parent folder and open those who match the cRef. Hope this can help you and find a solution for Yourself.

Public Sub OpenFolder()
Dim Fso                     As FileSystemObject
Dim Folder                  As Object
Dim File                    As Object
Dim cRef                    As String

cRef = "G12345" 'for example
Set Fso = New scripting.FileSystemObject
Set Folder = Fso.GetFolder("d:\data\clients\correspondence\")

For Each File In Folder.Files
   If Left(cRef, 6) Then
      Shell "C:\WINDOWS\explorer.exe """ & Folder & "", vbNormalFocus
      Stop 'so that you can work on your findings or Exit For to stop it
   End If
Next 'file

End Sub

You have to turn on microsoft Scripting runtime for this to work