COPY only file names into excel

[tizzodizzo]

I am trying to copy just the names of a file from a directory and patse it into different cell within excel. I don't need anything from with-in the file. I just need the name of the file. Can anyone Help?

 

[SkipVought]

Hi,

Check out the FileSystemObject

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

... or Dir Function

Returns a String representing the [blue]name of a file[/blue], directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.

Syntax

Dir[(pathname[, attributes])]

Have fun.

---- Andy

 

[macropod]

Hi tizzodizzo,

You could use something like:

Public Sub ListAllFiles()
Dim FName As Variant
Dim i As Integer
i = 0
FName = Dir("C:\My Documents\*.xls", vbNormal)
While FName <> ""
  i = i + 1
  ActiveSheet.Cells(i, 1).Value = FName
  FName = Dir()
Wend
End Sub

Note that, as coded, the macro only lists xls files in the nominated folder. To get a different file type, change the 'xls' in the code to suit (use '*.*' for all files).

Cheers

[MS MVP - Word]