Pull File Name from Looping Files

Eric G

New Member
Joined
Dec 21, 2017
Messages
35
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:
Code:
'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>
Code:
'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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,993
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top