Query to find last saved date/Time
I want to run an MSQuery on all the spreadsheets on my network drive, to find
when they were last modified/accessed! Would it be possible in MSQuery or easier in VB? Can anyone suggest a way to do it? Thanks Dean |
Query to find last saved date/Time
This is no job for MSQUERY
Since you are in an EXCEL NG, use VBA. Look at FilesSearch in Help HTH -- AP "Dean" a écrit dans le message de news: ... I want to run an MSQuery on all the spreadsheets on my network drive, to find when they were last modified/accessed! Would it be possible in MSQuery or easier in VB? Can anyone suggest a way to do it? Thanks Dean |
Query to find last saved date/Time
Sub ListFileAttributes()
Dim FSO As Object Dim i As Long Dim sFolder As String Dim fldr As Object Dim Folder As Object Dim file As Object Dim Files As Object Dim this As Workbook Dim aryFiles Dim cnt As Long Dim sh As Worksheet Set FSO = CreateObject("Scripting.FileSystemObject") Set this = ActiveWorkbook sFolder = "C:\MyTest" Set Folder = FSO.GetFolder(sFolder) ReDim aryFiles(1 To 3, 1 To 1) Set Files = Folder.Files cnt = 0 For Each file In Files If file.Type = "Microsoft Excel Worksheet" Then cnt = cnt + 1 ReDim Preserve aryFiles(1 To 3, 1 To cnt) aryFiles(1, cnt) = file.Path aryFiles(2, cnt) = Format(file.DateLastModified, "dd mmm yyyy hh:mm:ss") aryFiles(3, cnt) = Format(file.DateCreated, "dd mmm yyyy hh:mm:ss") End If Next file On Error Resume Next Set sh = Worksheets("ListOfFiles") On Error GoTo 0 If sh Is Nothing Then Worksheets.Add.Name = "ListOfFiles" Else sh.Cells.ClearContents End If For i = LBound(aryFiles, 2) To UBound(aryFiles, 2) Cells(i + 1, "A").Value = aryFiles(1, i) Cells(i + 1, "B").Value = aryFiles(2, i) Cells(i + 1, "C").Value = aryFiles(3, i) Next i Range("A1").Value = "Filename" Range("B1").Value = "Modified" Range("C1").Value = "Created" Columns("A:C").AutoFit End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "Dean" wrote in message ... I want to run an MSQuery on all the spreadsheets on my network drive, to find when they were last modified/accessed! Would it be possible in MSQuery or easier in VB? Can anyone suggest a way to do it? Thanks Dean |
All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com