Copy Filenames AND details to Excel

[deeds]

Below is some code that I use to get display the filenames from directories
into excel. However, it only brings in the filename...how can I also get the
details portion of it...namely the modified date? Any ideas.
Thanks in advance


Sub AllFilenames()
Rem Change D:\ to your folder path
D = "C:\MY FILES\"
Cells(1, 1) = "Filenames"
r = 2
f = Dir(D, 7)
Do While f <> ""
Cells(r, 1) = f
r = r + 1
f = Dir
Loop
End Sub

 

[Conan Kelly]

deeds,

I haven't tested this, but I think it will do what you are asking.

It will prompt you to select any file in the folder you want to list the
contents of.

I used the object variable "pfsoFile" for file system files. If you put
your cursor any where below the variable declarations and type "pfsoFile."
(<--notice there is a dot/period at the end), the VBE should list all the
properties of file system files that you can return.

(Make sure to set a reference to MS Scripting Runtime library)





Option Explicit

Sub ChangeFileNames()
Dim pfso As New FileSystemObject
Dim pfsoFolder As Folder
Dim pfsoFile As File
Dim pstrFileDate As String
Dim pdteFileDate As Date
Dim pstrFile As String
Dim pstrFolder As String
Dim pintCounter As Integer

pintCounter = 2

pstrFile = Application.GetOpenFilename(, , "Please select any file in
the folder that you want to list the contents for")

pstrFolder = Left(pstrFile, InStrRev(pstrFile, "\"))

'MsgBox pstrFolder

'Set pfsoFolder = pfso.GetFolder("C:\MY FILES\")
Set pfsoFolder = pfso.GetFolder(pstrFolder)

Cells(1, 1) = "File Names"
Cells(1, 2) = "Modified Date"


For Each pfsoFile In pfsoFolder.Files
Cells(pintCounter, 1) = pfsoFile.Name
Cells(pintCounter, 2) = pfsoFile.DateLastModified
pintCounter = pintCounter + 1
Next pfsoFile
End Sub



HTH,

Conan