Use Windows Explorer to find a folder path and then paste into cell A1

jase71ds

Board Regular
Joined
Oct 23, 2006
Messages
137
Office Version
  1. 365
Platform
  1. Windows
Hi,

[Office 2010 / Win 7 Pro user.]

My users have to paste in a file path that changes and is usually quite long. (Later the macro saves modified spreadsheets to this folder path).

Currently I have them go to a worksheet named Control_Panel and they have to get out of Excel, locate the file path, copy the path, then go back to Excel and paste that path. It looks something like this: C:\path_yada\path_again\morePath\AndStillMorePath\WayTooLongPath\

Through no fault of mine, the path changes radically from month to month.
Is there a way to code my macro so that the user can click a button that opens a window (like they were going to open a file) and they could visually navigate and select the folder. Whatever folder they selected, then that file path would be pasted into cell A1?

This obviously isn't super critical, but it sure would add convenience to my users, especially the ones that are not particularly saavy.

Thanks.
Jase.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
.
.

Code:
Sub GetFolder()

    Dim fdg As FileDialog
    Dim ifn As String
    
    With Application
        Set fdg = .FileDialog(msoFileDialogFolderPicker)
        ifn = .DefaultFilePath & .PathSeparator
    End With

    With fdg
        .InitialFileName = ifn
        .Title = "Select a location"
        .Show
    End With
        
    If fdg.SelectedItems.Count = 0 Then Exit Sub
    
    Range("A1").Value = fdg.SelectedItems(1)
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top