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

New Member
Joined
Sep 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,458
Office Version
  1. 2013
Platform
  1. Windows
@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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
Just added another xlsx in the Archive:
1631719580917.png

And changed the one under New Folder:
1631719651274.png


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

Well-known Member
Joined
Sep 10, 2010
Messages
2,458
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,458
Office Version
  1. 2013
Platform
  1. Windows
@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

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

:) 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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088
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.

VBA Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,088

ADVERTISEMENT

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.
 
Last edited:

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
@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

Well-known Member
Joined
Sep 10, 2010
Messages
2,458
Office Version
  1. 2013
Platform
  1. Windows
@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

Well-known Member
Joined
Sep 10, 2010
Messages
2,458
Office Version
  1. 2013
Platform
  1. Windows
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
VBA Code:
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.
VBA Code:
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 :cool:
 

Forum statistics

Threads
1,148,241
Messages
5,745,583
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top