Reading in Excel data

Hey, I have a bunch of old data in excel files. I learned VB a loonggg time ago and have forgotten a good bit of it.
I have a form that pops up asking to browse for an excel file.
Next you select an increment in the time stamp for it to scan through (example: if you put 5 it will pull data from every 5 seconds)
It then reads through the file and pulls the data out and presents it in a form and also saves it as a .txt.

How could I get it to also print out information that is missing?
Say it pulls out 0,5,15,20. How could I get it to say that 10 is missing from the document?

I will pay well for help here if it becomes too much.

Code:

Imports System.IO

Imports System.Text

 

Public Class Form1

    Public Fname As String = "C:\" 'Set Default FileName

    Public Sname As String = "C:\PMIResults.txt" 'Set default save location

    Public secondcheck As Boolean 'Variable to determine if the Multiple Check box is checked

    Public browsed As Boolean = False

    Public numberOfPmi As Integer

 

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Browse button gives the capability to choose a file other than the default to input into the form

        OpenFileDialog1.ShowDialog()

        Fname = OpenFileDialog1.FileName

        Label1.Text = Fname

        browsed = True

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click

        'Browse button gives the capability to choose a file name other than the default to save the form into

        SaveFileDialog1.ShowDialog()

        Sname = SaveFileDialog1.FileName

        Label3.Text = Sname

    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click 'Click of the Run button

        Dim data(), temp1, myfilename_temp() As String

        Dim check_time As Decimal

        Dim run_count As Integer = 1

        numberOfPmi = 0

        'Opens file dialog if Filename is not the default

95:     If browsed = False Then

            Dim didwork As Integer

            didwork = OpenFileDialog1.ShowDialog()

            Fname = OpenFileDialog1.FileName

            If didwork = DialogResult.Cancel Then

                GoTo 99

            End If

        End If

        

        check_time = TextBox1.Text 'Set the first interval time to check

 

        Dim fileReader As System.IO.StreamReader

        Dim cols, rows, i, x, end_line, start_line As Integer

 

        fileReader = File.OpenText(Fname) 'Open file temporarily to determine the number of columns

        temp1 = fileReader.ReadLine

        data = Split(temp1, ControlChars.Tab)

        cols = data.LongLength

        rows = 0

 

        Do 'Counts the number of rows and finds the start and end points of the data

            temp1 = fileReader.ReadLine

            rows = rows + 1

 

            If temp1 = "#End_Data" Then 'Finds the end of the data

                end_line = rows - 1

                Exit Do

                'Exits the loop if it finds #End_Data

            End If

 

            If temp1 = "#Start_Data" Then 'Finds the start of the data

                start_line = rows + 1

            End If

 

        Loop Until temp1 Is Nothing

 

63:     fileReader.Close()

        fileReader = File.OpenText(Fname) 'Reopens the file

 

        Dim table As String

        Dim Reader As New System.IO.StreamReader(Fname)

 

        table = Reader.ReadToEnd 'Reads the file

        Reader.Close()

        data = table.Split(ControlChars.Tab)

        fileReader.Close() 'Closes the file

        x = 0

 

        Dim stringmatch As Boolean

        Dim time((end_line - start_line)) As String

 

        For i = 0 To (data.Length - 1) 'Searches for data in the the file with the yyyy:ddd:hh:mm:ss format

            stringmatch = data(i) Like "####:###:##:##:##"

            If stringmatch = True Then

                time(x) = data(i) 'If the format is matched, store the data in the time variable

                x = x + 1

            End If

        Next

 

        Dim time1 As String

        Dim years1, days1, hours1, minutes1, seconds1, years2, days2, hours2, minutes2, seconds2 As String

        time1 = ""

 

        For i = 0 To time.Length - 1 'Loop concatenates the time variable into a string

            time1 = time1 & ":" & time(i)

        Next

 

        Dim gap_start As String

        Dim gap_time

        Dim gap_end

        Dim myfilename = Sname

        Dim MyTXTWriter As System.IO.StreamWriter

 

