Select a directory in an Excel spreadsheet 4

[migv1]

I have a field in an Excel template where users must type in the current working directory (this almost never remains constant). Unfortunately, the path names are rather long, since we work out of shared network drives.
Is there a control I can place on the worksheet that will let users select the proper directory and return the path string to a cell using the common open or find file dialog?
I tried a quick keyword search, with no luck - sorry if this question has been asked and answered.
Thanks for any and all suggestions.

 

[SkipVought]

Hi,

Yes, you can use Application.GetOpenFileName, mebe something like this, assuming that Column A is the column of interest...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim fn, x
    With Target
        If .Row = 1 Then Exit Sub
        If .Count > 1 Then Exit Sub
        If .Column <> 1 Then Exit Sub
        fn = Application.GetOpenFilename
        If fn <> False Then
            .Value = Left(fn, InStrRev(fn, "\") - 1)
        End If
    End With
End Sub

copy this code into the SHEET OBJECT - right click sheet tab - select View Code


Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

Hi Skip - If the folder is empty with no file to select, then i don't think it can be gotten using that can it?

How about a slight tweak on yours combined with a function:-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim fn, x
    With Target
        If .Row = 1 Then Exit Sub
        If .Count > 1 Then Exit Sub
        If .Column <> 1 Then Exit Sub
        .Value = PickFolder(strStartDir)
    End With
End Sub

Function PickFolder(strStartDir As Variant) As String
    Dim SA As Object, F As Object
    Set SA = CreateObject("Shell.application")
    Set F = SA.BrowseForFolder(0, "Choose a folder", 0, strStartDir)
    If (Not F Is Nothing) Then
        PickFolder = F.items.Item.Path
    End If
    Set F = Nothing
    Set SA = Nothing
End Function

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Ooops! ==>[purple]*[/purple]

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

LOL - I'm sat here before pressing the button thinking "I must have this wrong because it was Skip that posted it". I tried it half a dozen times before pressing the damn button

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Though I've prepared,
And come to play.
You've discovered
My feet of clay.

Mohammed SkipVought Ali

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

LOL

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[migv1]

Thank you Skip & Ken for your valuable input. I ended up using Ken's method (had to dim strStartDir in place of 'x'), but Skip's had the interesting benefit of returning the value as a hyperlink (filed this gem away in my code vault - may be useful in the future).

I love Tek-Tips - I always learn something useful, and everyone is always very helpful. I dabble in Excel occaisionally, but I'm actually much better at Access (maybe I can help with your databases).

 

[KenWright]

but I'm actually much better at Access (maybe I can help with your databases).

You just demonstrated exactly why this place works so well

Regards
Ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------