Copy filename into report?

[id107]

Hi. I have a workbook that compiles data that our team has completed each week. They fill in a tracker (A2:I300), send it back and I save them all into one folder amd run a macro to compile the report. Is there any way to also import the name of each file into this report? Preferably into column J.

The code I am currently using is:

Sub Compile_Data()
    Dim myDir As String, fn As String
    myDir = "m:\weekly_reporting\"
fn = Dir(myDir & "*.xls")
Do While fn <> ""
    If fn <> ThisWorkbook.Name Then
        With Workbooks.Open(myDir & fn)
            With .Sheets(1).Range("A2:I300")
                ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2) _
                .Resize(.Rows.Count, .Columns.Count).Value = .Value
            End With
            .Close False
        End With
    End If
    fn = Dir
Loop
End Sub

 

[ColinKJ]

Hi id107,

If I'm correct, you have the FileName in fn, so while you still have the file open, copy fn into J1. maybe something like

Code:

If fn <> ThisWorkbook.Name Then
With Workbooks.Open(myDir & fn)
With .Sheets(1).Range("A2:I300")
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp)(2) _
.Resize(.Rows.Count, .Columns.Count).Value = .Value

"ThisWorkbook.Sheets(1).Range("J1") = fn"

End With
.Close False
End With
End If

ColinKJ

 

[id107]

That's a very good point with the fn.

I'm trying to get the filename to be listed beside the data from the file and I've been trying to get this applied (your code helped a lot in my attempts), but I can't get it to line up properly.

Apologies. I'm ill and nothing is going well today.