Browse folders and input folder path in text box

[mikey2322]

Hi,

I have a text box in a form in which the person using the workbook inputs the destination folder of the files the worbook will produce when another macro is run. .

I have it so that you can type in the desired folder path (i.e. C:\Test\Test\...etc.) and it works but I also wanted to put a browse button beside the text box that would allow the user to browse and select the folder (i.e. ....Test\) and then hit ok and the folder path will show in the text box before the user hits the final button that will produce the files to be saved to that folder. Any help would be greatly appreciated!

Cheers,

Mike

 

[dave3009]

Hi Mike

Lets say you add a standard Command Button the code might look something like

Private Sub CommandButton1_Click()
Dim a As String
a = Application.GetOpenFilename
Sheet1.Range("A1").Value = a
End Sub

Just change the Sheet1.Range("A1").Value to the syntax for your textbox

HTH


Dave

 

[mikey2322]

Hi David,

Thanks for that bit of code, it does what I want except it makes me choose a file as the end of the path where I just want to choose the folder is there a way of changing it to FileOpenFolder etc.??

Cheers,

Mike

 

[dave3009]

Hi Mike

Not something I know myself but a quick search turned this up

Hope it helps

Dave

 

[Sal Paradise]

From here:

Public Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

'API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Here let us use the above shell functions to open the browse directory dialog

Sub Show_BrowseDirectory_Dialog()

' BrowseForFolder
' SHBrowseForFolder API Function Example

Dim dirInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

' Set Default Root folder = Desktop
dirInfo.pidlRoot = 0&

dirInfo.lpszTitle = "Browse directory!"

' Type of directory
dirInfo.ulFlags = &H1

' Show the Browse Dialog
x = SHBrowseForFolder(dirInfo)

' Parse the result
path = Space$(512)
r = SHGetPathFromIDList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
MsgBox "You have selected :=" & Left(path, pos - 1)
Else
MsgBox "Browse a Directory..."
Show_BrowseDirectory_Dialog
End If

End Sub

 

[Krishnakumar]

Works on XL2002 and later versions

Dim FolderName As String
With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    FolderName = .SelectedItems(1)
End With