CODEWITHSUNDEEP
vb.netexcelserial-port
Low69SquareBack
Low69SquareBack

Reputation: 15

Serial data to Excel spread sheet

I need help, I accepted a project with the thought that it should be straight forward, but now I am stuck. It’s a data logging program that needs to receive a serial string from a com port, process the string, then put that information into an Excel spread sheet. So far I have the serial port working using which is working.

Private Sub SerialPort_DataReceived(ByVal sender As Object, ByVal e As System.IO.Ports.SerialDataReceivedEventArgs) Handles serPort.DataReceived
        Dim str As String = serPort.ReadExisting()
        Invoke(myDelegate, str)
    End Sub

    Sub procString(input As String)
        bufString = input
        If InStr(bufString, "|") Then
            tempString.Add(bufString.Split(New Char() {ChrW(2)}))

        End If
    End Sub

Which brings me to my first question, how to best handle the incoming string. Currently I am splitting the string into a List (Of String), I am assuming that is the best method to handle the strings prior to sending to excel. Please correct me if I am wrong. Second question is how to best process this string, then put that information into Excel. I have tried using a timer to get the data into Excel, but it is not working 

Private Sub Timer1_Tick(ByVal sender As System.Object,ByVal e As System.EventArgs) Handles Timer1.Tick


        UsedRange = xlWorkSheet.UsedRange
        RowRange = UsedRange.Rows

        Dim CurrentRow As Int32 = RowRange.Count + 1
        xlCells = xlWorkSheet.Cells


        xlCells(CurrentRow, 1) = tempString(0).ToString()  ' Start of text
        xlCells(CurrentRow, 2) = tempString(1).ToString()  ' Unit Address
        xlCells(CurrentRow, 3) = tempString(2).ToString()  ' Product SKU

I am sure there is an easier way of getting this data into Excel, I am just not sure how best to go about it. Any insight on how best to get this done would be greatly appreciated. The incoming string is a pipe delimited and has a STX and ETX.

Upvotes: 1

Views: 632

Answers (1)

Steve
Steve

Reputation: 5545

To answer the first question, what you are doing works. I assume you are trying to not lock up the serial port while you add the information to the excel file? If not, you could do it all on the same thread without doing all this. But it is good you are doing it this way.

I would like to suggest a couple things

1: When you are changing an object in multiple threads, you have to worry about thread safety. To avoid issues with that, you should use a SyncLock. They are pretty easy to work with.

2: You seem to be splitting the string and putting it in an array, but when another comes in, are you overwriting or adding to the list? I cannot see from the code posted. I would suggest, since it might be possible that DataReceived could fire 2 or more times for only one Tick of your timer, you put the full string into the collection (ADD) and then in the timer, you parse it and remove it from the collection.

For example, the DataRecieved might do this:

SyncLock MyLock
    tempString.add(bufString)
End SyncLock

And your timer might do this:

SyncLock MyLock
    For Each s As String In tempString
        Dim sAry As String() = bufString.Split(New Char() {ChrW(2)})
        For i As Int16 = 1 To sAry.Length
            xlCells(CurrentRow, i).Value = sAry(i - 1).ToString()
        Next
    Next
    tempString.Clear()
End SyncLock

To answer the second question, your issue is likely because you are not setting the proper property in excel. While you are doing this:

xlCells(CurrentRow, 1) = ?

You should be doing this:

xlCells(CurrentRow, 1).Value = ?

Upvotes: 1

Related Questions