Hi Experts,

I have a scenario where I have list of text files that needs to be consolidated in a separate worksheet. I have a macro in place however the instead of giving folder in the coding I want that to be picked with the cell reference, where I have given the path of the file with the formula reference.

The list of 21 text files to be open and consolidated in the next sheet with the help of reference in the cell.

List of files in column A

File reference path in column J

Find below the coding.

Sub ImpTxt()

Dim i As Long
Dim lRow As Long


Sheets(2).Cells.ClearContents

fDir = "C:\Users\bkrishnamurthy\Documents\Payroll\Week1\" (here instead of giving the full path can I give the cell reference numbers to pick the files)
Count = 0
MyFile = Dir(fDir & "*.txt")

While MyFile <> ""
Count = Count + 1
Sheets(1).Cells(Count, 1) = fDir & MyFile
MyFile = Dir
Wend

For i = 1 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Sheets(2).Select

lRow = Range("A1048576").End(xlUp).Offset(1, 0).Row

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & Sheets(1).Cells(i, 1), Destination:=Range("$A$" & lRow))
.Name = "imptxt" & i
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Next


For i = 1 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
On Error Resume Next
ActiveWorkbook.Connections("imptxt" & i).Delete
Cells.QueryTable.Delete
Next

Range("A1").Select

Call DelimitingText

End Sub