software

The following macro list the file names in a specific directory on to a
worksheet named quot;TAS forms receivedquot; in column A and row 1. Each time it is
run overwrites the list.

What do I need to add to the macro so that the prior list is never
overwritten but the new file names in the directory are added at the end of
the list.

Thanks.Private Sub Worksheet_Activate()

Dim FN As String ' For File Name
Dim ThisRow As Long
Dim FileLocation As String
Dim newWks As WorksheetSet newWks = Worksheets(quot;TAS forms receivedquot;)

Application.ScreenUpdating = False
FileLocation = quot;F:\Finance\Transparency\Data collection\TAS forms
received\*.xlsquot;

FN = Dir(FileLocation)
Do Until FN = quot;quot;
ThisRow = ThisRow 1
newWks.Cells(ThisRow, 1) = FN
FN = Dir
Loop

Application.ScreenUpdating = True

End Sub
nc,

Change

FN = Dir(FileLocation)
Do Until FN = quot;quot;
ThisRow = ThisRow 1
newWks.Cells(ThisRow, 1) = FN
FN = Dir
Loopto

FN = Dir(FileLocation)
Do Until FN = quot;quot;
If IsError(Application.Match(FN, newWks.Range(quot;A:Aquot;), False)) Then
newWks.Cells(Rows.Count, 1).End(xlUp)(2).Value = FN
End If
FN = Dir
Loop

HTH,
Bernie
MS Excel MVPquot;ncquot; gt; wrote in message
...
gt; The following macro list the file names in a specific directory on to a
gt; worksheet named quot;TAS forms receivedquot; in column A and row 1. Each time it is
gt; run overwrites the list.
gt;
gt; What do I need to add to the macro so that the prior list is never
gt; overwritten but the new file names in the directory are added at the end of
gt; the list.
gt;
gt; Thanks.
gt;
gt;
gt; Private Sub Worksheet_Activate()
gt;
gt; Dim FN As String ' For File Name
gt; Dim ThisRow As Long
gt; Dim FileLocation As String
gt; Dim newWks As Worksheet
gt;
gt;
gt; Set newWks = Worksheets(quot;TAS forms receivedquot;)
gt;
gt; Application.ScreenUpdating = False
gt;
gt;
gt;
gt; FileLocation = quot;F:\Finance\Transparency\Data collection\TAS forms
gt; received\*.xlsquot;
gt;
gt; FN = Dir(FileLocation)
gt; Do Until FN = quot;quot;
gt; ThisRow = ThisRow 1
gt; newWks.Cells(ThisRow, 1) = FN
gt; FN = Dir
gt; Loop
gt;
gt; Application.ScreenUpdating = True
gt;
gt; End Sub
gt;
gt;
gt;
gt;
gt;