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
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" <[email protected]> a écrit dans le message de news:
[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks