Retrieving File Info

[Harapa]

Appreciate any help that I can get to do this in excel.

I have a database of about 4000 files, when I update the database
daily some of files are not updated on a given day for variety of
reasons. I would like to know the names of all files that were not
updated on a given date. The structure of my data base is as follow

c:\DB\A (have all files begining with letter A)
c:\DB\B (have all files begining with letter B)
...... and so on untill the last folder as Z
c:\DB\Z (chave all files begining with letter Z)

I thought of two ways
First, to have a macro that will retrieve names of all files not
updated on the date supplied in the query

Second, to retrieve names and last saved date of all files and then
sort those in excel to find the one that has the last save date older
then in the query

I am sure there are bettr ways of doing this then above.
Appreciate your help and time.

 

[Bob Phillips]

Harapa,

Do you store the updated date in the database? If so, why not just use
Autofilter to limit that date column to dates earl;ier than your nominated
date?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Jim Cone]

Harapa,

It appears your files are stored in the normal Windows file system.
If so, my "List Files" Excel add-in should do the trick.
It finds files with the extension you specify, such as *.xls, in the folder
you specify and returns a list on a new worksheet. Each file is hyperlinked
and gives the file name, path, size and date last modified. It can be
sorted by date.
Comes with a one page install/instructions Word.doc.

It is available, for free, upon direct request - remove xxx from address.

Regards,
Jim Cone
San Francisco, CA
(e-mail address removed)

Appreciate any help that I can get to do this in excel.
I have a database of about 4000 files, when I update the database
daily some of files are not updated on a given day for variety of
reasons. I would like to know the names of all files that were not
updated on a given date. The structure of my data base is as follow
c:\DB\A (have all files begining with letter A)
c:\DB\B (have all files begining with letter B)
..... and so on untill the last folder as Z
c:\DB\Z (chave all files begining with letter Z)
I thought of two ways

 

[Harapa]

I have this macro that works well with .xls or .txt files. I am sure I
got it from a user in this forum. As files in my database are in
neither of these formats and the macro returns nothing. In addition it
doesn't capture anything other than file name. I need file name as
well as the last time it got modified. Any way, I sent a separate
messege to ypu requesting the Excell add in you mentioned. Thanks for
you help.
Harapa

......................................................

Sub ListFileNames()
'Lists all file names in the folder on the active sheet
Dim FileName As String
Dim r As Integer
r = 2
With Application.FileSearch
.NewSearch
.LookIn = "C:\CustomDatabase\"
.FileName = ""
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
FileName = Mid(.FoundFiles(i), 23)
Cells(r, 1) = FileName
r = r + 1
Next i
End If
End With
End Sub

 

[Harapa]

My database store information in individual files that are not
accessible in Excell-some propriety format(not mine but of vendor of
the software package I use).

 

[Jim Cone]

Harapa,

The "List Files" program is on its way via email.
For what its worth, the program uses a recursive scripting runtime code
routine that I wrote. It's way more responsive and accurate than the
regular Application.FileSearch stuff.

Regards,
Jim Cone
San Francisco, CA

I have this macro that works well with .xls or .txt files. I am sure I
got it from a user in this forum. As files in my database are in
neither of these formats and the macro returns nothing. In addition it
doesn't capture anything other than file name. I need file name as
well as the last time it got modified. Any way, I sent a separate
messege to ypu requesting the Excell add in you mentioned. Thanks for
you help.
Harapa
.....................................................
Sub ListFileNames()
'Lists all file names in the folder on the active sheet
Dim FileName As String
Dim r As Integer
r = 2
With Application.FileSearch
.NewSearch
.LookIn = "C:\CustomDatabase\"
.FileName = ""
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
FileName = Mid(.FoundFiles(i), 23)
Cells(r, 1) = FileName
r = r + 1
Next i
End If
End With
End Sub

-snip-

 

[Harapa]

Thanks a lot for the add in. It is able to do exactly what I want.
Harapa.