Hello,

I'm trying to write a macro that will open a document from a separate folder (I know how to do that) but that is specific to the current document and current folder.

For example, we are working on a case using NewReport, which is inside of MainFolder, and we need to open OldReport that is in SubFolder (of the main folder, which contains NewReport). The cases and folders have an extremely consistent file naming system; they are always named "253762954 OldReport 5/29/2018", or "253762954 NewReport 5/29/2019", with the numbers and dates changing for each specific case.

Is there a way to open a file that contains a keyword? ie., could VBA understand "go to the file path of this active document, go to this sub folder, and open the file in the subfolder that contains, "OldReport", in the file name"?



I just kinda threw together 2 lines that do similar things but not the right thing. I put 'Foldernames' in place of the actual directory and folder names, lol.

Private Sub CheckBox1_Click()
'OldReport
Shell Environ("windir") & "\Explorer.exe " & ActiveDocument.path, vbNormalFocus
Shell "Explorer.exe \\'Foldernames'\OldReport", vbNormalFocus
Me.Hide
End Sub