Get File Path ends with folder

Question

I know how to let the user click on a button to navigate to a specific file to open   Code   Private Sub CommandButton2 Click       Dim vaFiles As Variant      vaFiles   Application GetOpenFilename        ActiveSheet Range  B9     vaFiles End Sub   I want a second button that will let the user navigate to a folder to save the  pdf file that my program creates   The problem  The GetOpenFilename requires the user to click on a file   If there s no file in the folder then there s nothing the user can do

User · Accepted Answer

Use the Application FileDialog object  Sub SelectFolder       Dim diaFolder As FileDialog     Dim selected As Boolean        Open the file dialog     Set diaFolder   Application FileDialog msoFileDialogFolderPicker      diaFolder AllowMultiSelect   False     selected   diaFolder Show      If selected Then         MsgBox diaFolder SelectedItems 1      End If      Set diaFolder   Nothing End Sub

User · Answer

Have added ErrorHandler to this in case the user hits the cancel button instead of selecting a folder  So instead of getting a horrible error message you get a message that a folder must be selected and then the routine ends  Below code also stores the folder path in a range name  Which is just linked to cell A1 on a sheet    Sub SelectFolder    Dim diaFolder As FileDialog   Open the file dialog On Error GoTo ErrorHandler Set diaFolder   Application FileDialog msoFileDialogFolderPicker  diaFolder AllowMultiSelect   False diaFolder Title    Select a folder then hit OK  diaFolder Show Range  IC Files Path   Value   diaFolder SelectedItems 1  Set diaFolder   Nothing Exit Sub  ErrorHandler  Msg    No folder selected  you must select a folder for program to run  Style   vbError Title    Need to Select Folder  Response   MsgBox Msg  Style  Title   End Sub

User · Answer

If you want to browse to a folder by default  For example  D  Default Folder  just initialise the  InitialFileName  attribute  Dim diaFolder As FileDialog    Open the file dialog Set diaFolder   Application FileDialog msoFileDialogFolderPicker  diaFolder AllowMultiSelect   False diaFolder InitialFileName    D  Default Folder  diaFolder Show

User · Answer

Use Application GetSaveAsFilename   in the same way that you used  Application GetOpenFilename

User · Answer

In the VBA Editor s Tools menu  click References    scroll down to  Microsoft Shell Controls And Automation  and choose it   Sub FolderSelection       Dim MyPath As String     MyPath   SelectFolder  Select Folder           If Len MyPath  Then         MsgBox MyPath     Else         MsgBox  Cancel was pressed      End If End Sub   Both arguements are optional  The first is the dialog caption and  the second is is to specify the top-most visible folder in the  hierarchy  The default is  My Computer    Function SelectFolder Optional Title As String  Optional TopFolder                            As String  As String     Dim objShell As New Shell32 Shell     Dim objFolder As Shell32 Folder   If you use 16384 instead of 1 on the next line   files are also displayed     Set objFolder   objShell BrowseForFolder                                0  Title  1  TopFolder      If Not objFolder Is Nothing Then         SelectFolder   objFolder Items Item Path     End If End Function   Source Link

User · Answer

This might help you out   Sub SelectFolder       Dim diaFolder As FileDialog     Dim Fname As String      Set diaFolder   Application FileDialog msoFileDialogFolderPicker      diaFolder AllowMultiSelect   False     diaFolder Show      Fname   diaFolder SelectedItems 1       ActiveSheet Range  B9     Fname  End Sub

[excel] Get File Path (ends with folder)

Examples related to excel

Examples related to vba

Examples related to file-io