ExcelBanter - Get Folder Name

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Get Folder Name (https://www.excelbanter.com/excel-programming/409661-get-folder-name.html)

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson

Thanks Dave.
--
Gary''s Student - gsnu200780

"Dave Peterson" wrote:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson

If your users are pre-xl2002 and you don't want to go the Windows API route,
here is one more option.

_______________________________

Sub FetchAfolderpath()
Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_COMPUTER)
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath)

If objFolder Is Nothing Then
Exit Sub
End If

Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path

MsgBox objPath

End Sub

______________________________

Steve Yandl

"Gary''s Student" wrote in message
...
Thanks Dave.
--
Gary''s Student - gsnu200780

"Dave Peterson" wrote:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help
for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson

Gary's Student,

My copy and paste was a bit too quick.

If you test the routine above, you can drop the duplicate line (the second
time it appears)
Set objShell = CreateObject("Shell.Application")

Also, I pulled this from a vbs file of mine. In a script, it isn't
important to set the objects to nothing at the end of the sub but in VBA you
should include a line at the end of the sub that reads:
Set objShell = Nothing

Steve

"Steve Yandl" wrote in message
. ..
If your users are pre-xl2002 and you don't want to go the Windows API
route, here is one more option.

_______________________________

Sub FetchAfolderpath()
Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_COMPUTER)
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath)

If objFolder Is Nothing Then
Exit Sub
End If

Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path

MsgBox objPath

End Sub

______________________________

Steve Yandl

"Gary''s Student" wrote in
message ...
Thanks Dave.
--
Gary''s Student - gsnu200780

"Dave Peterson" wrote:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help
for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson

Thank you very much Steve!!
--
Gary''s Student - gsnu200780

"Steve Yandl" wrote:

Gary's Student,

My copy and paste was a bit too quick.

If you test the routine above, you can drop the duplicate line (the second
time it appears)
Set objShell = CreateObject("Shell.Application")

Also, I pulled this from a vbs file of mine. In a script, it isn't
important to set the objects to nothing at the end of the sub but in VBA you
should include a line at the end of the sub that reads:
Set objShell = Nothing

Steve

"Steve Yandl" wrote in message
. ..
If your users are pre-xl2002 and you don't want to go the Windows API
route, here is one more option.

_______________________________

Sub FetchAfolderpath()
Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_COMPUTER)
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath)

If objFolder Is Nothing Then
Exit Sub
End If

Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path

MsgBox objPath

End Sub

______________________________

Steve Yandl

"Gary''s Student" wrote in
message ...
Thanks Dave.
--
Gary''s Student - gsnu200780

"Dave Peterson" wrote:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help
for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson

You're welcome.

If you want to use a top level folder other that the MyComputer special
folder, this reference will provide the available constants.
http://www.microsoft.com/technet/scr....mspx?mfr=true

Steve

"Gary''s Student" wrote in message
...
Thank you very much Steve!!
--
Gary''s Student - gsnu200780

"Steve Yandl" wrote:

Gary's Student,

My copy and paste was a bit too quick.

If you test the routine above, you can drop the duplicate line (the
second
time it appears)
Set objShell = CreateObject("Shell.Application")

Also, I pulled this from a vbs file of mine. In a script, it isn't
important to set the objects to nothing at the end of the sub but in VBA
you
should include a line at the end of the sub that reads:
Set objShell = Nothing

Steve

"Steve Yandl" wrote in message
. ..
If your users are pre-xl2002 and you don't want to go the Windows API
route, here is one more option.

_______________________________

Sub FetchAfolderpath()
Const MY_COMPUTER = &H11&
Const WINDOW_HANDLE = 0
Const OPTIONS = 0

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.Namespace(MY_COMPUTER)
Set objFolderItem = objFolder.Self
strPath = objFolderItem.Path

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder _
(WINDOW_HANDLE, "Select a folder:", OPTIONS, strPath)

If objFolder Is Nothing Then
Exit Sub
End If

Set objFolderItem = objFolder.Self
objPath = objFolderItem.Path

MsgBox objPath

End Sub

______________________________

Steve Yandl

"Gary''s Student" wrote in
message ...
Thanks Dave.
--
Gary''s Student - gsnu200780

"Dave Peterson" wrote:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's
help
for:
application.filedialog(msoFileDialogFolderPicker)

Gary''s Student wrote:

I can use the file open dialog to select a file:

Sub WhichOne()
MsgBox (Application.GetOpenFilename)
End Sub

How can I select a folder??
--
Gary''s Student - gsnu200780

--

Dave Peterson