CODEWITHSUNDEEP
excelvbapathdirectory
Rico
Rico

Reputation: 13

Open folder using variable Path

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.

Upvotes: 1

Views: 6455

Answers (1)

BigBen
BigBen

Reputation: 49998

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.

Upvotes: 7

Related Questions