the number of the files in your search may change from day to day. You must
first see if the file already exists in column A. If the filst exists then
you only want to update the file size and last modified. Otherwise you want
to add anew row

Excel and Access each have ther own advantages and disadvantes. the VBA
language is the same and the file structures are very similar. the
Application is idfferent. Access is better for large databases and if you
require filesharing. Excel is more user friendly in the methods you use to
enter data and formulas; and the way the data is displayed and formated.

Try these changes.

Sub FilelistUpdates()
Dim fso As Object, folder As Object
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Range("A2:C2").Resize(ws.Cells(Rows.Count,
"A").End(xlUp).Row).ClearContents

If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))

lngRow = ws.Range("A" & rows.Count).end(xlup).row
lngRow = lngrow + 1

For Each fl In folder.Files
FName = folder.Path & "\" & fl.Name
set c = ws.columns("A").Find(what:=FName, _
lookin:=xlvalues,lookat:=xlwhole)

if c is nothing then
DataRow = lngRow
ws.Range("A" & DataRow).Formula = _
"=hyperlink(""" & FName & """,""" & FName & """)"
lngRow = lngRow + 1
else
DataRow = c.row
end if

ws.Range("B" & DataRow) = fl.Size
ws.Range("C" & DataRow) = fl.DateLastModified

Next

End If
Next
End Sub


"driller" wrote:

Hello genuine guys,

Is the macro function <see below here still possible for adjustment so I
can use this very good macro, productively, as often as needed ? Data to
build are "filenames" <i.e. €œ*.pdf€�,..

Steps Criteria :
Source address is in A1
1st the generated list of filenames shall be on Col A2~C, start row 2
2nd the next generated list of filenames (e.g. after re-using the function
again) shall append/build-up from the bottom of previously generated row of
data.

The filenames are searched from a folder address as written in A1.
This folder stores files in a daily basis.
Bottom line, I have to perform a routine listing of filenames without
re-building the previously gathered filenames.

--
Sub FilelistUpdates()
Dim fso As Object, folder As Object
Dim lngRow As Long, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

For Each ws In Worksheets
ws.Range("A2:C2").Resize(ws.Cells(Rows.Count,
"A").End(xlUp).Row).ClearContents

If fso.FolderExists(ws.Range("A1")) Then
Set folder = fso.GetFolder(ws.Range("A1"))
lngRow = 2

For Each fl In folder.Files
'ws.Range("A" & lngRow).Formula = "=hyperlink(""" & folder.Path & "\" &
fl.Name & """,""" & fl.Name & """)"
ws.Range("A" & lngRow).Formula = "=hyperlink(""" & folder.Path & "\" &
fl.Name & """,""" & fl.Parentfolder.Name & "\" & fl.Name & """)"

ws.Range("B" & lngRow) = fl.Size
ws.Range("C" & lngRow) = fl.DateLastModified
lngRow = lngRow + 1
Next

End If
Next
End Sub
--
The above macro works for me yesterday, then when i re-hit the function
today, the new list are generated and the yesterday's filename list were
rearranged.
I have already written working/recording notes along Col. D for each
filename yesterday and now I have to re-arranged it <where it fits all over
again...

I am not familiar yet with ms access..i believe excel is more powerful and
flexible..
--
Regards �