Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with macro
The following macro list the file names in a specific directory on to a
worksheet named "TAS forms received" 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 Worksheet Set newWks = Worksheets("TAS forms received") Application.ScreenUpdating = False FileLocation = "F:\Finance\Transparency\Data collection\TAS forms received\*.xls" FN = Dir(FileLocation) Do Until FN = "" ThisRow = ThisRow + 1 newWks.Cells(ThisRow, 1) = FN FN = Dir Loop Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with macro
nc,
Change FN = Dir(FileLocation) Do Until FN = "" ThisRow = ThisRow + 1 newWks.Cells(ThisRow, 1) = FN FN = Dir Loop to FN = Dir(FileLocation) Do Until FN = "" If IsError(Application.Match(FN, newWks.Range("A:A"), False)) Then newWks.Cells(Rows.Count, 1).End(xlUp)(2).Value = FN End If FN = Dir Loop HTH, Bernie MS Excel MVP "nc" wrote in message ... The following macro list the file names in a specific directory on to a worksheet named "TAS forms received" 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 Worksheet Set newWks = Worksheets("TAS forms received") Application.ScreenUpdating = False FileLocation = "F:\Finance\Transparency\Data collection\TAS forms received\*.xls" FN = Dir(FileLocation) Do Until FN = "" ThisRow = ThisRow + 1 newWks.Cells(ThisRow, 1) = FN FN = Dir Loop Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
Editing a simple macro | Excel Worksheet Functions | |||
Can T Get Macro To Run! | New Users to Excel | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Date macro | Excel Discussion (Misc queries) |