Opening a folder with VB

  • Thread starter Thread starter Brettjg
  • Start date Start date
I use the following function to get a user selected folder. You need to set
a reference to 'Microsoft Scripting Runtime' in your project.

Public Function getFolder()
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
If .SelectedItems.Count = 0 Then
' Cancel button has been selected at this point
getFolder = ""
Else
getfolder = .SelectedItems(1)
End If
End With
End Function
 
Hi Dear

In addition to the FSO (FileSystemObject) mentioned by Nigel you may use

DIR function available in VBA. Refer below link
(http://www.techonthenet.com/excel/formulas/dir.php)

OR

If you mean to open the folder in explorer(since you mentioned about
hyperlink);
you can use the below code:

Shell "explorer.exe c:\temp"
(change the folder)


If this post helps click Yes
 
Hi Nigel, thanks for that. Not too sure if it will do want I want, but I'll
fool around with it. Regards, Brett
 
What do you mean "open" a folder? Open the folder window to display
files and folders within that folder?

Try

Dim FolderName As String
FolderName = "C:\Test"
Shell "explorer " & FolderName

or you can use

ThisWorkbook.FollowHyperlink "file://C:\Test"

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Here's a macro to open the folder of the current worksheet, highlighting the
current file (handy if assigned to a toolbar button to open the folder of a
file picked from the Recent Files menu):

Sub OpenCurrentFolder()
On Error Resume Next
Dim CurFold As String
Dim CurFile As String

CurFold = Application.ActiveWorkbook.Path
CurFile = Application.ActiveWorkbook.Name

Call Shell("c:\windows\explorer.exe /e, " & CurFold & ",/select," _
& CurFold & "\" & CurFile, vbNormalFocus)

End Sub
 
CurFile may not be located in CurFold.

Call Shell("c:\windows\explorer.exe /e, " & ActiveWorkbook.path _
& ",/select," & ActiveWorkbook.FullName, vbNormalFocus)

or skip the "Call"

Shell "c:\windows\explorer.exe /e, " & ActiveWorkbook.path _
& ",/select," & ActiveWorkbook.FullName, vbNormalFocus

- Jon
 
Other than Book1,2,etc (which does give an error message) is there any other
way the file 'ActiveWorkbook.Name' could not be in the folder
'ActiveWorkbook.Path'?

At one point I did eliminate the "Call", but I don't remember why I put it
back.
 
If the workbook has not been saved, it has a blank path, so the shell
command has no folder to open. If the workbook has been saved, then by
definition

ActiveWorkbook.Path & "\" & ActiveWorkbook.Name = ActiveWorkbook.FullName

I have to apologize, because I read the post too quickly and thought that
you were using CurDir, or Current Directory, which is the folder that Excel
will display in the File Open or Save File As dialogs.

- Jon
 
To all who replied: thanks for your reply, but I'm not getting any closer to
a solution. It seems that I have a bit of a nasty problem here.

This formula works (and has always worked):
=HYPERLINK("C:\2. SETTLED CLIENTS","SETTLED Folder")

This formula used to work, but no longer does:
=HYPERLINK("C:\2. SETTLED CLIENTS\Coad, K","Coad, K")

It gives me a msg of "The path "K" does not exist or is not a directory.
This is patently not the case because I can open it from the 2. settled
clients folder.

So I tried Chip's code, but that gives the same error, but it works on the
main folder (although it doesn't bring the window to the front). This is not
a problem with VB code or Excel formulas, and nothing has changed on those
ares, whatsoever.

It doesn't matter which way I try to open the folder (macro or =hyperlink)
it gives the same error, where it's truncating the path to the last letter. I
know that I don't need a "\" after the last foldername character (because
then it just says that "K\" doesn't exist).

What could possibly cause that? In the folders options I tried to create a
new action called Explore and may have inadvertently left the " on either
side of "C:\Windows\explorer.exe" (the danger of cut and paste). I am not
allowed to edit this action and nor can I delete now. If this was the cause
then why does it not happen on other folders?

As you can probably tell, I'm at my wit's end because I can't function
properly with my client's folders.

Thankyou all for your help thus far.
 
To all who replied: thanks for your reply, but I'm not getting any closer to
a solution. It seems that I have a bit of a nasty problem here.

This formula works (and has always worked):
=HYPERLINK("C:\2. SETTLED CLIENTS","SETTLED Folder")

This formula used to work, but no longer does:
=HYPERLINK("C:\2. SETTLED CLIENTS\Coad, K","Coad, K")

It gives me a msg of "The path "K" does not exist or is not a directory.
This is patently not the case because I can open it from the 2. settled
clients folder.

So I tried Chip's code, but that gives the same error, but it works on the
main folder (although it doesn't bring the window to the front). This is not
a problem with VB code or Excel formulas, and nothing has changed on those
ares, whatsoever.

It doesn't matter which way I try to open the folder (macro or =hyperlink)
it gives the same error, where it's truncating the path to the last letter. I
know that I don't need a "\" after the last foldername character (because
then it just says that "K\" doesn't exist).

What could possibly cause that? In the folders options I tried to create a
new action called Explore and may have inadvertently left the " on either
side of "C:\Windows\explorer.exe" (the danger of cut and paste). I am not
allowed to edit this action and nor can I delete now. If this was the cause
then why does it not happen on other folders?

As you can probably tell, I'm at my wit's end because I can't function
properly with my client's folders.

Thankyou all for your help thus far.
 
To all who replied: thanks for your reply, but I'm not getting any closer to
a solution. It seems that I have a bit of a nasty problem here.

This formula works (and has always worked):
=HYPERLINK("C:\2. SETTLED CLIENTS","SETTLED Folder")

This formula used to work, but no longer does:
=HYPERLINK("C:\2. SETTLED CLIENTS\Coad, K","Coad, K")

It gives me a msg of "The path "K" does not exist or is not a directory.
This is patently not the case because I can open it from the 2. settled
clients folder.

So I tried Chip's code, but that gives the same error, but it works on the
main folder (although it doesn't bring the window to the front). This is not
a problem with VB code or Excel formulas, and nothing has changed on those
ares, whatsoever.

It doesn't matter which way I try to open the folder (macro or =hyperlink)
it gives the same error, where it's truncating the path to the last letter. I
know that I don't need a "\" after the last foldername character (because
then it just says that "K\" doesn't exist).

What could possibly cause that? In the folders options I tried to create a
new action called Explore and may have inadvertently left the " on either
side of "C:\Windows\explorer.exe" (the danger of cut and paste). I am not
allowed to edit this action and nor can I delete now. If this was the cause
then why does it not happen on other folders?

As you can probably tell, I'm at my wit's end because I can't function
properly with my client's folders.

Thankyou all for your help thus far.
 
To all who replied: thanks for your reply, but I'm not getting any closer to
a solution. It seems that I have a bit of a nasty problem here.

This formula works (and has always worked):
=HYPERLINK("C:\2. SETTLED CLIENTS","SETTLED Folder")

This formula used to work, but no longer does:
=HYPERLINK("C:\2. SETTLED CLIENTS\Coad, K","Coad, K")

It gives me a msg of "The path "K" does not exist or is not a directory.
This is patently not the case because I can open it from the 2. settled
clients folder.

So I tried Chip's code, but that gives the same error, but it works on the
main folder (although it doesn't bring the window to the front). This is not
a problem with VB code or Excel formulas, and nothing has changed on those
ares, whatsoever.

It doesn't matter which way I try to open the folder (macro or =hyperlink)
it gives the same error, where it's truncating the path to the last letter. I
know that I don't need a "\" after the last foldername character (because
then it just says that "K\" doesn't exist).

What could possibly cause that? In the folders options I tried to create a
new action called Explore and may have inadvertently left the " on either
side of "C:\Windows\explorer.exe" (the danger of cut and paste). I am not
allowed to edit this action and nor can I delete now. If this was the cause
then why does it not happen on other folders?

As you can probably tell, I'm at my wit's end because I can't function
properly with my client's folders.

Thankyou all for your help thus far.
 
The obvious solution is to remove the punctuation from your directory names.
I'm not sure commas are prohibited in path names, but I'm sure they violate
"best practices".

- Jon
 
Hi Jon, and suddenly it works again (after 4 years of working perfectly with
commas in the directory names). Thanks very much for that. It was a bit TOO
obvious for me to spot because of the long history of it working.

So now the last remaining problem to solve is why (again, after 4 years of
working properly) the subfolders are now opening up in separate windows, when
I have "Open in the same window" checked in Folder Options. BTW, this is
where the whole fiasco began - trying to solve this problem. Do you have any
clues on this part please? Regards, Brett
 
Back
Top