Excel VBA Code to Read Text File Without Opening

Sub ReadTextFile_FastestWay()
    
    'Declare variables
    Dim Text_data As String
    Dim F_Path As String
    Dim F_Number As Integer
    
    'Set the file path (you have to modify the path accordingly)
    F_Path = "C:\Users\USER\Downloads\SOLVED EXCEL\A Fairy Song.txt"
    
    'Open the text file
    F_Number = FreeFile()
    Open F_Path For Input As #F_Number
    
    'Read the entire text file
    Text_data = Input$(LOF(F_Number), #F_Number)
    
    'Close the text file
    Close #F_Number
    
    'show the text data in message box
    MsgBox Text_data
    
End Sub

Sub ReadTextFile_Method2()

    'Declare variables
    Dim Stream_obj As Object
    Dim Text_data As String
    Dim F_Path As String
    
    'Set the file path
    F_Path = "C:\Users\USER\Downloads\SOLVED EXCEL\A Fairy Song.txt"
    
    'Create an ADODB.Stream object
    Set Stream_obj = CreateObject("ADODB.Stream")
    
    'Configure the stream to read text
    Stream_obj.Type = 2 'Text stream type
    Stream_obj.Charset = "UTF-8"
    
    'Open the text file
    Stream_obj.Open
    Stream_obj.LoadFromFile F_Path
    
    'Read the entire text file
    Text_data = Stream_obj.ReadText
    
    'Close the text file
    Stream_obj.Close
    
    'Clean up
    Set Stream_obj = Nothing
    
    'Dispay the text
    MsgBox Text_data
    
End Sub

Sub ReadTextFile_Method3()

    'Declare variables
    Dim F_sys_obj As Object
    Dim T_Stream As Object
    Dim Text_data As String
    Dim F_Path As String
    
    'Set the file path
    F_Path = "C:\Users\USER\Downloads\SOLVED EXCEL\A Fairy Song.txt"
    
    'Create a FileSystemObject
    Set F_sys_obj = CreateObject("Scripting.FileSystemObject")
    
    'Open the text file
    Set T_Stream = F_sys_obj.OpenTextFile(F_Path, 1, False)
    
    'Read the entire text file
    Text_data = T_Stream.ReadAll
    
    'Close the text file
    T_Stream.Close
    
    'Clean up
    Set F_sys_obj = Nothing
    Set T_Stream = Nothing
    
    'Do something with the text
    MsgBox Text_data
    
End Sub

Sub OpenTextFileAndCopyToExcel_SaveSheet()
    
    'Declare variables
    Dim F_Path As String
    Dim F_Number As Integer
    Dim Each_Line As String
    Dim Row_Number As Long
    
    'Set the file path
    F_Path = "C:\Users\USER\Downloads\SOLVED EXCEL\A Fairy Song.txt"
    
    'Open the text file
    F_Number = FreeFile()
    Open F_Path For Input As #F_Number
    
    'Copy the data to Excel
    Row_Number = 1
    Do While Not EOF(F_Number)
        'Read a line of text from the file
        Line Input #F_Number, Each_Line
        
        'Copy the text to the Excel sheet
        Range("A" & Row_Number).Value = Each_Line
        
        'Increment the row number
        Row_Number = Row_Number + 1
    Loop
    
    'Close the text file
    Close #F_Number
    
    'Save the Excel sheet
    ThisWorkbook.Save
    
End Sub

Sub CompareExecutionTime()
    
    ' Declare variables
    Dim StartTime As Double
    Dim EndTime As Double
    Dim Text As String
    Dim F_Path As String
    Dim F_Number As Integer
    
     ' Set the file path
    F_Path = "C:\Users\USER\Downloads\SOLVED EXCEL\sample-large-text-file.txt"
    
    
    ' Method 1: Input function with binary mode
    StartTime = Timer
    F_Number = FreeFile()
    Open F_Path For Binary As #F_Number
    Text = Space$(LOF(F_Number))
    Get #F_Number, , Text
    Close #F_Number
    EndTime = Timer
    'As execution time is very small to count, so we multiplied by 1000
    MsgBox "Method 1 took " & Format((EndTime - StartTime) * 1000, "#0.0000") & " milliseconds to execute."
    
    ' Method 2: Input function with text mode
    
    
    StartTime = Timer
    F_Number = FreeFile()
    Open F_Path For Input As #F_Number
    Text = Input$(LOF(F_Number), #F_Number)
    Close #F_Number
    EndTime = Timer
    'As execution time is very small to count, so we multiplied by 1000
    MsgBox "Method 2 took " & Format((EndTime - StartTime) * 1000, "#0.0000") & " milliseconds to execute."
    
   ' Method 3: Read file line by line
   
    StartTime = Timer
    F_Number = FreeFile()
    Open F_Path For Input As #F_Number
    Do While Not EOF(F_Number)
        Line Input #F_Number, Text
    Loop
    Close #F_Number
    EndTime = Timer
    'As execution time is very small to count, so we multiplied by 1000
    MsgBox "Method 3 took " & Format((EndTime - StartTime) * 1000, "#0.0000") & " milliseconds to execute."
    
End Sub