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:
- 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.
- 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: