Here is a link to the demo Excel file: https://easyupload.io/zeazmtHi, please share the text file to see how data is available. Also share the expected output in Excel file.
Thanks,
Saurabh
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?Hi,
Check below code.
Before using code, add reference as below:
Tool -> References -> Microsoft scripting RunTime
VBA Code: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
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.File name cant contain special characters.