VBA to open explorer and select, not open, a selected file name

Question

I want a macro to open windows explorer and just select a file but don’t want it to open the file. I’ve got a list of document names in excel split into some variables. I also included some hyperlinks in it, so you can directly open the selected file. But now I want a macro that selects the corresponding file in explorer and just selects it. All files are in a predefined location, but all filenames are different, off course. Example; D:\username\Documents\workplans\document.001.1.xls D:\username\Documents\workplans\document.002.2.xls D:\username\Documents\workplans\document.003.3.xls

I want to select the corresponding file name in excel, and start the macro to select it in explorer. So for example I select cell D3 and start the macro so it opens explorer and go’s to the following address and selects the file; D:\username\Documents\workplans\document.002.2.xls

      A                 B             C             D                 E
 1. var 1             var 2          var 3      doc. Name          Hyperlink
 2. document            1             1     document.001.1.xls  document.001.1
 3. document            2             2     document.002.2.xls  document.002.2
 4. document            3             3     document.003.3.xls  document.003.3

If I use the following code directly to the link it works like how I want it to be, but the file name is variable.

Sub open_explorer()
Shell "C:\Windows\explorer.exe /select,D:\username\Documents\workplans\document.002.2.xls", vbMaximizedFocus
End Sub

I adjusted the code, but it won’t work. I think the problem is in the (& range (activeCell.select)). How do I get this to work?

Sub open_explorer()
Shell "C:\Windows\explorer.exe /select, D:\username\Documents\workplans\ &Range ActiveCell.Select", vbMaximizedFocus
End Sub

Solution 1

Give this a try:

Sub open_explorer()
Shell "C:\Windows\explorer.exe /select, ""D:\username\Documents\workplans\" _
    & ActiveCell.Value & """", vbMaximizedFocus
End Sub

Note that quotes are required around any path that has spaces or other special characters. The doubled quotes in the string above, both before D: and after ActiveCell.Value (concatenated to the end of the string) puts a double quote char before and after the path.

Solution 2

The above only worked for me if I add an extension to the filename such as ".xls".

Sub open_explorer()
Shell "C:\Windows\explorer.exe /select, D:\username\Documents\workplans\" & ActiveCell.Value & ".xls", vbMaximizedFocus
End Sub