List directory (folder) names for Mac Excel

Hi there


I am completely new to the idea of macros and VBA, but excited by the potential. I have a book project I am working on, and would like to list all of the folder names from a (massive) set of incoming projects organised into folders.
I found this post (http://www.excelforum.com/excel-prog...directory.html) which seems to do the trick for Windows based Excel, but not Mac - I get the message

'Runtime Error 428 Active X component can't create object' - I think thi sis due to OSX not being compatible with VBA 'objects' - is that right?

Here is the script below - anyone have any clues as to how I can configure it for Mac OSX Excel?

thanks for your time!

Sub ListThem()
   Dim startRange As Range
 
  Sheet1.Cells.Clear
  Set startRange = Sheet1.Range("A1")
  'Parent Directory - Change this to whichever directory you want to use
  ListFoldersAndInfo "afp://Xserve._afpovertcp._tcp.local/Groups/Projects/558_Murderme/MICHAEL JOO/incoming/Images/Works", startRange

End Sub
 
 
Sub ListFoldersAndInfo(FolderName As String, Destination As Range)
  
  Dim FSO As Object
  Dim Folder As Object
  Dim R As Long
  Dim SubFolder As Object
  Dim Wks As Worksheet
 
    Set FSO = CreateObject("Scripting.FileSystemObject")
 
      Set Folder = FSO.GetFolder(FolderName)
 
      For Each SubFolder In Folder.SubFolders
        Destination = SubFolder.Name
        Destination.Offset(0, 1) = SubFolder.Path
        Destination.Offset(0, 2) = SubFolder.Size
        Set Destination = Destination.Offset(1, 0)
      Next SubFolder
 
    Set FSO = Nothing
 
End Sub

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

you can't use the scripting runtime on a mac and dir won't give you folder sizes (it also doesn't work with file names longer than 27/28 characters) so I reckon you'd need apple script for that. I'd try asking on macscripter.net