Re: Browse for Folder/Directory

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Feb 28, 3:00 pm, kamran....@xxxxxxxxx wrote:
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


.

Quantcast