Open folder using variable Path

Question

I am trying to open the folder where the macro workbook is located.

In this example, it is in folder "H:\Projects\IWA_Populate". However, the workbook could be in any folder location.

If I specify a static path as below it opens to the target folder IWA_Populate:

Shell "explorer.exe H:\Projects\IWA_Populate", vbNormalFocus

However if I replace the path with a variable as such below it only opens My Documents folder.

Shell "explorer.exe file_path", vbNormalFocus

Here is my code to get file_path initialized:

Public file_path As String
Public xl As Excel.Application
Set xl = Application: xl.DisplayAlerts = False
ActiveWorkbook.Save
file_path = xl.ActiveWorkbook.Path
'Shell "explorer.exe H:\Projects\IWA_Populate", vbNormalFocus
Shell "explorer.exe file_path", vbNormalFocus
MsgBox file_path

The Message box is to test that file_path is set to the correct full path.

BigBen · Accepted Answer · 2018-11-07 20:03:36Z

7

Move file_path outside the quotation marks and use the ampersand &. While it's inside the quotation marks, it is no longer the variable file_path, but the String literal "file_path".

Change:

Shell "explorer.exe file_path", vbNormalFocus

to

Shell "explorer.exe " & file_path, vbNormalFocus

EDIT: As @TimWilliams pointed out, you can adjust this to

Shell "explorer.exe """ & file_path & """", vbNormalFocus

to allow for spaces in the file name.

edited Nov 7, 2018 at 20:03

answered Nov 7, 2018 at 19:09

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rico Over a year ago

Hi Ben, Quick question would Shell "explorer.exe " & file_path, vbNormalFocus be different if my path contained a space? Like: H:\Projects\IWA Populate

Tim Williams Over a year ago

Shell "explorer.exe """ & file_path & """", vbNormalFocus will allow for spaces in the folder path

Collectives™ on Stack Overflow

Open folder using variable Path

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related