Browse for Folder/Directory

[kamran.nyc]

Hi Guys,

I have a dilemma hope some one can solve it. I am basically a PC user
and currently working on an excel model. Part of the model is a browse
button which is assigned a macro which gets triggered when the user
clicks and it prompts the use to select a particular folder/directory.
It works fine on a PC, however since I am not so familiar to a Mac, I
am finding it difficult to get it done.

I posted my question on couple of forums but till now couldn't get any
reply. Any suggestions are appreciated.

Thanks

 

[JE McGimpsey]

Hi Guys,

I have a dilemma hope some one can solve it. I am basically a PC user
and currently working on an excel model. Part of the model is a browse
button which is assigned a macro which gets triggered when the user
clicks and it prompts the use to select a particular folder/directory.
It works fine on a PC, however since I am not so familiar to a Mac, I
am finding it difficult to get it done.

What mechanism do you use that "prompts the use to select a particular
folder/directory"?

Do you use an input box? or a user form? Something else?

 

[CyberTaz]

Forgive me if I'm underestimating your need, but based on the scant info you
provided I can't help thinking you're possibly trying to reinvent the wheel.
I have no problem with code-driven buttons etc. where *necessary*, but...

Why not just add the folders to the Finder window sidebar?

 

[akamook62]

Hi Guys,

I have a dilemma hope some one can solve it. I am basically a PC user
and currently working on an excel model. Part of the model is a browse
button which is assigned a macro which gets triggered when the user
clicks and it prompts the use to select a particular folder/directory.
It works fine on a PC, however since I am not so familiar to a Mac, I
am finding it difficult to get it done.

I posted my question on couple of forums but till now couldn't get any
reply. Any suggestions are appreciated.

Thanks

Here is what I found to work. I'm running Excel X Service Release 1.
I have not used with anything other than Excel X. Let me know if this
works with newer versions of Excel.

Sub getFolderItems()
Dim cf As Variant
Dim i As Integer

' open finder choose folder dialog window
cf = MacScript("Choose Folder")
' remove alias from the beginning of the folder path
cf = Mid(cf, 7, Len(cf))
With Application.FileFind

.Options = msoOptionsNew
.SearchPath = cf
.SearchSubFolders = True
.FileName = ""
.Execute

With .FoundFiles

For i = 1 To .Count

Worksheets(1).Cells(i, 1) = getFileName(.Item(i))
Next i
End With
End With

End Sub


Function getFileName(filePath As Variant) As String
Dim character As String
Dim i As Integer

For i = Len(filePath) To 1 Step -1

character = Mid(filePath, i, 1)
If character = ":" Then

Exit Function
Else
getFileName = character & getFileName
End If
Next i

End Function

 

[JE McGimpsey]

Let me know if this works with newer versions of Excel.

It does not - FileFind is not implemented in XL04.