Click here to Skip to main content
15,877,103 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
i am acquiring data from serial port and displaying it on a screen using text boxes,i want to log these data to excel file at a rate of 4000 values per second.On first click of a export button data logging should start and log the data for every second on the second click it should stop logging.how to achieve it.
the excel file format is as below

Temp1 Temp2 C1 C2 C3.............C3999 C4000

for ex:
For I = 3 To 4002
Application.DoEvents()
h4kworksheet.Cells(1, I) = "C" & I - 2
h4kworksheet.Cells(2, 1) = temp1_horizontal
h4kworksheet.Cells(2, 2) = temp2_horizontal
h4kworksheet.Cells(2, I) = rf_data_horizontal

h4ksave.Value = I
Next

here in the above code temp1_horizontal value should be placed at 1 coloumn,temp2_horizontal should be placed at 2 coloumn and rf_data_horizontal should be placed from c1 to c4000
if temp1_horizontal=20
temp2_horizontal=30
rf_data_horizontal=2.5

the values should be placed as follows in excel
Temp1 Temp2 C1 C2 C3.............C3999 C4000
20 30 2.5 2.5 2.5 2.5 2.5
20 30 2.5 2.5 2.5 2.5 2.5

What I have tried:

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Application.DoEvents()
        bt_count2 = bt_count2 + 1
        If bt_count2 <= 1 Then
            h4klog.FileName = DateTime.Now.ToString("ddMMyyyy") & "_" & DateTime.Now.ToString("HHmmss") & "h4k.xls"
            If h4klog.ShowDialog() = Windows.Forms.DialogResult.OK Then
                h4klogfile_name = h4klog.FileName
                nc = 0
                nr = 2
                h4kworkbook = xlapp1.Workbooks.Add(1)
                h4kworksheet = h4kworkbook.Sheets("sheet1")
                h4kworksheet.Cells(1, 1) = "TR1"
                h4kworksheet.Cells(1, 2) = "TR2"

                file1_count = 0
                Button2.Text = "STOP"
                Button2.ForeColor = Color.Red
                enable_h4k = True
            End If
        End If
        If bt_count2 >= 2 Then
            Button2.Enabled = False
            Button2.Text = "START"
            Button2.ForeColor = Color.Green
            bt_count2 = 0
            If enable_h4k = True Then
                enable_h4k = False
                h4ksave.Visible = True
                lbh4ksave_status.Visible = True
                For I = 3 To 4002
                    Application.DoEvents()
                    h4kworksheet.Cells(1, I) = "C" & I - 2
                    h4kworksheet.Cells(2, 1) = temp1_horizontal
                    h4kworksheet.Cells(2, 2) = temp2_horizontal
                    h4kworksheet.Cells(2, I) = rf_data_horizontal

                    h4ksave.Value = I
                Next

                h4ksave.Visible = False
                For t = 1 To 10
                    lbh4ksave.Visible = True
                    delay1(1)
                    lbh4ksave.Visible = False
                    delay1(1)
                Next
                lbh4ksave_status.Visible = False
                Button2.Enabled = True
                h4kworksheet.Range("A1:EWX1").HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter
                h4kworkbook.SaveAs(h4klogfile_name)
                h4kworkbook.Close()
                xlapp1.Quit()
            End If
            MsgBox("Done")
        End If
Posted
Updated 9-Jan-17 0:34am
Comments
Patrice T 9-Jan-17 12:49pm    
What is the speed of your serial link ?
What is the size of the value you read ?

1 solution

If you need a statical analysis of the data then use your VB program to collect the data from the serial port (store them in memory). At the end of such 'data-collection' process create the sheet with the data and enjoy the power of Excel.

There are caveats, of course. Your VB application must be able to keep up with the transmission rate on the serial port and it should have enough memory to temporarily store the wanted amount of data.
 
Share this answer
 
Comments
Garth J Lancaster 9-Jan-17 6:56am    
yup - its what I call an impedance mismatch issue - something producing faster than a directly-coupled consumer can handle it - I almost always opt for 'decoupling' - holding the data in a 'memory buffer'/queue and using a second process to handle the stats and in this case writing to Excel. Depending on what stats the op wants, they could be done 'on the fly' ie a 'moving average' and standard deviation etc
Member 12659926 9-Jan-17 7:03am    
Thanks for reply,
can u give any example of how to do it

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900