MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

How to prompt the user for a directory

  Asked By: Rani    Date: Nov 25    Category: MS Office    Views: 1919

What would be the excel VBA code to prompt the user for a directory and
return a string that represents the selected directory with path i.e.



1 Answer Found

Answer #1    Answered By: Devrim Yilmaz     Answered On: Nov 25

Troll through this code. It does more than you want, so you can cut it
down to size.
Option Explicit
Dim tb_One As Object
Dim All_Valid_YN As Boolean
Dim Input_Path As String
Dim Output_File As String

Private Sub Browse_Output_File_Click()
Output_File = Browse_Select("Output_File")
End Sub
Private Sub Button_Input_Path_Click()
Input_Path = Browse_Select("Input_Path")
End Sub
Private Function Browse_Select(This_File As String)
Dim File_Selected

Select Case This_File
Case "Input_Path"
File_Selected = Application.GetOpenFilename("Excel Files
(*.xls),*.xls", , "Select File from Input Directory")
If File_Selected = False Then Exit Function

' Open the file to get its path
Workbooks.Open filename:=File_Selected, UpdateLinks:=False
File_Selected = ActiveWorkbook.Path
Application.DisplayAlerts = False
Application.DisplayAlerts = True

Me.tb_Input_Path = File_Selected
Me.tb_Output_File = File_Selected & "\Multiple Mills " &
Application.text(Now(), "yyyymmdd")

Case "Output_File"
File_Selected = _

Application.GetSaveAsFilename(InitialFilename:=Input_Path & "\", _
FileFilter:="Excel files,*.xls", Title:="Select Output
file name")
End Select

If File_Selected = False Then Exit Function

Browse_Select = File_Selected
Me.Controls("tb_" & This_File) = File_Selected
End Function
Private Sub Button_Cancel_Click()
Me.Tag = vbCancel
End Sub
Private Sub Button_OK_Click()
Range("Input_Path") = Me.tb_Input_Path
Range("Output_File") = Me.tb_Output_File

Me.Tag = vbOK
End Sub
Note that all this code  is part of a form, the design of which you can
figure out from the above code. Credit to Sheila Cooksey, Perth for

Didn't find what you were looking for? Find more on How to prompt the user for a directory Or get search suggestion and latest updates.