Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Filenames AND details to Excel
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy Filenames AND details to Excel
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 "deeds" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type details into one cell and they copy into another | Excel Discussion (Misc queries) | |||
Macro for copy pasting details from main sheet | Excel Discussion (Misc queries) | |||
Copy row details where cell value meets certain criteria | Excel Worksheet Functions | |||
using excel to rename filenames? | Excel Discussion (Misc queries) | |||
How do I copy subtotaled data without the underlying details? | Excel Worksheet Functions |