Open Explorer and highlight file

Formula11

Active Member
Joined
Mar 1, 2005
Messages
468
Office Version
  1. 365
Platform
  1. Windows
I have a large number of documents saved in a folder.
In Excel, I have written the name of one of the files in the active cell, including the extension.
The code below opens Windows Explorer when the macro is run.

I have the following issues:
(A) Ideally Windows Explorer is opened in a maximized form (via "vbMaximizedFocus"). However there are two problems with this. (1) The first time the macro is run, it will not highlight the file, it has to be run again. (2) If there is a large number of files in the folder, which would require scrolling to view all, the file won't be highlighted, or it may be highlighted but it won't be in view and you have to scroll to find it.
(B) Using a restored focus (via "vbNormalFocus") seems to solve the above problems. However the default state of Explorer may be maximized when first run. Which leads to the same issue as above.

Test the macro by creating a text file in a folder called "Test", type in "Test.txt" in the active cell in Excel. Run the macro. Change "FolderPath" in macro if not in My Documents.
Note that the behaviour seems to be inconsistent. For example, if files are saved in My Documents, it may not show issues above. Try a folder with a deeper level.

Is there a fix to either (A) or (B) above?


Code:
Sub Open_and_Highlight()
    Dim FolderPath As String
    FolderPath = Environ$("USERPROFILE") & "\Documents" & "\" & ActiveCell.Formula
    'Shell "C:\Windows\explorer.exe /select," & FolderPath, vbMaximizedFocus
    Shell "C:\Windows\explorer.exe /select," & FolderPath, vbNormalFocus
End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

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