Visual Basic

Imports System.IO
Public Class Form1

    Dim TheFile As Microsoft.Office.Interop.Excel.Workbook
    Dim SaveFile As StreamWriter
    Dim Files() As String
    '  Dim filebrowser As New FolderBrowserDialog
    Dim intAccountNumber As Integer
    Dim decTotal As Decimal


    Private Sub btnexit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnexit.Click
        'Closes the application
        Me.Close()
    End Sub

    Private Sub btnshowthefile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnshowthefile.Click
        'Shows the spreadsheet
        TheFile = GetObject("G:\data\dr.xls")
        TheFile.Application.Visible = True
        TheFile.Windows(1).Visible = True
        TheFile.Application.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized

    End Sub

    Private Sub btnbrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnbrowse.Click


        FolderBrowserDialog1.Description = "Select location of file"
        FolderBrowserDialog1.ShowNewFolderButton = False
        FolderBrowserDialog1.RootFolder = Environment.SpecialFolder.MyComputer

        'FolderBrowserDialog1.Filter = "Excel files (*.xls)|*.xls"
        FolderBrowserDialog1.ShowDialog()
        Dim fpath As String = FolderBrowserDialog1.SelectedPath()
        Dim tfiles() As String
        Dim files() As String
        Dim count As Integer
        tfiles = Directory.GetFiles(fpath)
        files = tfiles
        For count = 0 To files.Length() - 1
            ListBox1.Items.Add(files(count))
        Next


        SaveFile = File.CreateText("G:\data\dr.xls")
    End Sub

    Function findAccountNumber()

        'To find the account number
        Try

            If TheFile.Worksheets("sheet1").cells("9", "f").value = ("State") Then
                intAccountNumber = TheFile.Worksheets("sheet1").cells("10", "f").value
            Else
                intAccountNumber = TheFile.Worksheets("sheet1").cells("10", "g").value
            End If

        Catch ex As Exception
            intAccountNumber = 0
        End Try
        Return intAccountNumber

    End Function

    Function findthetotal()

        Dim intTotalSearch As Integer = 0

        'find the total
        
        Try
            For intTotalSearch = 0 To 90
                If TheFile.Worksheets("sheet1").cells(intTotalSearch, "g").value = ("Total") Then
                    decTotal = TheFile.Worksheets("sheet1").cells(intTotalSearch, "h").value
                Else
                    MessageBox.Show("Wrong")
                End If
            Next intTotalSearch
        Catch ex As Exception
            decTotal = 0
        End Try
        Return decTotal
    End Function

    Private Sub btnaccountinformation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnaccountinformation.Click

        Try
            For Each item As String In Files

                findAccountNumber()
                findthetotal()

                'ListBox2.Items.Add("Account # " & intAccountNumber & " Total " & decTotal)

                ListBox2.Items.Add("Account # " & findAccountNumber() & " Total " & findthetotal())

                'To append
                TheFile.AppendFile(intAccountNumber & vbTab & decTotal & vbCrLf)
            Next
        Catch ex As Exception

        End Try


    End Sub