VBA: import text from multiple text files into rows of a single excel worksheet in column B with given addresses in column A

[azov5]

VBA: import text from multiple text files into rows of a single excel worksheet in column B with given addresses in column A

 

[Saurabhj]

Hi, please share the text file to see how data is available. Also share the expected output in Excel file.

Thanks,
Saurabh

 

[azov5]

Hi, please share the text file to see how data is available. Also share the expected output in Excel file.

Thanks,
Saurabh

Here is a link to the demo Excel file: https://easyupload.io/zeazmt
Here is one of the demo text file: Gofile

Thanks in advance.

 

[Saurabhj]

Hi,

Check below code.
Before using code, add reference as below:
Tool -> References -> Microsoft scripting RunTime

Sub txtToExcel() 
    
    Dim objFSO As FileSystemObject
    Dim objTS As TextStream
    Dim filePath As String
    Dim rowno As Integer
    Dim strFile As String
    
    Application.ScreenUpdating = False
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    For rowno = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        filePath = ActiveSheet.Range("A" & rowno)
        If Dir(filePath) <> vbNullString Then
            Set objTS = objFSO.OpenTextFile(filePath, ForReading, False, TristateUseDefault)
            ActiveSheet.Range("B" & rowno).Value = objTS.ReadAll
            objTS.Close
        Else
            ActiveSheet.Range("B" & rowno).Value = "File Not Found"
        End If
    Next
    Application.ScreenUpdating = True

End Sub

 

[azov5]

Saying "File Not Found" in cells.

 

[Saurabhj]

Hi, Please share what the sheet contains using XL2BB.

Make sure file exists in the path you specified in column A of the sheet.
Which line is highlighted when you debug the error ?

 

[azov5]

Worked. file-path was the issue. Thank you.
Through a separate VBA can I combine column B cell data with Column E cell data?
and save the combined data as a new random file name (given in row cells of columns C) in the given folder location on the desktop (Given directory location in row cells of columns D)?

 

[azov5]

Hi,

Check below code.
Before using code, add reference as below:
Tool -> References -> Microsoft scripting RunTime

Sub txtToExcel()
   
    Dim objFSO As FileSystemObject
    Dim objTS As TextStream
    Dim filePath As String
    Dim rowno As Integer
    Dim strFile As String
   
    Application.ScreenUpdating = False
    Set objFSO = CreateObject("Scripting.FileSystemObject")
   
    For rowno = 2 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        filePath = ActiveSheet.Range("A" & rowno)
        If Dir(filePath) <> vbNullString Then
            Set objTS = objFSO.OpenTextFile(filePath, ForReading, False, TristateUseDefault)
            ActiveSheet.Range("B" & rowno).Value = objTS.ReadAll
            objTS.Close
        Else
            ActiveSheet.Range("B" & rowno).Value = "File Not Found"
        End If
    Next
    Application.ScreenUpdating = True

End Sub

If the file starts with "=" or any other special character or number then the VBA does not work. Can this be done?

 

[Saurabhj]

File name cant contain special characters.

 

[azov5]

File name cant contain special characters.

not the file name. I mean if the text in the file starts with a special character then the VBA does not work and I have some special characters before the starting text in some files. Help.