Datagrid real time update

[GSIV]

I have an Excel workbook that receives real time data (updates every second) via a DDE link. I am trying to import this data into a vb.net datagrid. I can do that ok, except that my datagrid is static and the only way I can update it is to reload the workbook and refresh the datagrid. Since my worbook contains 1600 real time cells, it takes too long... maybe 5-10 seconds to read the data. Can someone give me some ideas on how to achieve a real time update in my datagrid as well?

This is my present code; I am refreshing it with a timer control:

    Private Sub OpenDataGrid()
        Dim dt As New DataTable
        Dim dc As New DataColumn
        Dim dr As DataRow

        ' Set up column headers
        dt.Columns.Clear()
        dt.Columns.Add("Symbol", Type.GetType("System.String"))
        dt.Columns.Add("Date", Type.GetType("System.String"))
        dt.Columns.Add("Time", Type.GetType("System.String"))
        dt.Columns.Add("PrevClose", Type.GetType("System.String"))
        dt.Columns.Add("Open", Type.GetType("System.String"))
        dt.Columns.Add("High", Type.GetType("System.String"))
        dt.Columns.Add("Low", Type.GetType("System.String"))
        dt.Columns.Add("Last", Type.GetType("System.String"))
        dt.Columns.Add("OI", Type.GetType("System.String"))
        dt.Columns.Add("Change", Type.GetType("System.String"))
        dt.Columns.Add("TradeVol", Type.GetType("System.String"))
        dt.Columns.Add("DailyVol", Type.GetType("System.String"))
        dt.Columns.Add("BidSize", Type.GetType("System.String"))
        dt.Columns.Add("Bid", Type.GetType("System.String"))
        dt.Columns.Add("Ask", Type.GetType("System.String"))
        dt.Columns.Add("AskSize", Type.GetType("System.String"))

        ' Add Excel data to datatable row
            Dim RowIndex As Integer = 0
            Dim ColIndex As Integer = 0
            For RowIndex = 2 To 101 ' In the future, read upper boundary
            dr = dt.NewRow
            ColIndex += 1
            dr("Symbol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Date") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Time") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("PrevClose") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Open") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("High") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Low") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Last") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("OI") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Change") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("TradeVol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("DailyVol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("BidSize") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Bid") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("Ask") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex += 1
            dr("AskSize") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
            ColIndex = 0
            ' Add row to datatable
            dt.Rows.Add(dr)
            ' Bind datatable to the datagrid
            dg.DataSource = dt
        Next RowIndex

        ' Upon exit, release controls
        dt = Nothing
        dc = Nothing
        dr = Nothing

    End Sub

[GSIV]

Ok, I've changed to trying to do this with ADO.NET as a connected database. It loads much quicker, but I still can't figure out how to get the updates automatically. Somehow, I will need the reader to remain open or be triggered on change... something. Any ideas?

Dim conStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DDE1.xls;" & "Extended Properties=""Excel 8.0;HDR=YES"""
        Dim conn As New OleDbConnection(conStr)
        conn.Open()
        ' Create query command
        Dim sqlStr As New OleDbCommand("Select * From [Sheet1$]", conn)
        ' Open reader
        Dim Reader As OleDbDataReader = sqlStr.ExecuteReader()
        ' Define data table
        Dim dt As New DataTable
        Dim dc As New DataColumn
        Dim dr As DataRow
        ' Label column headers
        ' Count Columns
        Dim ColCount As Integer = Reader.FieldCount

        ' Clear existing column headers
        dt.Columns.Clear()
        Dim Counter As Integer
        ' Add new column headers to datagrid
        For Counter = 0 To ColCount - 1
            dt.Columns.Add(Reader.GetName(Counter))
        Next
        ' Assign column index
        ' Note: these names must match column headers in WB exactly
        Dim idxSymbol As Integer = Reader.GetOrdinal("Symbol")
        Dim idxLastDate As Integer = Reader.GetOrdinal("Date")
        Dim idxLastTime As Integer = Reader.GetOrdinal("Time")
        Dim idxPrevClose As Integer = Reader.GetOrdinal("PrevClose")
        Dim idxOpen As Integer = Reader.GetOrdinal("Open")
        Dim idxHigh As Integer = Reader.GetOrdinal("High")
        Dim idxLow As Integer = Reader.GetOrdinal("Low")
        Dim idxLast As Integer = Reader.GetOrdinal("Last")
        Dim idxOI As Integer = Reader.GetOrdinal("OI")
        Dim idxChange As Integer = Reader.GetOrdinal("Change")
        Dim idxTradeVol As Integer = Reader.GetOrdinal("TradeVol")
        Dim idxDailyVol As Integer = Reader.GetOrdinal("DailyVol")
        Dim idxBidSize As Integer = Reader.GetOrdinal("BidSize")
        Dim idxBid As Integer = Reader.GetOrdinal("Bid")
        Dim idxAsk As Integer = Reader.GetOrdinal("Ask")
        Dim idxAskSize As Integer = Reader.GetOrdinal("AskSize")
        'Do While ExitFlag = False

        ' Populate datagrid
        While Reader.Read
            dr = dt.NewRow
            dr(idxSymbol) = Reader.GetString(idxSymbol)
            dr(idxLastDate) = Format(Reader.GetDateTime(idxLastDate), "dd-MMM-yyyy")
            dr(idxLastTime) = Format(Reader.GetDateTime(idxLastTime), "H:mm:ss")
            dr(idxPrevClose) = Format(Reader.GetValue(idxPrevClose), "n2")
            dr(idxOpen) = Format(Reader.GetValue(idxOpen), "n2")
            dr(idxHigh) = Format(Reader.GetValue(idxHigh), "n2")
            dr(idxLow) = Format(Reader.GetValue(idxLow), "n2")
            dr(idxLast) = Format(Reader.GetValue(idxLast), "n2")
            dr(idxOI) = Format(Reader.GetValue(idxOI), "n0")
            dr(idxChange) = Format(Reader.GetValue(idxChange), "n2")
            dr(idxTradeVol) = Format(Reader.GetValue(idxTradeVol), "n0")
            dr(idxDailyVol) = Format(Reader.GetValue(idxDailyVol), "n0")
            dr(idxBidSize) = Format(Reader.GetValue(idxBidSize), "n0")
            dr(idxBid) = Format(Reader.GetValue(idxBid), "n2")
            dr(idxAsk) = Format(Reader.GetValue(idxAsk), "n2")
            dr(idxAskSize) = Format(Reader.GetValue(idxAskSize), "n0")
            ' Add data to table
            dt.Rows.Add(dr)
            ' Bind datatable to the datagrid
            dg.DataSource = dt
        End While
        'Loop

        ' Upon exit, release controls
        dt = Nothing
        dc = Nothing
        dr = Nothing
        ' Close connection
        conn.Close()

    End Sub