3 Replies Latest reply on Jun 17, 2016 8:41 AM by JOHN GEORGE

    VBA Get File Name & Path from Windows Explorer Search Results

    Tom Fosler

      I have a VBA macro that is going to open a drawing. The thing is that I don't know where this drawing will be located in the file system, so I would like to use either the search functionality in the Open File dialog box or using the Shell command.

       

      I tried to use this: RetVal = Shell("c:\windows\explorer.exe " & "search-ms://query=" & Filename & Paths & """", vbNormalFocus)

       

      But as of yet I have found no way to select the file that shows up in the search results. I know there is a /select function within the Shell parameters, but I can't seem to get it to work within the above code. The goal here is getting the file name and file path returned from the search.


      We do not have PDM. IMO the Shell function is faster than the FSO recursive, as I am searching through a large swath of directories. Definitely open to any suggestions. Thanks!

        • Re: VBA Get File Name & Path from Windows Explorer Search Results
          Tom Fosler

          Ok, I found it. Maybe not the best work around, but it works for now.

           

          In Routine:

          RetVal = Shell("explorer.exe ""search-ms://query=" & FileName & Paths & """", vbNormalFocus)

          GetSelectedFilesInWinExplorers

           

          Function retrieves filename & filepath:

          Function GetSelectedFilesInWinExplorers() As Collection

          On Error Resume Next

          Set ExpWin = New SHDocVw.ShellWindows

          For Each CurrWin In ExpWin

              If Not CurrWin.Document Is Nothing Then

                  If Not CurrWin.Document.FocusedItem Is Nothing Then

                      CurrSelFile = CurrWin.Document.FocusedItem.Path

                      If CurrSelFile <> "" Then

                          Result.Add CurrSelFile

                          FileName = Right(CurrSelFile, Len(CurrSelFile) - InStrRev(CurrSelFile, "\"))

                          FilePath = VBA.Left(CurrSelFile, InStrRev(CurrSelFile, "\"))

                          Exit For

                      End If

                      CurrSelFile = ""

                  End If

              End If

          Next CurrWin

          Set GetSelectedFilesInWinExplorers = Result

          End Function

           

          Routine to close Explorer after getting Filename & Filepath:

          Sub TerminateApp()

          Dim strTerminateThis As String

          Dim objWMIcimv2 As Object, objProcess As Object, objList As Object

          Dim intError As Integer

          strTerminateThis = "explorer.exe"

          Set objWMIcimv2 = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\cimv2")

          Set objList = objWMIcimv2.ExecQuery("select * from win32_process where name='" & strTerminateThis & "'")

          For Each objProcess In objList

              intError = objProcess.Terminate 'Terminates a process and all of its threads.

              If intError <> 0 Then Exit For

          Next

          Set objWMIcimv2 = Nothing

          Set objList = Nothing

          Set objProcess = Nothing

          End Sub

           

          I hope this is helpful to others.

          • Re: VBA Get File Name & Path from Windows Explorer Search Results
            JOHN GEORGE

            Tom,

            I am not a VBA expert and not able to comment on this issue.

            I hope this discussion may guide you to solve the problem