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

[I3looM]

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

 

[srikanth.nm]

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

 

[Richard Schollar]

Just use the FileDialog:

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

 

[I3looM]

Thanks for the quick replies, works like a charm.

 

[I3looM]

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

 

[Richard Schollar]

You could do that with the one I posted:

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

 

[I3looM]

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?

 

[Weaver]

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.

 

[I3looM]

Nope 2003

 

[Richard Schollar]

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

.InitialFileName