42:     MyTXTWriter = My.Computer.FileSystem.OpenTextFileWriter(myfilename, False)

        MyTXTWriter.WriteLine("Permanently Missing Interval (PMI) Within Given Data File" & vbCrLf)

        MyTXTWriter.WriteLine("Start:" & vbTab & vbTab & vbTab & "Length:" & vbTab & vbTab & "End:" & vbCrLf)

 

        Dim hms As TimeSpan

        Dim h, m, s As String

 

        For i = 0 To (time.Length - 2) 'This loop separates the string into individual pieces to compare for gaps

            years1 = (time1(18 * i + 1) & time1(18 * i + 2) & time1(18 * i + 3) & time1(18 * i + 4))

            days1 = (time1(18 * i + 6) & time1(18 * i + 7) & time1(18 * i + 8))

            hours1 = (time1(18 * i + 10) & time1(18 * i + 11))

            minutes1 = (time1(18 * i + 13) & time1(18 * i + 14))

            seconds1 = (time1(18 * i + 16) & time1(18 * i + 17))

            years2 = (time1(18 * (i + 1) + 1) & time1(18 * (i + 1) + 2) & time1(18 * (i + 1) + 3) & time1(18 * (i + 1) + 4))

            days2 = (time1(18 * (i + 1) + 6) & time1(18 * (i + 1) + 7) & time1(18 * (i + 1) + 8))

            hours2 = (time1(18 * (i + 1) + 10) & time1(18 * (i + 1) + 11))

            minutes2 = (time1(18 * (i + 1) + 13) & time1(18 * (i + 1) + 14))

            seconds2 = (time1(18 * (i + 1) + 16) & time1(18 * (i + 1) + 17))

            'This If loop does the actual comparison and uses a new self indexed variable to keep track of how many PMIs it encounters

 

            If (Val(hours2) * 3600 + Val(minutes2) * 60 + Val(seconds2)) - (Val(hours1) * 3600 + Val(minutes1) * 60 + Val(seconds1)) >= check_time Then

                gap_start = ((years1) & ":" & (days1) & ":" & (hours1) & ":" & (minutes1) & ":" & (seconds1))

                gap_end = ((years2) & ":" & (days2) & ":" & (hours2) & ":" & (minutes2) & ":" & (seconds2))

                gap_time = ((Val(hours2) * 3600 + Val(minutes2) * 60 + Val(seconds2)) - (Val(hours1) * 3600 + Val(minutes1) * 60 + Val(seconds1)))

                hms = TimeSpan.FromSeconds(gap_time)

                h = hms.Hours.ToString

                m = hms.Minutes.ToString

                s = hms.Seconds.ToString

 

                If h.Length = 1 Then 'Takes gap time to hh:mm:ss format

                    h = "0" & h

                End If

 

                If m.Length = 1 Then

                    m = "0" & m

                End If

 

                If s.Length = 1 Then

                    s = "0" & s

                End If

 

                gap_time = h & ":" & m & ":" & s

                Results.listviewload(gap_start, gap_time, gap_end) 'Sends the results to the listview in the results window

                numberOfPmi = numberOfPmi + 1

                MyTXTWriter.WriteLine(gap_start & vbTab & gap_time & vbTab & gap_end & vbCrLf) 'Writes the line of results to the text file

 

            End If

        Next

 

        MyTXTWriter.Close()

 

        If secondcheck = True And run_count = 1 Then 'Index run_count for use in a switching statement to determine the command path

            myfilename_temp = Split(myfilename, ".", 2)

            myfilename = myfilename_temp(0) & "2.txt"

            Results.listviewload("Second Check Results", " ", " ") ' Adds a line in the listview to seperate the results

            check_time = TextBox3.Text 'Loads the second check time into the variable

            run_count = run_count + 1

            GoTo 42

        End If

 

        Button3.Visible = True 'Turn on the Reset button if the fields are used

        Beep() 'Lets you know the program is done

 

        'Signal check is complete

        If numberOfPmi = 0 Then

            Label5.Visible = True

        Else

            Button5.Visible = True

        End If

 

99:

    End Sub

    Private Sub CheckBox1_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CheckBox1.CheckedChanged

 

        If CheckBox1.CheckState = 1 Then 'If the checkbox is checked, turn on the second check time interface

            Label8.Visible = True

            TextBox3.Visible = True

            secondcheck = True

            Label13.Visible = True

        Else

            Label8.Visible = False

            TextBox3.Visible = False

            Label13.Visible = False

            secondcheck = False

        End If

 

    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

        Button3.Visible = False 'Turn off the reset button until the program is rerun

        TextBox1.Text = "20"

        TextBox3.Text = "120"

        Fname = "C:\" 'Reset default filename

        Label1.Text = "C:\" 'Default

        CheckBox1.CheckState = 0 'Uncheck the box

        Label5.Visible = False

        Button5.Visible = False

        browsed = False

        Me.Height = 182 'Set the window size back to normal

    End Sub

    Public Sub PMIChecker_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.Height = 182 'Set the window size to the smaller value on load, until results are displayed

    End Sub

 

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

        Results.Visible = True 'Opens the results window

    End Sub

End Class

 

If this is explicitly a paid job, post in the Software Market instead.
What you'll need to do is looking into Office interop, but the tricky part is that it's version specific - So if you use the libraries from the 2003 Office interop (I've done a fair bit of work with that) they won't work with 2007 documents, etc. You should have the libraries for it already installed since I'm pretty sure they ship with whatever version of Office you've got. The interop has been shipped with Office since Office 97 I believe, but if you go as far back as that and the 2000 suite you might be dealing with OLE/Win32 libraries. Unsure about how recently they've been using those, they could still be using them for all I know.

 

Not really a paid job, only if it is too difficult, I have some of it working, I just need someone to help me read data across cells, I will update with the code I have In the morning.

 

I learned from some good tutorials online, just search for a guide to the Office API for .NET and make sure you get the year right like I mentioned in my last post.

 

Updated with the code, I have it working now but I need a little more help.

What would I do to have it print out any information that is missing when it scans through the increments?

How would I get it to scan in information from multiple columns?

off topic-how do I make my forum avi a .gif? lol.