Need to add full file path in column A with one line of text from opened txt file going into each column going left to right

[HowdeeDoodee]

The macro is working and is shown below. The macro retrieves three lines of text from a text file. One of the lines retrieved is inserted into Col A, another line into Col B, and and the third line Col C.

In the macro shown below, I need to add a new column A to show the file path and file name of the txt file being accessed. File access is being done using the FileSystemObject in the macro as shown below. Currently, one line of text of the three lines retrieved from each file are inserted into each of three columns. The first line of text in the accessed txt file is shown col A, the second line from the accessed txt file is shown in Col B, and the third line of text from the accessed txt file is shown in Col C.

However, I need to see the full file path with the file name in col A.
Essentially, I need only to add a col A showing the full file path. Everything else in the macro can stay the same, except the cols B, C, and D are moved over from their current position of cols A, col B, and col C

The contents of the txt file with the first line of text goes into col B, the second line of text goes into col C, and the third line of text goes into col D, all the lines of text going on the same row.

This is how the sheet looks now. Only three columns are used.

Col A----------------------Col B-------------------Col C
1st line of txt------------2nd line of txt-----------3rd line of text

This is how the sheet should look after the macro is changed. Four columns are used

Col A-----------------Col B-------------------Col C-------------------Col D
File path---------1st line of txt------------2nd line of txt -----------3rd line of txt

I believe these lines are the lines in the macro that need to be changed.

' Print to sheet
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1 & ":BR" & LR + 1) = Arr
strTxt = vbNullString


Here is the macro.

Sub print_file_content()

'select cells to be process in col A
Dim i%, strTxt$, OpenTxt As Object, fso As Object, myFile, myFolder, LR&, Arr, fPath$


' Use filesystemobject to manage files and folders
Set fso = CreateObject("Scripting.FileSystemObject")
fPath = "f:\Backup\ArtAppreciation\"
' Import from all text files
Set myFolder = fso.GetFolder(fPath).Files
For Each myFile In myFolder
If LCase(myFile) Like "*.txt" Then
' Open file and convert to ASCII if not already so
Set OpenTxt = fspenTextFile(myFile, 1)
' Get required lines
For i = 1 To 3
If strTxt = "" Then
strTxt = Application.Clean(OpenTxt.ReadLine)
Else
strTxt = strTxt & "|" & Application.Clean(OpenTxt.ReadLine)
End If
Next
' Convert text to array
Arr = Split(strTxt, "|")
' Print to sheet
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & LR + 1 & ":BR" & LR + 1) = Arr
strTxt = vbNullString
End If
Next

' Turn functionality back on
With Application
.Calculation = xlCalculationAutomatic
.DisplayStatusBar = True
.EnableEvents = True
End With


End Sub

Thank you in advance for your reply.

 

[gaj104]

Try:

Sub print_file_content()

'select cells to be process in col A
Dim i%, strTxt$, OpenTxt As Object, fso As Object, myFile, myFolder, LR&, Arr, fPath$


' Use filesystemobject to manage files and folders
Set fso = CreateObject("Scripting.FileSystemObject")
fPath = "F:\Text\"
' Import from all text files
Set myFolder = fso.GetFolder(fPath).Files
For Each myFile In myFolder
    If LCase(myFile) Like "*.txt" Then
        
        ' Open file and convert to ASCII if not already so
        Set OpenTxt = fso.OpenTextFile(myFile, 1)
        LR = Range("A" & Rows.Count).End(xlUp).Row + 1
        
        ' Get required lines
        Range("A" & LR).Value = myFile
        For i = 1 To 3
            Cells(LR, i + 1).Value = Application.Clean(OpenTxt.ReadLine)
        Next i
        
    End If
Next

' Turn functionality back on
With Application
.Calculation = xlCalculationAutomatic
.DisplayStatusBar = True
.EnableEvents = True
End With


End Sub

Note, in your original code "Range("A" & LR + 1 & ":BR" & LR + 1)" the entries went from A to BR (D - BR would be populated with #N/A) in case that throws up any quirks.

 

[HowdeeDoodee]

Excellent!!! Yes! Most high gaj104

Thank you very much.

 

[HowdeeDoodee]

Excellent!!! Yes! Most high gaj104

Thank you very much.

 

[HowdeeDoodee]

Mods would you please mark this thread solved.