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

I3looM

New Member
Joined
Jun 22, 2010
Messages
12
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

srikanth.nm

New Member
Joined
Jun 14, 2010
Messages
35
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

I3looM

New Member
Joined
Jun 22, 2010
Messages
12

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

I3looM

New Member
Joined
Jun 22, 2010
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,609
Messages
5,765,408
Members
425,284
Latest member
fishymuffin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top