Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Browse to a folder and paste full path into a cell?

  1. #1
    New Member
    Join Date
    Jun 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Browse to a folder and paste full path into a cell?

    Hi all, the title pretty much says it all, I have a form where I am trying to create a button that will pop up a window where the user will browse to the desired folder and once that folder is selected, the path will be copied as a text string into the cell adjacent. My command Button cell is in B3 and the path should be shown in C3.

    Can anyone help?

    Thanks

  2. #2
    New Member
    Join Date
    Jun 2010
    Location
    London, United Kingdom
    Posts
    35
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    First, follow the steps here http://www.vbaexpress.com/kb/getarticle.php?kb_id=284 and create the BrowseForFolder function.

    Then, create the following procedure and assign it to your command button


    Public Sub CopyFolderAddress()

    Dim strChosenFolder As String
    strChosenFolder = BrowseForFolder("Choose a folder")
    'handle the click of cancel button
    If (InStr(1, LCase(strChosenFolder), LCase("false"), vbTextCompare) > 0) Then
    Exit Sub
    End If
    ActiveSheet.Range("C3") = strChosenFolder

    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Just use the FileDialog:

    Code:
    Sub CommandButton1_Click()
    With Application.FileDialog(msoFileDialogFolderPicker)
      .IntialFileName = Activeworkbook.Path
      .Title = "Please choose a folder"
      .AllowMultiSelect = False
      If .Show = -1 Then Range("C3").Value = .SelectedItems(1)
    End With
    End Sub
    Richard Schollar

    Using xl2013

  4. #4
    New Member
    Join Date
    Jun 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Thanks for the quick replies, works like a charm.

  5. #5
    New Member
    Join Date
    Jun 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Quote Originally Posted by srikanth.nm View Post
    First, follow the steps here http://www.vbaexpress.com/kb/getarticle.php?kb_id=284 and create the BrowseForFolder function.

    Then, create the following procedure and assign it to your command button


    Public Sub CopyFolderAddress()

    Dim strChosenFolder As String
    strChosenFolder = BrowseForFolder("Choose a folder")
    'handle the click of cancel button
    If (InStr(1, LCase(strChosenFolder), LCase("false"), vbTextCompare) > 0) Then
    Exit Sub
    End If
    ActiveSheet.Range("C3") = strChosenFolder

    End Sub
    Just coming back to this...

    I have a scenario that when a user selects a folder and presses ok, if they have selected the wrong folder and want to change it by pressing the browse button again it starts back at the desktop root etc... and I was wondering if it was possible to change the code to start browsing at the location where the text is pasted in cell C3. The desired end result is that when the button is pressed the folder tree automatically opens at the last selected folder.

    Is this possible?

    Thanks again

  6. #6
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    You could do that with the one I posted:

    Code:
    Sub CommandButton1_Click()
    With Application.FileDialog(msoFileDialogFolderPicker)
      .IntialFileName = Iif(Range("C3").Value = "",Activeworkbook.Path,Range("C3").Value)
      .Title = "Please choose a folder"
      .AllowMultiSelect = False
      If .Show = -1 Then Range("C3").Value = .SelectedItems(1)
    End With
    End Sub
    Richard Schollar

    Using xl2013

  7. #7
    New Member
    Join Date
    Jun 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Thanks for the quick reply Richard.

    I replaced the previous button code with yours but I'm getting an error:

    Compile Error
    Method or Data member not found

    .IntialFileName = is also highlighted in the debugger

    Any idea's what's gone wrong?

  8. #8
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Are you using 2007? I've found some issues using .IntialFileName, but that was if the .fileFilter was set up incorrectly. I'm wondering if this is connected.
    If the above suggestion contains vb code and you're not sure about using macros,check the following links (which in no way should be interpreted as being specific to your individual query):

    http://www.mrexcel.com/articles/past...o-into-vbe.php

    You can find help with array formulas here:

    http://www.cpearson.com/excel/arrayformulas.aspx

    If you really want to learn Excel, don't always accept the first solution.

  9. #9
    New Member
    Join Date
    Jun 2010
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Nope 2003

  10. #10
    MrExcel MVP
    Moderator
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Browse to a folder and paste full path into a cell?

    Sorry, that was a typo - it should have been:

    .InitialFileName
    Richard Schollar

    Using xl2013

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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