API for a user to select a folder?

[Chrisso]

Hi All

Is there an API call similar to Application.GetOpenFileName that will
allow a user to select a folder and then return the selected folder as
a String?

Thanks,
Chrisso

 

[Tom Hutchins]

Here are two ways...

Sub AAAA()
Dim oApp As Object, oFolder
Set oApp = CreateObject("Shell.Application")
'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then
'Do whatever
Else
MsgBox "You did not select a folder"
End If
'carry on...
End Sub

Sub BBBB()
'Declare variables for this macro.
Dim Fyle As String, FylePath As String, xx As Integer
'The user needs to select any file in the folder.
Fyle$ = Application.GetOpenFilename("All Files (*.*), *.*", , _
"Select any file in the desired folder")
'If no file was selected (Cancel clicked), stop the macro.
If Fyle$ = "False" Then Exit Sub
'Extract just the file path from Fyle$.
FylePath$ = vbNullString
For xx% = Len(Fyle$) To 1 Step -1
If Mid(Fyle$, xx%, 1) = "\" Then
FylePath$ = Left(Fyle$, xx%)
Exit For
End If
Next xx%
If Len(FylePath$) = 0 Then
MsgBox "Cannot identify file path"
Exit Sub
End If
'carry on...
End Sub

Hope this helps,

Hutch

 

[Richard MG]

Chrisso wrote "Is there an API call similar to Application.GetOpenFileName
that will

allow a user to select a folder and then return the selected folder as
a String?"

What if the user just needs to open a file in the selected folder which will
then run in its usual programme independantly of Excel. All the solutions I
have seen give a way of selecting the file path. How do I get the file to
open in the correct programme, i.e. .xlm file opening in Infopath. I am using
Excel 2003 and Infopath 2007