Results 1 to 9 of 9
  1. #1
    JCunningham
    Guest

    Macro Code for Directory Selection (97 SR2)

    Please forgive the ignorance. But HELP! If I could do this in Word, I would be done. But the powers to be said Excel.

    I need to open a group of text files and parse portions of the data into a excel. Most of this is done. But I need to be able to allow the user to select (browse to) the directory where the text files are located. In Word I can open the FileOpen dialog box and trap the path name. Without acutally opening a file. How do I do this in excel?

    FYI, the text files are not opened with the File Open dialog box. Only the path name is trapped. I open the text files sequentially using the Open # For Input command.

    TIA
    Jay Cunningham

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro Code for Directory Selection (97 SR2)

    You want to use the BrowseForFolder API call. <A target="_blank" HREF=http://www.mvps.org/btmtz/browsdlg/>Here</A> is how to put it all together. It's ugly the first time, but then you just copy it whenever you need it. The good news is that everything else is easier in Excel than Word. <img src=/S/aflame.gif border=0 alt=aflame width=15 height=15> HTH. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code for Directory Selection (97 SR2)

    use something like:
    FileToOpen = Application.GetOpenFileName

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code for Directory Selection (97 SR2)

    You can use the GetOpenFIlename function and throw away the filename like this:

    <pre>Public Function GetFolder() As Variant
    Dim vFolder As Variant
    vFolder = Application.GetOpenFilename(Title:="Select the Folder")
    If vFolder = False Then
    GetFolder = False
    Exit Function
    End If
    Do While (Len(vFolder) > 0 And Right(vFolder, 1) <> "")
    vFolder = Left(vFolder, Len(vFolder) - 1)
    Loop
    GetFolder = vFolder
    End Function
    </pre>

    Legare Coleman

  5. #5
    JCunningham
    Guest

    Re: Macro Code for Directory Selection (97 SR2)

    Perfect. Thanks
    Jay

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code for Directory Selection (97 SR2)

    MyFileToOpen = Application.GetOpenFileName
    you will find that MyFileToOpen will contain the full path of the file to open, or the word False if user clicked cancel. In either case, you'll have the directory in there.
    You can limit users to text files by
    MyFileToOpen = Application.GetOpenFilename("Text Files *.txt,*.txt")

  7. #7
    ralph_davies
    Guest

    Re: Macro Code for Directory Selection (97 SR2)

    This works fine as long as there is a file in the folder.
    When there is no file the open button is deactivated so selection is not possible.
    I am writing a macro to allow the selection of folders where files will be stored and to specify where information files are.
    The folder the user selects may initialy be empty.
    How do I get just the folder Info when there is no file ?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro Code for Directory Selection (97 SR2)

    GetSaveAsFilename might work better for you. See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=79086&page=&v iew=&sb=&o=&vc=1#Post79086>This Thread</A> for some ideas.
    Legare Coleman

  9. #9
    ralph_davies
    Guest

    Re: Macro Code for Directory Selection (97 SR2)

    Work Well Thanks

    Have Fun

    Ralph Davies

Posting Permissions

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