Announcement

Collapse
No announcement yet.

Choose Destination Folder With Vba

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Choose Destination Folder With Vba



    Hi,
    I would like a user of my program to choose the destination folder where a .txt file will be saved.
    I have code below which allows the chooser to browse through folders and choose a file:
    Code:
      
        Dim f1 As Variant, s1 As Variant
        s1 = "#1: Choose the .txt file which contains montly data for the stock price."
        
        f1 = Application _
        .GetOpenFilename("TextFiles(*.txt),*.txt", , s1)
        txt2.Text = f1
    I want the user to browse folders and subfolders like they can with the above code. However the user will select a file path not a particular file. In the case of the code above, the text of Text Box txt2 would be set to the file path (i.e. "C:\Documents and Settings\Administrator\Desktop")
    Thanks!

  • #2
    Re: Choose Destination Folder With Vba

    You can use the filedialog. msoFileDialogFolderPicker

    From the helpfiles:

    FileDialog Property
    See AlsoApplies ToExampleSpecificsReturns a FileDialog object representing an instance of the file dialog.

    expression.FileDialog(fileDialogType)
    expression Required. An expression that returns one of the objects in the Applies To list.

    fileDialogType Required MsoFileDialogType. The type of file dialog.

    MsoFileDialogType can be one of these MsoFileDialogType constants.
    msoFileDialogFilePicker Allows user to select a file.
    msoFileDialogFolderPicker Allows user to select a folder.
    msoFileDialogOpen Allows user to open a file.
    msoFileDialogSaveAs Allows user to save a file.

    So:

    Code:
    Sub hth()
    
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
    
            If .SelectedItems.Count > 0 Then
                txt2.Text = .SelectedItems(1)
            End If
    
        End With
    
    End Sub
    Reafidy

    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

    Comment


    • #3
      Re: Choose Destination Folder With Vba

      Thank you Reafidy!

      Comment


      • #4


        Re: Choose Destination Folder With Vba

        The only thing to be wary of is you need to be running xl2002 or higher to make use of FileDialogs. In case you are interested, here is a function I wrote a while ago which uses the FileDialog o pick a folder and allows an optional argument to be passed in denoting the intial folder from which to serach:

        Code:
        Function GetFolder(Optional startFolder As Variant = -1) As Variant
            Dim fldr As FileDialog
            Dim vItem As Variant
            Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
            With fldr
                .Title = "Select a Folder"
                .AllowMultiSelect = False
                If startFolder = -1 Then
                    .InitialFileName = Application.DefaultFilePath
                Else
                    If Right(startFolder, 1) <> "\" Then
                        .InitialFileName = startFolder & "\"
                    Else
                        .InitialFileName = startFolder
                    End If
                End If
                If .Show <> -1 Then GoTo NextCode
                vItem = .SelectedItems(1)
            End With
        NextCode:
            GetFolder = vItem
            Set fldr = Nothing
        End Function
        Richard

        Comment

        Working...
        X