VBA - Opening or getting location for file where I don't know exactly where the file is saved or exactly what it is called

[excelquestion55]

At my job I have a bunch of products - about 1,600. For each product, we have recipe cards saved on excel. These are in a folder ("Meals") containing subfolders (eg. "Foils"). In these subfolder, are the excel recipe cards and another subfolder called "Archive" which has the old versions. If I have a product code eg. 324787, I would search this in file explorer and open the most up to date one. Can I do this on VBA? The other complication is that the recipes have no naming structure other than the fact that the 6 digit product code will be there somewhere. Thanks!

I have code to search for the file but I have no idea about how to then open the file - or get the file locations as I could then open it from this.

Sub SearchFiles()
Call Shell("explorer.exe " & Chr(34) & "search-ms:query= ***CODE*** &crumb=location: ***FILE LOCATION*** " & Chr(34), vbNormalFocus)
End Sub

 

[GWteB]

@Gokhan Aycan, FYI, your code is iterating the folder structure from downwards up, so it picks up the last modified file on the deepest folder level, ignoring a newer file elsewhere within that folder structure.

 

[Gokhan Aycan]

Just added another xlsx in the Archive:

And changed the one under New Folder:

Looks like working as intended. Number (0) is the newest, (1-2-3 etc) are in whatever order they were found. Newest is also listed as it is found. So 3 files total, 0 being added as a default value.

 

[GWteB]

I'm afraid you're really overlooking something. In a test with your code the last modified file in a certain folder structure shows up within the dictionary on the 34th place and with John_W's code (based on the Windows console DIR function) on the 7th place of the files listed. The DIR sort feature isn't reliable in this matter (if used on both folders and files simultaneously) and your code doesn't sort at all.

 

[GWteB]

@Gokhan Aycan, you're right !!!!!! Your code works as intended.
At first I had also (like the OP) to make some changes to get it work.
I hope you accept my apology.

 

[Gokhan Aycan]

You are right in the sense it is not sorting the files, however the most recent is set to choice of 0. I wonder what is the issue the OP has with it.

 

[John_w]

John - You're code is very good. However, there is one thing I hadn't realised. I some folders there is "current" and "archive" and it's opening the last modified in the archive folder - not the last modified in the "current" folder or last modified overall (is it cause archive is before current alphabetically). Also, could I modify this so it returns the file path in say cell A1 (without opening it so it's not too slow)

Try this modified macro, which ignores files found in the "Archive" folder. If found, it puts the file path in A1.

Public Sub Find_Product_Workbook()

    Dim mainFolder As String, productCode As String
    Dim dirLines As Variant
    Dim i As Long, foundFile As String
   
    mainFolder = "C:\path\to\Meals\"            'change
    productCode = "123456"                      'change
   
    If Right(mainFolder, 1) <> "\" Then mainFolder = mainFolder & "\"
    dirLines = Split(CreateObject("wscript.shell").exec("cmd /c DIR /B /S /A-D /O-D " & Chr(34) & mainFolder & "*" & productCode & "*.xlsx" & Chr(34)).StdOut.ReadAll, vbCrLf)
   
    foundFile = ""
    If UBound(dirLines) >= 0 Then
        i = 0
        While i < UBound(dirLines) And foundFile = ""
            If InStr(1, dirLines(i), "\Archive\", vbTextCompare) = 0 Then foundFile = dirLines(i)
            i = i + 1
        Wend
    End If
    If foundFile <> "" Then
        MsgBox "Found " & foundFile, vbInformation, "Find Product workbook"
        ActiveSheet.Range("A1").Value = foundFile
    Else
        MsgBox "Excel workbook with file name containing product code '" & productCode & "' not found in " & mainFolder & " and its subfolders", vbExclamation, "Find Product workbook"
    End If
   
End Sub

 

[John_w]

Both @John_w's and @Gokhan Aycan's code depend on the folder structure, so it is more or less a coincidence if the most recently modified file is found.

Yes, that could happen with my code because DIR first sorts in ascending order of ASCII folder name (folder AAA, then BBB, etc.), then the /O-D sorts files within each folder by their modified date/time, newest first. So if "*123456*.xlsx" occurs in AAA and BBB, it will return the file in AAA, even if the file in BBB is newer. I'll try to fix this issue because the DIR method is very fast.

 

[Gokhan Aycan]

@GWteB You mentioned something about making a change (at first) to make my code work. Can you elaborate if so? Was it the same issue OP is having? I wrote the code with early binding and later changed to late binding (removing the references) once it was working. I wonder if it is some odd file name or explorer setting that is causing the issue.

 

[GWteB]

@John_w, in the MSDos era I often struggled with this kind of thing, but (despite piping and redirectioning using Find and Sort) I could hardly ever solve this with one command line and had to use a batch file. I've just played around with it briefly, but I have the idea that there's something more involved to achieve the desired result when only using the command console. I am therefore curious about your solution.

 

[GWteB]

You mentioned something about making a change (at first) to make my code work. Can you elaborate if so? Was it the same issue OP is having?

@Gokhan Aycan,
No, it was something else but I can't remember exactly what. I do know that even on the first run my test file was not returned. Then I took a quick look at the structure of your code and left it for what it was. Until you made me doubt with your post #12, after which I again pasted your code into an empty workbook, modified the search folder and ran your code. Then the correct file was returned and also, with that file in a different folder, a second time after which I apologized to you.

Nevertheless, it still gnawed at me. Was I so wrong then the first time? Because I don't doubt myself so easily, I decided that I would go through your code line by line. Then the problem, which was initially difficult to reproduce, was quickly found. The cause lies in (reliance on) VBA's implicit vartype conversion.
The code line

If InStr(oFile.Name, strProductCode) And InStr(oFile.Path, ".xls") Then

is based on three (obviously unconscious and unintentional) assumptions.

The first two and most obvious assumptions are that the variable strProductCode is supposed to contain only numerical characters (after all, they don't have uppercase and lowercase) and that the extension name of the file involved (XLSX / XLSM / XLST / XLSB) can never appear in uppercase. My test involved several files whose extension name was in uppercase. They were not considered by your code when it came to date and time stamp.

The third and final assumption was that VBA would convert every single statement in the above line to a Boolean vartype. In a single statement, VBA does that automatically, but in a double statement, as above, VBA sticks to the vartype returned by the invoked Instr function, which is a Long vartype.
I should have seen this right away, but didn't. Since you made me doubt I made a file called blahdieblah563412 v5.xlsx, dropped it in a folder somewhere and then modified your code as per below.

If InStr(1, oFile.Name, strProductCode, vbTextCompare) And InStr(1, oFile.Path, ".xls", vbTextCompare) Then

Right in the first run meanwhile stepping through the code the file was ignored and then I got it. In the oFile.Name, the argument searched for (the code) started at position 12, while the ".xls" started at position 80. With a logical AND, the result of 12 And 80 is 0, and VBA converts that to False.
In my very first test I also used a random folder structure and a fresh (empty) file called 563412.xlsx but it was ignored several times. So in those scenarios, the ".xls" must have happened to have started in an even position. So I wasn't wrong the first time at all, although it did cost me some headaches ... LOL