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

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

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