Solved: macro to add FileName and currentdate to an existing worksheet

[satyen]

Hello

I need to add some code to a macro so that each time it is run it will add the FileName (in Cell A2) and CurrentDate (in cell B2) into an already set up spreadsheet called "Files". Headings (already set up) will be in A1 and B1

Everytime the macro is run there will be new files in the folder (probably between 50-100), so data in "Files" should not be overwriten data but instead add name and current date to the next un-used row down and so on.

Thanks in advance

[mdmackillop]

[VBA]Sub FileDate()
Sheets("Files").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(ActiveWorkbook.Name, Date)
End Sub
[/VBA]

[satyen]

Thanks- can you tweek the code so that it adds names and current date of all files in a specific folder that reside in a folder, also would like the code to open a separate Workbook called Files and adds the information in Sheet1 of "Files". It will then always add information to this workbook

[mdmackillop]

[VBA]Sub FileDate()
Dim WB As Workbook
Set WB = Workbooks.Open("C:\AAA\Files.xls")
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(ActiveWorkbook.Name, Date)
WB.Close True
End Sub[/VBA]

[satyen]

Thanks -nearly there. I need the names of the files that are in another directory, i.e ("C:\BBB\"). There will be around 50-100 files probably. The information (filename and current date +time) still has to go into "Files"

[mdmackillop]

Is this what you are after?
[VBA]
Sub FileDate()
Dim WB As Workbook, MyFile As String
Set WB = Workbooks.Open("C:\AAA\Files.xls")
MyFile = Dir("C:\BBB\*.*")
Do Until MyFile = ""
Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(MyFile, Date)
MyFile = Dir
Loop
WB.Close True
End Sub

[/VBA]

[satyen]

This works perfectly! Many Thanks. I just replaced Date with Now so it gives me a date and time. Because I will have between 50-100 files may be more, in the BBB folder is this the most efficient code? Just a query.

[mdmackillop]

Can't think of a quicker method offhand.

[satyen]

How do I change this thread to solved?

[mdmackillop]

Use the Thread Tools dropdown