open a dir and filter files with * ?

[drazen]

Hi, hope someone can help
I have a directory with 1000's of excel files the filenames are formatted A1234 17.xlxs
The A is a checksum, the 1234 is client ref then a space then a year.
I am trying to put a button on my DB that will open the folder and filter everything except this client selected on the DB. in win Explorer I would type *1234*
But when I do this in VBA it dosn't work. is it possible?
My current code is

Private Sub openfolder_Click()
Dim str_folder As String

str_folder = "\\server3\d\Excel Files CLients\Accounts\*1234*"

Call Shell("explorer.exe " & str_folder, vbNormalFocus)
End Sub

thanks

 

[The_Doc_Man]

Perhaps try to add ".XLSX" or ".XL*" to that file spec.

Note for future questioning:

when I do this in VBA it dosn't work

We love to help people but HATE to see this type of question. That is because it doesn't tell us why or how you KNOW that it doesn't work.

Does it give you an error popup message? Do you get a debugging message box with "Ignore" and "Debug" and "Reset"? Does it run without returning any files? Does the computer paint a full-screen sized, angry emoji giving you the finger? How do you know that it doesn't work?

With specific failure information, we know which direction to go. Since you have not made many posts, this type of question is easily forgivable, but please remember for future reference that saying "it doesn't work" tells us almost nothing.

 

[MarkK]

Are you trying to return the selected file name to your VBA code?

 

[jdraw]

You might review the FileDialog as a possible approach to solving your issue.
There is a youtube here with info.

Perhaps you could tell us more about what exactly you are trying to do. Plain English with no jargon please.

 

[drazen]

Sorry for the delay, just got back in the office.

We (Accountancy Firm) have an Access DB (2010) that controls 1000+ clients, there names, details, an event log for each client for work done. When A Client phones up we get there details up on Access, a lot of the time the client wants to talk about their accounts, which are stored on an Excel file in an 'Accounts Directory' ALL clients accounts are located in the same directory with the file format X1234 17.xlsx the X is a 'check' character, the 1234 is the client ref number and the 17 is the year the accounts are for.

When the client is on the phone it can take a while to locate the file(s) so I wanted a button on access that can use the ref number on the Access form and open the directory with only that ref number. in Explorer i would simple type *1234*.* But I can't do that with Access.

I just want the files to show in Windows explorer, so the user can then double click the files and it will open in Excel

Hope this helps
Thanks

 

[isladogs]

As it seems there will only be one file per client, use your button to only open that file using shell or by some other method.

In other words miss out the step of presenting the file in a folder.

 

[drazen]

unfortunately there may (and usually are) multiple files the 'Ref' part of the file name is the only constant for each client. The files could be :-

A1234 15.xls
A1234 16.xls
A1234 16 Amended.xls
A1234 17.xlsx

 

[isladogs]

But will you only need to open the latest one (17) for this situation?

 

[static]

Create a new form.
Add a list box called 'FileList'.
Add a command button called 'btnSearch'.
Add a textbox called 'txtClient'.
Paste the code into the form's module.

Const FOLPATH As String = "\\server3\d\Excel Files CLients\Accounts\"

Private Sub btnSearch_Click()
    With FileList
        'set up/clear list
        .RowSourceType = "value list"
        .RowSource = ""
        
        'find client files and add them to the list
        s = Dir(FOLPATH & "*" & txtClient & "*.xl*")
        Do Until s = ""
            .AddItem s
            s = Dir
        Loop
    End With
End Sub

Private Sub FileList_DblClick(Cancel As Integer)
    Shell "explorer.exe """ & FOLPATH & FileList & """"
End Sub

Type a search string in the textbox,
click btnSearch,
double click an item in the list.