Select a file in File Explorer from a list of files in Excel in the same FE window

C

CoolAuto

New Member
Joined
Aug 26, 2015
Messages
32
Couple of macros below are doing what I need, but create an issue - it closes Internet Explorer window under certain conditions, instead of File Explorer.
Catch is that without "Call CloseWindow(sPrev)" code keeps opening more and more File Explorer windows.

On Excel worksheet in A3 there's path to file. File names from that path are listed in A6 and down.

Code relevant to this question in first macro below starts with line "If Target.Column = 1 And Target.Row > 5 Then"
Code closes existing File Explorer window, then opens a new one, then selects the next file listed in A6 and down in the newly opened File Explorer window.

Looking for modification of this code for selection of the next file to take place without opening new or closing existent File Explorer window, i.e. select new file in the same File Explorer window.
("Call CopyFirstOne" just copies first set of characters from a cell and not relevant to this question)

Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static sPrev As String
    Static iRow As Long
    Dim sFolderName As String, sFullPathName As String
    
    If Target.Rows.Count > 1 Then Exit Sub
    If Target.Row < 6 Then Exit Sub
    If iRow > 0 Then
        Rows(iRow).RowHeight = 15
        Rows(iRow).Font.Size = 10
        Rows(iRow).Cells.Interior.ColorIndex = 0
    End If
    iRow = Target.Row
    Target.RowHeight = 30
    Target.EntireRow.Font.Size = 12
    Target.EntireRow.Cells.Interior.ColorIndex = 37


    If Target.Column = 1 And Target.Row > 5 Then
        sFolderName = Range("A3").Text
        sFolderName = IIf(Right(sFolderName, 1) = "\", sFolderName, sFolderName & "\")
        sFullPathName = sFolderName & ActiveCell(1, 1).Value
        
        Call CloseWindow(sPrev)
        If Len(Dir(sFullPathName)) Then
            If GetAttr(sFullPathName) = 32 Then
                Shell "C:\Windows\explorer.exe /select," & sFullPathName, vbNormalFocus 'somewhere past this line need for Excel wb to take the focus back as an option
                sPrev = sFullPathName
                ThisWorkbook.Activate 'for This Excel wb to take the focus back to see if this will fit the workflow
            End If
        End If
    End If
    ActiveWindow.ScrollRow = Selection.Row
    Call CopyFirstOne
    
End Sub

Private Sub CloseWindow(ByVal FullPathName As String)
    Dim sh As Object
    Dim w As Object
On Error Resume Next
    Set sh = CreateObject("shell.application")
    For Each w In sh.Windows
        If Not w.Document Is Nothing Then
            If Not w.Document.FocusedItem Is Nothing Then
                If w.Document.FocusedItem.Path = FullPathName Then
                    w.Quit
                    Exit For
                End If
            End If
        End If
    Next w
End Sub
 
Sort by date Sort by votes
Like this:

Code: 
    'Find our File Explorer window
    
    Do While wb Is Nothing: Set wb = GetExplorer(Sh32, folder): DoEvents: Loop
    
    'Set its position and size
    
    With wb
        .Left = 100
        .Top = 200
        .Width = 1000
        .Height = 600
    End With
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Some sources make it seem more complicated than it really is - that works great!
Now trying to figure out the code lines for file explorer window Preview pane to be on, if not already on and Navigation pane off, if wasn't already off... Pretty sure it's a simple addition to your awesome solution just above...
 
Upvote 0
Are you still looking for code to handle the Preview and Navigation panes in the File Explorer window? I've been developing code which uses UIAutomation and have a solution you can try.
 
Upvote 0
Here is the procedure which uses UIAutomation to close the Navigation Pane and open the Preview Pane in the File Explorer window. To use, call UIAutomation_Set_File_Explorer_View with the window handle of the File Explorer window.

The code requires a reference to UIAutomationClient, which you must set via Tools -> References in the VBA editor.

Put this code in a standard module.

Code: 
'Reference required: UIAutomationClient


Option Explicit

'https://msdn.microsoft.com/en-us/library/office/gg264421.aspx
'64-Bit Visual Basic for Applications Overview

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    'New VBA version 7 compiler, therefore >= Office 2010
    'PtrSafe means function works in 32-bit and 64-bit Office
    'LongPtr type alias resolves to Long (32 bits) in 32-bit Office, or LongLong (64 bits) in 64-bit Office
    Public Declare PtrSafe Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As LongPtr) As LongPtr
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    'Old VBA version 6 or earlier compiler, therefore <= Office 2007
    Public Declare Function SetForegroundWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
    Public Declare Sub Sleep Lib "kernel32" (ByVal milliseconds As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


'This uses UIAutomation FindFirst to search for the Navigation pane and Preview pane elements.

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
Public Sub UIAutomation_Set_File_Explorer_View(FEhwnd As LongPtr)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
Public Sub UIAutomation_Set_File_Explorer_View(FEhwnd As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
   
    Dim UIauto As IUIAutomation
    Dim FEwindow As IUIAutomationElement
    Dim NameCondition As IUIAutomationCondition, TypeCondition As IUIAutomationCondition
    Dim AndCondition As IUIAutomationCondition
    Dim NavigationPane As IUIAutomationElement, PreviewPane As IUIAutomationElement
    Dim ViewMenu As IUIAutomationElement
    Dim Pattern As IUIAutomationLegacyIAccessiblePattern
    Dim NavigationPaneButton As IUIAutomationElement
    Dim NavigationPaneButtonSubMenu As IUIAutomationElement
    Dim PreviewPaneButton As IUIAutomationElement
   
    'Create UIAutomation object
    
    Set UIauto = New CUIAutomation
            
    'Get the File Explorer window element
    
    Set FEwindow = UIauto.ElementFromHandle(ByVal FEhwnd)
    
    'Optional - bring the File Explorer window to the foreground
    
    FEwindow.SetFocus
        
    'See if the Navigation pane exists, and if so close it
    
    'Create criteria to find the Navigation pane in the Explorer window - it is a tree control named "Tree View"
    'Name = Tree View
    'Class = SysTreeView32
    'Ctrl type = 50023
    'Ctrl Name = tree
    
    Set ViewMenu = Nothing
    Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "Tree View")
    Set TypeCondition = UIauto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_TreeControlTypeId)
    Set AndCondition = UIauto.CreateAndCondition(NameCondition, TypeCondition)
    Set NavigationPane = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
    
    If Not NavigationPane Is Nothing Then
    
        'The Navigation pane exists, so close it by performing the following steps:
        '1. Find and click the 'View' menu item, making the next step available
        '2. Find and click the main 'Navigation pane' button, making the next step available
        '3. Find and click the 'Navigation pane' sub-menu item, to close the Navigation pane
        
        'Find the 'View' menu item
        
        Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "View")
        Set AndCondition = UIauto.CreateAndCondition(NameCondition, UIauto.ControlViewCondition)
        Set ViewMenu = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
        
        'Call DoDefaultAction (Switch) on the 'View' menu item to activate the View menu
        
        Set Pattern = ViewMenu.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
        Pattern.DoDefaultAction
        DoEvents
        
        'Find main 'Navigation pane' button in the FE window
        
        Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "Navigation pane")
        Set AndCondition = UIauto.CreateAndCondition(NameCondition, UIauto.ControlViewCondition)
        Set NavigationPaneButton = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
        
        'Call DoDefaultAction (Open) on the main 'Navigation pane' button.  This opens the sub-menu below it.
    
        Set Pattern = NavigationPaneButton.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
        Pattern.DoDefaultAction
        DoEvents
        
        'Find 'Navigation pane' sub-menu item under the main 'Navigation pane' button
        
        Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "Navigation pane")
        Set AndCondition = UIauto.CreateAndCondition(NameCondition, UIauto.ControlViewCondition)
        Set NavigationPaneButtonSubMenu = FEwindow.FindFirst(TreeScope_Subtree, AndCondition)
       
        'Call DoDefaultAction (Execute) on the 'Navigation pane' sub-menu item.  This turns off the Navigation pane
       
        Set Pattern = NavigationPaneButtonSubMenu.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
        Pattern.DoDefaultAction
        DoEvents
        
    End If
    
    'See if the Preview pane exists, and if not open it
    
    'Create criteria to find the Preview pane in the Explorer window - it is an image control named "Thumbnail Module"
    'Name = Thumbnail Module
    'Class = PreviewThumbnailInner
    'Ctrl type = 50006
    'Ctrl Name = image
    
    Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "Thumbnail Module")
    Set TypeCondition = UIauto.CreatePropertyCondition(UIA_ControlTypePropertyId, UIA_ImageControlTypeId)
    Set AndCondition = UIauto.CreateAndCondition(NameCondition, TypeCondition)
    Set PreviewPane = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
    
    If PreviewPane Is Nothing Then
    
        'The Preview pane does not exist, so we want to open it by performing the following steps:
        '1. If not previously activated, find and click the 'View' menu item, making the next step available
        '2. Find and click the 'Preview pane' button, to open the Preview pane
        
        'Find the 'View' menu item
        
        If ViewMenu Is Nothing Then
        
            Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "View")
            Set AndCondition = UIauto.CreateAndCondition(NameCondition, UIauto.ControlViewCondition)
            Set ViewMenu = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
            
            'Call DoDefaultAction (Switch) on the 'View' menu item to activate the View menu
            
            Set Pattern = ViewMenu.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
            Pattern.DoDefaultAction
            DoEvents
        
        End If
       
        'Find 'Preview pane' button in the FE window
        
        Set NameCondition = UIauto.CreatePropertyCondition(UIA_NamePropertyId, "Preview pane")
        Set AndCondition = UIauto.CreateAndCondition(NameCondition, UIauto.ControlViewCondition)
        Set PreviewPaneButton = FEwindow.FindFirst(TreeScope_Descendants, AndCondition)
        
        'Call DoDefaultAction (Press) on the 'Preview pane' button to open the Preview pane
    
        Set Pattern = PreviewPaneButton.GetCurrentPattern(UIA_LegacyIAccessiblePatternId)
        Pattern.DoDefaultAction
        DoEvents
        
    End If
    
