VBA codes to get dates of files in a folder

[Guest]

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

 

[Jim Cone]

FileSearch was removed from XL2007.
You can use the "Dir" function or the (Scripting) FileSystemObject.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Manuelauch"
wrote in message
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

 

[Guest]

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.

 

[Guest]

Jim Cone I do not understand what you mean by (Scripting) FileSystemObject

 

[Guest]

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.

 

[Guest]

I will clean it up later today !!

 

[Jim Cone]

The Microsoft Scripting Runtime (scrrun.dll) is included with all current versions
of Windows. You can set a reference to it in Tools | References in the VBE or
use late binding. An example...
'---
Sub LatestFile()
'Jim Cone - San Francisco, USA - June 02, 2005
'Displays the latest file name in the strPath folder.

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim strPath As String
Dim strName As String
Dim varDate As Variant

' Specify the folder...
strPath = "C:\Program Files\Microsoft Office\Office\Library"
' Use Microsoft Scripting runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(strPath)

' Check date on each file in folder.
For Each objFile In objFolder.Files
If objFile.DateLastModified > varDate Then
varDate = objFile.DateLastModified
strName = objFile.Name
End If
Next 'objFile

' Display file name in message box.
MsgBox strName & " - is latest file - " & varDate

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub
'---
Also see...
http://www.microsoft.com/downloads/...48-207d-4be1-8a76-1c4099d7bbb9&DisplayLang=en
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)





"Manuelauch"
wrote in message
Jim Cone
I do not understand what you mean by (Scripting) FileSystemObject

 

[Guest]

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.

 

[Guest]

Thnk you again fro your response.

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.

 

[Guest]

Thank you for your response.