Imports system.IO
 
Imports Microsoft.Office.Core
 
Public Class Form1
    Private Filename As String
    Dim sr As StreamReader
 
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Try
            With OpenFileDialog1
                'OpenFileDialog1
                .Filter = "Test files (prept*)|prept*|" & "All files|*.*"
                If .ShowDialog() = DialogResult.OK Then
                    Filename = .FileName
                    sr = New StreamReader(.OpenFile)
                    'using streamreader to read the opened text file
                    RichTextBox1.Text = sr.ReadToEnd()
 
                    'displaying text from streamreader in richtextbox
                End If
            End With
        Catch es As Exception
            MessageBox.Show(es.Message)
        Finally
            If Not (sr Is Nothing) Then
                sr.Close()
            End If
        End Try
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
    End Sub
 
    Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBox1.TextChanged
        RichTextBox1.Text = Replace(RichTextBox1.Text, Chr(35), String.Empty)
 
        For Each line As String In RichTextBox1.Lines
            Dim equalsPos As Integer = line.IndexOf("=") + 1
             If equalsPos = 0 Then equalsPos = line.IndexOf(":") + 1
            RichTextBox2.AppendText(line.Substring(equalsPos, line.Length - equalsPos).Trim() & vbCrLf)
            Dim txt As String
            txt = RichTextBox2.Text
        Next
 End Sub
 
 
    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oRng As Excel.Range
 
        ' Start Excel and get Application object.
        oXL = CreateObject("Excel.Application")
        oXL.Visible = False
 
        ' Get a new workbook.
        oWB = oXL.Workbooks.Add
        oSheet = oWB.ActiveSheet
        Dim first As String
        first = DateString
 
 
        ' Add data cell by cell.
        oSheet.Cells(2, 4).Value = "TEST REPORT " &  first
        With oSheet.Range("B2", "D2")
            .Font.Underline = True
            .Font.Name = "Times New Roman"
            .Font.Bold = True
            .Font.Size = 15
            .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
        End With
        oSheet.Range("c2", "l2").Merge()
 
      oSheet.Cells(2, 1).Value = " BULK Excel Report" 
        oSheet.Range("a2", "b2").Merge()
        With oSheet.Range("a2", "b2")
 
            .Font.Bold = True
            .Font.Name = "Times New Roman"
            .Font.Size = 10
            .VerticalAlignment = Excel.XlVAlign.xlVAlignTop
        End With
        '----------------------------------------------------------------
 
 
        oSheet.Range("a1", "k60").Interior.ColorIndex = 2 'white
        oSheet.Name = "Report" & (RichTextBox2.Lines(6))
        '----------------------------------------------------------------
 
        oXL.Visible = True
 
   oRng = Nothing
        oSheet = Nothing
        oWB = Nothing
        oXL.Quit()
        oXL = Nothing
 RichTextBox1.Text = vbNullString
        RichTextBox2.Text = vbNullString
        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub