Can I open a folder with VB code, without using a hyperlink? Regards, Brett

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

Regards,
Nigel




"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

Hi Nigel, thanks for that. Not too sure if it will do want I want, but I'll
fool around with it. Regards, Brett

"Nigel" wrote:

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

Regards,
Nigel




"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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
---------------
Jacob Skaria

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.


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Brettjg" wrote in message
...
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.


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

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

"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Brettjg" wrote in message
...
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.


"Jacob Skaria" wrote:

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
---------------
Jacob Skaria

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)




On Sun, 15 Mar 2009 00:20:00 -0700, Brettjg
wrote:

Can I open a folder with VB code, without using a hyperlink? Regards, Brett

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.





"Chip Pearson" wrote:

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)




On Sun, 15 Mar 2009 00:20:00 -0700, Brettjg
wrote:

Can I open a folder with VB code, without using a hyperlink? Regards, Brett

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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"AAM" wrote in message
...
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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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.


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"AAM" wrote in message
...
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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"AAM" wrote in message
...
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.


"Jon Peltier" wrote in message
...
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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"AAM" wrote in message
...
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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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.


"Jon Peltier" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"AAM" wrote in message
...
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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett

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.


"AAM" wrote:

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

"Brettjg" wrote in message
...
Can I open a folder with VB code, without using a hyperlink? Regards,
Brett