Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Choose Destination Folder With Vba

  1. #1
    Join Date
    25th September 2006
    Posts
    5

    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:
    VB:
     
    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,897

    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:

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

  3. #3
    Join Date
    25th September 2006
    Posts
    5

    Re: Choose Destination Folder With Vba

    Thank you Reafidy!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: January 26th, 2009, 16:10
  2. Allow User To Choose Folder/File To Open
    By jamiwr in forum EXCEL HELP
    Replies: 6
    Last Post: April 18th, 2008, 09:23
  3. Replies: 2
    Last Post: March 26th, 2008, 17:27
  4. Choose Drive & Folder
    By kim11vagabond in forum EXCEL HELP
    Replies: 5
    Last Post: February 26th, 2008, 22:59
  5. Open Folder: Choose Workbooks to Open
    By chadly in forum EXCEL HELP
    Replies: 1
    Last Post: August 11th, 2006, 14:58

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno