VBA codes to get dates of files in a folder
Thnk you again fro your response.
"Gary''s Student" wrote:
As promised:
Sub Macro3()
'
'gsnuxx
'
Dim v As String, n As Long
Dim r As Range
x = Shell("cmd.exe /c dir /Tw c:\temp\*.xls c:\temp.csv", 1)
Workbooks.Open Filename:="C:\temp.csv"
Set r = ActiveSheet.UsedRange
n = r.Rows.Count + r.Row - 1
For i = n To 1 Step -1
v = Cells(i, "B").Value
If Right(v, 3) < "xls" Then
Cells(i, "B").EntireRow.Delete
Else
Cells(i, "B").Value = Split(v, " ")(1)
End If
Next
End Sub
1. you can easily changed the folder being "searched"
2. you can easily change the filetype being "searched"
3. you can easily change the attribute being captured
4. extraneous junk records have been removed
5. it can be modified to do sub-folders as well.
--
Gary''s Student - gsnu200756
"Manuelauch" wrote:
Gary, thank for the anwser but your approach is to messy to extract the file
name. If I do not find another way I will probably have to use your.
"Gary''s Student" wrote:
Sub Macro3()
x = Shell("cmd.exe /c dir /Tw c:\temp\*.xls c:\temp.csv", 1)
Workbooks.Open Filename:="C:\temp.csv"
End Sub
This will get you the file and dates. You need to delete any extraneous
records.
--
Gary''s Student - gsnu200756
"Manuelauch" wrote:
I was using these lines of codes in EXCEL 2003 to get files names and dates
in a folder, they do not woork in EXCEL 2007. Any new codes or modification I
can use
Set obFileSearch = Application.FileSearch
With obFileSearch
.LookIn = "C\:Temp\"
.Filename = "*.xls" '
'True, files were found
If .Execute 0 Then
'Enter file names in spreadsheet
For iCounter = 1 To .FoundFiles.Count
Cells(19 + iCounter, 7).Value = .FoundFiles(iCounter)
Cells(19 + iCounter, 8).Value =
FileDateTime(.FoundFiles(iCounter))
Next iCounter
End If
End With
|