Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
Jim Cone I do not understand what you mean by (Scripting) FileSystemObject
"Jim Cone" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
I will clean it up later today !!
-- 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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/d...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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA codes to get dates of files in a folder
Thank you for your response.
"Jim Cone" wrote: 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/d...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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling pdf files from general folder to specific folder | Excel Discussion (Misc queries) | |||
Creating a unique list of Cost Codes in Col. A from all worksheets in all workbooks in folder X | Excel Programming | |||
User selection of folder and open all .xls files within folder | Excel Programming | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Programming |