Pull File Name from Looping Files

[Eric G]

How do I pull a file name from a file being looped from a batch of files?

• I have a folder with a bunch of Excel files that I extract data from.
• The extracted data is consolidated into a single tab on a separate spreadsheet, which is located in a different folder.
• I would like to know which file the extracted data came from by having it recorded in the consolidated spreadsheet.
• The specific line of code in question is:

'Filename
 ws.Range(Cells(LR2 + 1, 1), ws.Cells(LR2 + 1, 1)) = ???

• Where I have type the question marks:
  1. I have tried "Thisworkbook.FullName," but I get the name of the spreadsheet which houses my VBA code and NOT from the spreadsheet I am extracting data.
  2. I have tried both "wb.FullName" and "wb," but I get an error.
• Below is not all of my code, but the portions I hope that provides you with enough context.<strike></strike>

'Destination (File)
    Dim wb3 As Workbook
    Dim ws As Worksheet
    Set wb3 = Workbooks.Open(Str_Consolidate)
    Set ws = wb3.Worksheets("Consolidate")

'Source (Files)
    Dim strP, strF As String
    Dim wb As Workbook
    strP = rng1
    strF = Dir(strP & "\*.xlsx")

Do While strF <> vbNullString
    Set wb = Workbooks.Open(strP & "\" & strF)

GoTo EXECUTE_EXTRACTION_CODE
CONTINUE_LOOP:

    Application.DisplayAlerts = False
    wb.Close True
    Application.DisplayAlerts = True
    strF = Dir()
Loop

GoTo LASTLINE
EXECUTE_EXTRACTION_CODE:

'Last Row
    LR2 = ws.Cells(Rows.Count, 2).End(xlUp).Row

'DATA PULL
    With ws
        'Organizational Information
         .Range(.Cells(LR2 + 1, 3), ws.Cells(LR2 + 1, 6)).Value = ws101.Range("C3:G3").Value
    End With

'Filename
    ws.Range(Cells(LR2 + 1, 1), ws.Cells(LR2 + 1, 1)) = ???

GoTo CONTINUE_LOOP
LASTLINE:

Any suggestions?

 

[NdNoviceHlp]

"strF" seems to be your wb name. HTH. Dave

 

[Eric G]

"strF" seems to be your wb name. HTH. Dave

That did it. Thanks, Dave.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave