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?
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