Macro to log .xls file names

[vrzimmerm]

I have an existing macro that first allows the user to open an xls
file, then it copies data from the selected file into the one where
the macro is located. This info is added to the existing data that's
already there. The user will run the macro multiple times (once for
each file to be added) ....thus ending with a "master" file composed
of data from each file they had selected.

That part is done. Now, on a separate sheet in the macro file I'd
like to create a log where each row shows the name of each file that
had been opened and copied into the master, along with the date when
this was done.

How would such a macro work?

Thanks.

 

[Guest]

I'd create a log worksheet and give it a worksheet code name that won't
change (Say "Logsheet")

Let's assume when you open the worksheet, you do it something like this

Dim oWB as workbook
set oWB = workbooks.open(filename)

I'm also going to assume that you reference the active book (the book that
you are writing data to) as aWB (with the appropriate dimensions)

Dim oWB As Workbook
Dim aWB As Workbook
Dim WS As Worksheet
Dim myWS As Worksheet
Dim lRow As Long

Set aWB = ActiveWorkbook

For Each WS In aWB.Worksheets
If WS.CodeName = "Logsheet" Then
Set myWS = WS
Exit For
End If
Next WS

If Not myWS Is Nothing Then
lRow = myWS.Cells(myWS.Rows.Count, 1).End(xlUp).Row
If lRow < myWS.Rows.Count Then
myWS.Cells(lRow + 1, 1) = oWB.Name
myWS.Cells(lRow + 1, 2) = Date
End If
End If