End Sub
Here is the OP's Worksheet_SelectionChange procedure modified to call the above UIAutomation_Set_File_Explorer_View procedure.

Put this code in the sheet module.

Code: 
Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Static iRow As Long
    Dim folderPath As String, fullFileName As String
    Dim fileName As String
    Dim thisWindow As Window
    
    Set thisWindow = ActiveWindow
    
    If Target.Rows.Count > 1 Then Exit Sub
    If Target.Row < 6 Then Exit Sub
    
    If iRow > 0 Then
        Rows(iRow).RowHeight = 15
        Rows(iRow).Font.Size = 10
        Rows(iRow).Cells.Interior.ColorIndex = 0
    End If
    
    iRow = Target.Row
    Target.RowHeight = 30
    Target.EntireRow.Font.Size = 12
    Target.EntireRow.Cells.Interior.ColorIndex = 37

    If Target.Column = 1 And Target.Row >= 6 Then
    
        fileName = Target.Text
        folderPath = Range("A3").Text
        If Right(folderPath, 1) = "\" Then folderPath = Left(folderPath, Len(folderPath) - 1)
        fullFileName = folderPath & "\" & fileName
        
        If Len(Dir(fullFileName)) Then
             'vbArchive (32) - File has changed since last backup
            If GetAttr(fullFileName) And vbArchive = vbArchive Then
                SelectFile folderPath, fileName
            End If
        End If
        
    End If
    
    DoEvents
    thisWindow.ScrollRow = Target.Row
    
