PDA

View Full Version : Solved: macro to add FileName and currentdate to an existing worksheet



satyen
04-16-2008, 01:26 PM
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
04-16-2008, 02:12 PM
Sub FileDate()
Sheets("Files").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(ActiveWorkbook.Name, Date)
End Sub

satyen
04-16-2008, 02:16 PM
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
04-16-2008, 02:25 PM
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

satyen
04-16-2008, 02:35 PM
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
04-16-2008, 02:44 PM
Is this what you are after?

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

satyen
04-16-2008, 02:53 PM
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
04-16-2008, 02:57 PM
Can't think of a quicker method offhand.

satyen
04-16-2008, 02:58 PM
How do I change this thread to solved?

mdmackillop
04-16-2008, 03:59 PM
Use the Thread Tools dropdown