I have decided to post a more detailed example of a direct serial link between the Nerdkit and an excel sheet. I do not feel qualified to give an explanation of the possible advantages and disadvantages of this type of Nerdkit/PC link. I have used this type of two way serial link many of times to achieve precise PC control of up to 3 stepper motors at once and relay circuit control. The inherent power of excel makes data processing charting and other detailed analyses quick and relatively simple on data received from the Nerdkit. In fact this type of serial connection is just about the only system I use to set up a link between the PC and the Nerdkit, It has been fast enough for my needs.

First set up the VBA communication modules. Follow the detailed instructions at.

link to VBA serial communication setup

You should not be concerned about the VBA code. In most cases it will not be necessary to have an understanding of any of that code. Once you have the modules in place they work in the background you don’t even know that they are there. You will likely have to have a basic understanding of basic VBA coding to write modules to make efficient use of the connection.

I would recommend one simple addition to the existing modules to help you confirm that the serial connection is open and ready to send/receive data.

Place the one additional line of code in module that opens the com port.

     Private Sub CommandButton1_Click()
             Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
             Dim lngStatus As Long

            intPortID = 4

           ' Open COM port
    lngStatus = CommOpen(intPortID, "COM" & CStr(intPortID), _
        "baud=115200 parity=N data=8 stop=1")

     Worksheets("Sheet1").Cells(4, 1).Value = lngStatus

     End Sub

I have found that it is very useful to have an on the sheet indication of the state of the serial connection. Often after working in the VBA editor or using cmd.com to change coding on the micro it is necessity to re-establish the serial connection to your Excel sheet. In order to see what the current state of the connection is, click the open connection button on “sheet1” of the excel workbook. The number displayed in cell A4 will indicate if the com connection is actually open. You are looking for a 0 (zero) which should mean that excel is ready to read/write on the serial. A -1 can usually be corrected by clicking the close button then clicking the open button again. A 5 indicates that excel thinks the com port is in use by someone else and can be corrected by unplugging the Nerdkit USB cable from the PC for a second.

After you have established a viable serial connection between the PC and the nerd kit you may wish to try it out. I am posting some code to set up a simple two way data swap between the Nerdkit and the PC. I wanted to post an example that would clearly demonstrate that the connection is bidirectional and that the data from the Nerdkit is ending up in the excel cells that we intended. Using VBA to place incoming data in appropriate cells and the built in features of excel almost anything is possible. From this example I hope that it will be apparent how powerful and useful this type of connection could be.

Place this VBA code in a module that can be triggered from a control button on “sheet1’. Ether make a new control button or use your existing read button.

     Private Sub CommandButton3_Click() 
      Dim intPortID As Integer ' Ex. 1, 2, 3, 4 for COM1 - COM4
      Dim lngStatus As Long                            '  indicates if operation was successful
       Dim strData   As String                          ' data recieved from serial connection
      Dim strData2   As String                          'data held for sheet
      Dim row_top As Integer
     Dim col_left As Integer
     Dim row_number As Integer
     Dim col_number As Integer
     Dim row_count As Integer
     Dim col_count As Integer
     Dim i As Integer

     intPortID = 4                                     'set com port id
     row_top = 12
     col_left = 2

     Worksheets("Sheet1").Cells(6, 2).Value = " Enter table size that you wish to fill with    data from Nerdkit"
      Worksheets("Sheet1").Cells(7, 3).Value = "Enter the number or rows in table into cell A7"
      Worksheets("Sheet1").Cells(8, 3).Value = "Enter the number of columns in table into cell A8"

     While ((Worksheets("Sheet1").Cells(7, 1).Value * Worksheets("Sheet1").Cells(8, 1).Value) < 2)
        Worksheets("Sheet1").Cells(10, 4).Value = "Enter data table size - click start button"
        Exit Sub

     Wend

     row_number = Worksheets("Sheet1").Cells(7, 1).Value
     col_number = Worksheets("Sheet1").Cells(8, 1).Value
     For i = 1 To 10
    lngStatus = CommRead(intPortID, strData, 6)    'clear the buffer

         Next i

        Worksheets("Sheet1").Cells(10, 4).Value = "Waiting for data from NerdKit"
        lngStatus = CommWrite(intPortID, "s")              'send a charitor "s" to micro to  indicate ready to recieve serial data

       While (Len(strData2) < 6)                          ' do we have 6 character to work with
        lngStatus = CommRead(intPortID, strData, 6)     'read 6 character from com 4 store in strData
        strData2 = strData2 & strData                   'append serial read to previous read data
       Wend                                                'end while- we do have at least 6 char
       Worksheets("Sheet1").Cells(10, 4).Value = "Recieveing data from NerdKit"

       For col_count = col_left To (col_left + col_number - 1) ' row selection on sheet
        For row_count = row_top To (row_top + row_number - 1) 'col selection on sheet
            If (Left(strData2, 6) = "  stop") Then
                Worksheets("Sheet1").Cells(10, 4).Value = "Data transmitions was stopped by the NerdKit- table size to large- resize table "
                Exit Sub
            End If
             Worksheets("Sheet1").Cells(row_count, col_count).Value = Val(Left(strData2, 6)) 'val of the left 6 char to sheet
               strData2 = Right(strData2, (Len(strData2) - 6)) ' remove left 6 char from string
            While (Len(strData2) < 6)                   ' do we have 6 char to work with
               lngStatus = CommRead(intPortID, strData, 6) 'read 6 character from com 4 store in strData
               strData2 = strData2 & strData               'append serial read to previous read data
            Wend

        Next row_count                                      'next row
    Next col_count                                          'next col
       lngStatus = CommWrite(intPortID, "t")

         Worksheets("Sheet1").Cells(10, 4).Value = "Data transmit ion completed table is full"

       End Sub

Use this code on the Nerdkit.

 #define F_CPU 14745600
 #include <avr/io.h>

     // #include <avr/pgmspace.h>  // needed for PSTR with printf_P
     #include "../libnerdkits/uart.h"
     #include <avr/interrupt.h>
     #include <avr/pgmspace.h>
     #include <util/delay.h>
     #include <inttypes.h>
     #include <stdlib.h>
     #include <string.h>
     #include "../libnerdkits/uart.h"

     #include "../libnerdkits/delay.h"
     #include "../libnerdkits/lcd.h"

     int main() {

     char incoming;

     uart_init();
     FILE uart_stream = FDEV_SETUP_STREAM(uart_putchar, uart_getchar, _FDEV_SETUP_RW);
     stdin = stdout = &uart_stream;
     lcd_init();
     FILE lcd_stream = FDEV_SETUP_STREAM(lcd_putchar, 0, _FDEV_SETUP_WRITE);

     uint16_t  i;

     while(1) {

     lcd_line_one();
     lcd_write_string(PSTR("  NerdKit - Excel  "));
     lcd_line_two();
     lcd_write_string(PSTR(" Waiting for start "));
     lcd_line_three();
     lcd_write_string(PSTR("  code from Excel  "));

     incoming= uart_read(); // read data from uart

     while (incoming !=  115) {   // is the incoming data a "s"
        incoming= uart_read();  // read data from uart 
     }                        // end while - when excel is ready for data

     lcd_line_two();
     lcd_write_string(PSTR(“Transmiting data "));
     lcd_line_three();
     lcd_write_string(PSTR("                   "));

     while(uart_char_is_waiting()){ // clear the uart 
        incoming = uart_read();
        }

     for (i=1;i<=25000;i++) {                    //count to 25000
        while(uart_char_is_waiting()){         //check for new char
            incoming = uart_read();

            while(incoming == 116){                  //is new char a "t"
                lcd_line_two();
                lcd_write_string(PSTR("Excel table is full"));
                lcd_line_three();
                fprintf_P(&lcd_stream, PSTR("last value %6d"),i);// print to LCD
                lcd_line_four();
                lcd_write_string(PSTR("power down to start"));
                while(1){                            //table is full do nothing just show message
                    }
            }                                     //while char is a "t"

        }                                         //while there is a char waiting

        lcd_line_three();
        fprintf_P(&lcd_stream, PSTR("sending %6d "),i);// print to LCD
        printf_P(PSTR("%6d"), i);               //send count number to serial

     }//next i

     lcd_line_two();
     lcd_write_string(PSTR(" Excel table large "));
     lcd_line_three();
     fprintf_P(&lcd_stream, PSTR("Tx stopped at%6d"),i);// print to LCD
     printf_P(PSTR("  stop"));               //send count number to serial
     lcd_line_four();
     lcd_write_string(PSTR("power down to start"));

     while (1){
            }                                 //do nothing just show mmessage
     }

     return 0;
     }

Connect Nerdkit USB cable to the PC. Make sure the com port matches up with the VBA code(com4). Power up the Nerdkit. Click the open port button on the excel sheet. Check that you have a 0 in cell A4. Click on the command button that will run the VBA module listed here. Enter the desired table size for the first test (cells A7 and A8), don’t forget to press enter after change. Click on the command button to run the VBA code and hopefully receive the data from the Nerdkit.

This setup is working on my system. There is a small glitch that occurs when my VBA module ends I am looking into the exact nature of that odd result.

I would be very interested in knowing if anyone gets this working.

Darryl

I noticed the link I posted does not work, I will try again.

link to Serial port communication in excel

Darryl

Thanks for the link to SERIAL PORT COMM. This may be the basis I am looking for to merge data from my TRACTOR PULL SLED to a spread sheet for each puller..... via a radio link from the sled..
Jim

I should also mention that you will have to change the baud rate in the excel VBA code to match up with the Nerdfkit baud rate. The baud rate is set in the open port sub listed above. You must simply change the baud=9600 to baud=115200.

Darryl