Author Topic: Arduino Serial Data to Excel  (Read 1459 times)

0 Members and 1 Guest are viewing this topic.

Offline metrologist

  • Super Contributor
  • ***
  • Posts: 1440
  • Country: 00
Arduino Serial Data to Excel
« on: July 13, 2017, 05:45:53 am »
I've come to using PuTTy to capture Arduino's serial data to a log file, and then I have a data connection from Excel to that log file for plotting.

(I am reading an analog voltage and printing "millis, value" to serial every 200 ms, it's a very simple program that works great for what I want)

Two problems:

The voltage I want to measure is likely to range between 25V to 45V. I thought of using a 10k potentiometer to create a voltage divider, but then I lose one bit of resolution (that would map to about 2.2V to 5V). I could ignore this, but it would be good to have the resolution, and I also wonder if there is a better range for the adc, such as 1V to 4V rather than say 2V to 5V?

Ever so often, there is a data glitch, it looks like the data in a cell gets truncated or shifted one cell to the right. I suspect this might be Excel accessing the log file (via auto refresh every 1 min) the same time as PuTTy and interrupting the communication to the log file. I could manually clean the data later or just access the log file after the test is complete, but it is nice to see the data plot update periodically.


I had initially hoped for a more direct path into Excel from the com port serial stream and have a more live update. Here are a couple VBA scripts I tried that would work once out of a thousand times or just capture some garbage or nothing. I gave up on them after days of hacking away at found code, but I post them for amusement.

Code: [Select]

Sub Send_and_Read()
'this one seemed to work when sending a "plain string, and sometime when casing a static integer variable cast as a string(intVar), for example...

  '--------------------------------------------------------
  cmnd$ = "Hello World"        'A string to send
  Dim i As Integer
  Dim timeout As Date
  i = 1
  '--------------------------------------------------------
  timeout = Now + TimeValue("00:00:20")
 
  Open "COM5" For Binary Access Read Write As #5
  cmnd$ = cmnd$ + Chr(13)      'add [CR] to command string
  'Put #1, , cmnd$              'write string to interface
  '--------------------------------------------------------
  While Now < timeout
 
  answer = ""                  'clear response string
  char = Input(3, #5)          'get first character
  While (char <> Chr(13))      'loop until [CR]
    If (char > Chr(47) And char < Chr(58)) Then   'was chr 31
      answer = answer + char   'add, if printable char
    Else
      ' Do what ever you like
    End If
    char = Input(255, #5)        'get the next character
  Wend

  '--------------------------------------------------------
  'Cells(1, 1) = answer         'put response in cell("A1")
  Cell = "B" & i
 Range(Cell).Value = Now
 Cell = "C" & i
 Range(Cell).Value = answer
 i = i + 1
 Application.Wait (Now + TimeValue("0:00:01"))
 
  Wend
  Close #5
 
End Sub


Public Sub Receive_COM5()
'This one seemed more reliable in capturing data, but was often 0 or 224.
   
    Dim timeout As Date
    Dim byte1 As Byte, chars As String, lineEnding As String
    Dim comPORT As Integer
    Dim i As Integer
    comPORT = 5
    i = 1
   
    'Define line ending character(s)
    'lineEnding = vbCrLf
    lineEnding = vbCr
   
    'Monitor COM port for 30 seconds
    timeout = Now + TimeValue("00:00:10")
   
   
    Close comPORT
   
    'Open COM5 port with baud rate 9600, No parity, 8 data bits, and 1 stop bit.
    'These are the default port settings in HyperTerminal.
    'In HyperTerminal, use Flow control = None.
   
    'Open "COM5:9600,N,8,1" For Random As #5 Len = 3
     Open "COM5:9600,N,8,1" For Binary Access Read Write As #5
   
    chars = ""
    While Now < timeout
   
        'Get 1 byte
        Get #comPORT, , byte1
        Debug.Print Now; IIf(Chr(byte1) < " ", "<" & byte1 & ">", Chr(byte1))
        chars = chars & Chr(byte1)
        Cell = "C" & i
        Range(Cell).Value = chars
        i = i + 1
       
        Application.Wait (Now + TimeValue("0:00:01"))
       
        If Right(chars, Len(lineEnding)) = lineEnding Then
            'Debug.Print "Line:" & Left(chars, Len(chars) - Len(lineEnding))
            chars = ""
        End If
       
        DoEvents
       
    Wend
   
    Close #comPORT

End Sub
« Last Edit: July 13, 2017, 05:59:34 am by metrologist »
 

Offline metrologist

  • Super Contributor
  • ***
  • Posts: 1440
  • Country: 00
Re: Arduino Serial Data to Excel
« Reply #1 on: July 13, 2017, 06:04:07 am »
BTW, in case I am not thinking something. I plan to connect the leads of a 36V battery pack while it is being charged via a wall charger. The arduino will be connected to a PC via USB, so there will be a ground connection to the PC and the wall charger. Will that create a magnesium glow and burn a hole to the center of the earth?
 

Offline metrologist

  • Super Contributor
  • ***
  • Posts: 1440
  • Country: 00
Re: Arduino Serial Data to Excel
« Reply #2 on: July 13, 2017, 06:30:28 am »
Here is the circuit I've constructed. It looks like there will be flames...  :-BROKE
 

Offline The Soulman

  • Frequent Contributor
  • **
  • Posts: 525
  • Country: nl
  • The sky is the limit!
Re: Arduino Serial Data to Excel
« Reply #3 on: July 13, 2017, 06:47:22 am »
Will that create a magnesium glow and burn a hole to the center of the earth?

It will likely stop at the concrete floor, so your carpet may suffer..
Nah, if the output of the charger is floating i.e. not connected to earth, you should be fine.
Some galvanic separation between the arduino and the pc would be best.
Why not use a (cheap) multimeter with serial output? safety and accuracy would be much improved.
You can still capture that (optical isolated) data with arduino and log it to a sd card.

If you decide to go that route make sure you get a multimeter with a fs9922 chip so I can use that program to.  ;)
 

Offline mikerj

  • Super Contributor
  • ***
  • Posts: 1662
  • Country: gb
Re: Arduino Serial Data to Excel
« Reply #4 on: July 13, 2017, 08:17:09 am »
If you have a 32bit version of Excel it's possible to use the MSCOMM32.OCX control that came with Visual Basic 6.  You need to do a spot of hex editing on the control if you want to use ports above COM12 (I think, been a long time since I used it).
 

Offline rstofer

  • Super Contributor
  • ***
  • Posts: 4692
  • Country: us
Re: Arduino Serial Data to Excel
« Reply #5 on: July 13, 2017, 11:03:46 pm »
Losing resolution (bits) because of a wide range of input voltages can be overcome with an op amp and 4 resistors.  See Chapter 4 here:

https://focus.ti.com/lit/an/slod006b/slod006b.pdf

For example, if you wanted to measure the range of 25-42 volts, you would first subtract (offset by) 25V leaving 0-17V.  Then you might scale by 4 to get 0-4.25V.

Offset and scale, that's one way to not lose so much resolution.
 

Offline sangseu

  • Contributor
  • Posts: 12
  • Country: vn
Re: Arduino Serial Data to Excel
« Reply #6 on: July 29, 2017, 03:04:24 pm »
My plan.
Please see attacked picture
 

Online gnif

  • Administrator
  • *****
  • Posts: 1031
  • Country: au
Re: Arduino Serial Data to Excel
« Reply #7 on: July 29, 2017, 03:56:40 pm »
Why not log into a database, even SQLite would be better then trying to interface to Excel directly. You could then interface from Excel to the SQLite database to import the data, avoiding any 1/2 reads, etc.
HostFission - Full Server Monitoring and Management Solutions.
https://hostfission.com/
https://twitter.com/HostFission

I volunteer my time to manage this server, if you would like to support this work I have a patreon here:
https://www.patreon.com/gnif
 
The following users thanked this post: sangseu


Share me

Digg  Facebook  SlashDot  Delicious  Technorati  Twitter  Google  Yahoo
Smf