Macro for creating a directory file list in a worksheet

[G. L. Swientek]

I am trying to figure out how to create a macro that will look into a
specific directory on the hard drive and then write the list of
filenames contained therein to a worksheet. Unfortunately all the
examples that I've come across refer to the FileFind feature that
doesn't work in the Macintosh version of Excel. Can anyone point me in
the right direction?

Gerard

 

[Jim Gordon MVP]

Hi,

FileFind was disabled for both Mac and Windows. That leaves the DIR command.

If DIR doesn't have the ability to do what you are asking you might take
a look at AppleScript to see whether or not it can provide the file
names. AppleScript can pass values to VBA.

-Jim

 

[G. L. Swientek]

Thanks Jim!

I was able to dope out enough of info on the DIR command to come up
with this code:

Public Sub LoopThroughXLS()
Dim sFName As String
#If Mac Then
sFName = Dir("Users:gerardesktop:Bid-folder:Bids-Accepted:",
MacID("XLS8"))
#Else
sFName = Dir("*.xls")
#End If
Do While Len(sFName) > 0
Debug.Print sFName
sFName = Dir
Loop
End Sub

And it does work in printing it to the Immediate window. But since I am
such a neophyte at this, I don't know how to display the results in an
open range of cells until it reaches the last file name. I would like
to start it off at cell A4 and have it fill the column until it reaches
the last file name. Could you are anyone help me wiht the print command
that does that? Thanks!

G

 

[JE McGimpsey]

One way:

Public Sub LoopThroughXLS()
Const nMACPATH As String = _
"Users:gerardesktop:Bid-folder:Bids-Accepted:"
Dim nCount As Long
Dim sFName As String
#If Mac Then
sFName = Dir(nMACPATH, MacID("XLS8"))
#Else
sFName = Dir("*.xls")
#End If
Do While Len(sFName) > 0
Cells(4 + nCount, 1).Value = sFName
nCount = nCount + 1
sFName = Dir
Loop
End Sub

 

[G. L. Swientek]

Thanks J! That did it!