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

[jase71ds]

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.

 

[VoG]

See if this helps http://www.mrexcel.com/forum/excel-questions/294728-browse-folder-visual-basic-applications.html

 

[ParamRay]

.
.

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

 

[jase71ds]

Wow. Thanks so much. This works fantastically!!