End Sub


'Open a File Explorer window in the specified folder (if not already open) and select the specified file
'Based on https://stackoverflow.com/questions/25693848/how-to-select-multiple-files-in-windows-explorer-from-selected-cells-in-excel-us

Private Sub SelectFile(folder As String, fileName As String)

    Dim wb As Object 'WebBrowser
    Dim Sh32 As Object 'Shell32.Shell
    
    Set Sh32 = CreateObject("Shell.Application") 'New Shell32.Shell

    Sh32.Open CVar(folder)

    'Find our File Explorer window

    Do While wb Is Nothing: Set wb = GetExplorer(Sh32, folder): DoEvents: Loop
        
    'Set its position and size
    
    With wb
        If .Left <> 900 Then
            .Left = 900
            .Top = 50
            .Width = 1000
            .Height = 600
        End If
    End With
    
    'Set the File Explorer view: turn off Navigation pane and turn on Preview pane
    
    UIAutomation_Set_File_Explorer_View wb.hwnd
    
    'Select file in the window
    '2nd argument: 5& deselects the previously selected file; 1& keeps it selected
    'The 2nd Sleep is necessary, otherwise the Preview pane sometimes displays "This file can't be previewed"
    
    Sleep 500
    wb.Document.SelectItem CVar(folder & "\" & fileName), 5&
    DoEvents
    Sleep 800
    
    'Optional: put Excel window on top
    
    'SetForegroundWindow Application.hwnd
    
End Sub


'Find the File Explorer window open at the specified folder

Private Function GetExplorer(Sh32 As Object, folder As String) As Object 'WebBrowser

    Dim wb As Object 'WebBrowser
   
    For Each wb In Sh32.Windows
        If UCase(wb.FullName) = "C:\WINDOWS\EXPLORER.EXE" Then
            If LCase(wb.Document.folder.Self.Path) = LCase(folder) Then
                Set GetExplorer = wb
            End If
        End If
    Next
    
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
VBA Code to double click on Folder in Col A and folder to opened and user to be able to select file
Replies
3
Views
817
mumps
mumps
A
  • Question Question
Open custom binary file (*.quote) created with Excel VBA from Outlook attachment
Replies
0
Views
505
ajloewen
A
C
  • Solved
VBA to merge sheet from multiple excel files into one master file
Replies
9
Views
10K
NdNoviceHlp
NdNoviceHlp
R
  • Question Question
How to combine the functions of having the ability to select multiple items from a drop down list and hide columns based on values of said list?
Replies
0
Views
396
rgree05
R
K
  • Question Question
VBA listing ".xlsx" files only from a selected folder
Replies
3
Views
2K
HaHoBe
HaHoBe

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

Share this page

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
Back
Top