PIC Data to Excel - How May It Be Done?

Status
Not open for further replies.

Member
Hello,

Hope everybody is ok.

I have been messing around with RS232 and have built a temperature monitoring circuit using an 18F2420. I can send temperature data to Hyperterminal. Pretty basic stuff I know for you more experienced guys but for me it's like, WOW!

Anyway, what I would like to do is send / import the data to Excel so that I may create a graph of temp' vs time. Could you please advise if this is possible and if so give me some tips to get me going. I've not done any programming apart from simple ASM although a few years ago did do some very basic stuff with the VB that is 'included' with Excel.

Thanks in advance for any help.

blueroomelectronics

Well-Known Member
I used CSV in a datalogger program. An example is in JPUG #2 on my site

paulfjujo

New Member
PIC Data to excel

hello,

you can use PORT.DLL under excel with Vba

here is an example, how to use this dll

Code:
Option Explicit                         'force explicit variable declaration
Public Const sheet1 As String = "Visu"
Public Const sheet2 As String = "Data"
Public Const config As String = "Configuration"

Declare Function OPENCOM Lib "port.dll" (ByVal a$) As Integer Declare Function INITCOMPULAB Lib "port.dll" (ByVal COM%) As Integer Declare Function FINDHARD Lib "port.dll" (ByVal Meldung%) As Integer Declare Sub CLOSECOM Lib "port.dll" () Declare Sub SENDBYTE Lib "port.dll" (ByVal b%) Declare Function READBYTE Lib "port.dll" () As Integer Declare Sub DOUT Lib "port.dll" (ByVal Wert%) Declare Function DIN Lib "port.dll" () As Integer Declare Function AIN Lib "port.dll" (ByVal Eingang%) As Integer Declare Sub DELAY Lib "port.dll" (ByVal b%) Declare Sub TIMEINIT Lib "port.dll" () Declare Function TIMEREAD Lib "port.dll" () As Long Private Const sheet1 As String = "Visu" Private Const sheet2 As String = "Data" Private Const FrameHeader As String = "T" '10 'was "A" ' "TX" Private Const FrameTail As String = "C" Private Const FrameLength As Byte = 15 ' was 6 Dim s1 As Variant, s2 As Variant Dim sortie As Integer Dim dr As Integer Dim s3 As Variant Sub StartScope() Dim ix As Byte, ComPort As String Dim port As Byte, baudrate As Long, parity As String, databits As Byte, stopbit As Byte Dim duration As Long, interval As Long Set s3 = ThisWorkbook.Sheets(config) 'get data acquisition configuration: With s3 ix = .Cells(3, 2).value 'get number of index selected duration = .Cells(3 + ix, 1).value * 1000 'get index ix = .Cells(3, 4).value interval = .Cells(3 + ix, 3).value * 1000 'get RS232 configuration: ix = .Cells(3, 6).value port = .Cells(3 + ix, 5).value ix = .Cells(3, 8).value baudrate = .Cells(3 + ix, 7).value ix = .Cells(3, 10).value parity = .Cells(3 + ix, 9).value ix = .Cells(3, 12).value databits = .Cells(3 + ix, 11).value ix = .Cells(3, 14).value stopbit = .Cells(3 + ix, 13).value End With ComPort = "COM" & port & ":" & baudrate & "," & parity & "," & stopbit ' ComPort = "COM" & port & ":" & baudrate & "," & parity & "," & databits & "," & stopbit Call Data_Fetch.GetData(ComPort, duration, interval) End Sub Sub GetData(ComPort, duration, interval) Dim RecString As String, ErrorString As String Dim HI As Long, LO As Byte, value As Double Dim row As Integer, n As Integer, time As Long Dim succ As Integer, I, dummy As Integer Set s1 = ThisWorkbook.Sheets(sheet1) Set s2 = ThisWorkbook.Sheets(sheet2) s2.Activate 'activate data sheet s2.Columns("A:B").Select 'select data Selection.ClearContents 'erase data columns Range("C1").Select s1.Activate 'activate scope Range("C5").Select s1.FrameHeaderBox.Text = FrameHeader 'display framing information s1.FrameTailBox.Text = FrameTail succ = 1 sortie = 0 succ = OPENCOM(ComPort) 'open serial communication port (DTR=1, RTS=0) If (succ = 0) Then dummy = MsgBox("RS232 connection failed: " & ComPort, vbCritical, "PORT.DLL") Else RecString = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" row = 1 'row counter time = interval 'reset time ActiveSheet.DrawingObjects("BarBack").Width = 100 'configure X/Y-axis of chart: With s1.ChartObjects(1).Chart.Axes(xlCategory) .MinimumScale = 0 'x-axis .MaximumScaleIsAuto = True .MajorUnitIsAuto = True 'y-axis .MinorUnitIsAuto = True End With '************** Outer Loop ************* TIMEINIT n = 0 While TIMEREAD <= (duration + interval) value = 0 dr = 0 RecString = "" '************** Inner Loop ************* Do ' Do K% = READBYTE If K% > 0 Then a$ = Chr(K%)
' Debug.Print a$, dr If ((a$ = FrameHeader) Or (dr = 1)) Then
dr = 1
RecString = RecString + a$If ((a$ = FrameTail) And (dr = 1)) Then
'  If ((a$= Chr(10)) And (dr = 1)) Then ' <- bug ? I = Len(RecString) 'Debug.Print "len="; I s1.TextBox1.Text = RecString s1.HiByteBox.Text = Left$(RecString, 5)
s1.LoByteBox.Text = Right$(RecString, 4) s1.Measure.Text = Mid$(RecString, 7, 6)
value = Val(s1.Measure.Text)
RecString = ""
dr = 0
a$= "" n = n + 1 DoEvents End If End If End If ' Loop Until (dr = 0) ' n = n + 1 Loop Until ((TIMEREAD > time) Or (sortie = 1)) 'timeslice finished '************** End Inner Loop ************* If sortie = 1 Then End 'break ' Application.Calculation = xlCalculationManual s2.Cells(row, 2).value = value s2.Cells(row, 1).value = (time - interval) / 1000 ActiveSheet.DrawingObjects("BarBack").Width = (time - interval) / duration * 100 ' Application.Calculation = xlCalculationAutomatic s1.TextBox2.Text = row row = row + 1 'next row time = time + interval 'next timeslice 'Debug.Print time Wend '************** End Outer Loop ************* CLOSECOM 'configure X-axis of chart to full range: Application.Calculation = xlCalculationManual With s1.ChartObjects(1).Chart.Axes(xlCategory) .MinimumScale = 0 'x-axis .MaximumScale = duration / 1000 .MajorUnitIsAuto = True 'y-axis .MinorUnitIsAuto = True End With Application.Calculation = xlCalculationAutomatic s1.TextBox1.Text = "Appuyer sur START" End If End Sub i tested it successfully, wit PIC16F application .. see my web page paulfjujo another, similar is RSAPI.dll Code: Mit RSAPI RS232 Schnittstelle auslesen geschrieben von: bademeister85 (IP bekannt) Datum: 20.04.08 16:48 Hallo, möchte gerne ein RFID Lesegerät auslesen, dieses ist über USB angeschlossen und generiert einen virtuellen COM Port so dass man über RS232 programmieren kann. Hier mein bisheriger Code: Declare Sub OPENCOM Lib "RSAPI.DLL" (ByVal Parameter$)
Declare Function READBYTE Lib "RSAPI.DLL" () As Integer
Declare Sub TIMEOUT Lib "RSAPI.DLL" (ByVal ms%)
Declare Sub CLOSECOM Lib "RSAPI.DLL" ()
Declare Sub DELAY Lib "RSAPI.DLL" (ByVal ms%)

Sub c_control()
OPENCOM "COM3:9600,N,8,1"
ThisWorkbook.Sheets("Tabelle1").Activate
Columns("A:c").Select
Selection.ClearContents
Range("A2").Select
TIMEOUT 1000
zeile = 3
Do
If e1 >= 0 Then
Cells(zeile, 1).Value = e1
Cells(1, 5).Value = zeile
zeile = zeile + 1
End If
Loop Until (e1 < 0)
CLOSECOM
Calculate
MsgBox Str(zeile - 1) + " Meßwerte gelesen"
End Sub

a 3e solution is to use VB6 to build a data server
witch drive the RS232 link and then , use DDE link with an excel sheet.

Attachments

• 221.6 KB Views: 918

Hero999

Banned
I've never tried it but there are open source libraries which can handle creating and modifying MS Office files.

They're part of OpenOffice.org, which is unfortunately PC based.

colin mac

New Member
Excel is proprietary. It's easy to create CSV files.

eg:

10,12,14

Save as .csv. Then it can be opened in Excel and handled by a programming language..

Last edited:

Noggin

Member
I think this is similar to what colin is saying...

What I do is just have the terminal program capture it as a text file. Open excel, then tell excel to open the txt file. When you select the file, excel will ask questions about how you want to arrange the numbers into columns. Instead of using spaces between the temps, use carriage return and line feed

Wilksey

Member
Ideally you dont want to be using VBA to do this kind of thing.

Use Express 2008 and drop in the serial port control.

Basically CSV is the easiest form of data logging, it stands for Comma Seperated Values, which equates to: (21, 34.4, 20.1, 18.2) these are for example temperatures from your hardware, this will put it in A1 - D1 in excel, when it reads a CRLF (carriage return, line feed) it will move it to the next row, so if it was (21 <CRLF> 34.4 <CRLF> 20.1 <CRLF> 18.2 <CRLF> this will put the values into A1 - A4 in excel.

The serial port control in .NET can be made to trigger on certain lengths and characters, or you can constantly try and "read" from the port, so it's going to be a case of experimentation.

And as always, dont forget your error handling!

Hope this helps.

Pommie

Well-Known Member
Ideally you dont want to be using VBA to do this kind of thing.
Why? You can use the MSComm control in excel. There is very little that can't be done in VBA.

Mike.

Wilksey

Member
Because VBA is not a application development tool.

Also, if you want to run it on another machine you will need excel on that machine also, as it is a macro.

.NET Express is a free tool from MS, this will allow you to develop a proper application and later put the data into a text file or database, I know VBA can also do this, and if the free express editions were not available and feature full (for a free version) I would say use VBA.

Anyway, just my opinion, nobody has to follow it.

marcosfpmoreira

New Member
Hello,

Can you help me ?

Which "DLL" must I use ?

What I would like to do is send the data from the multimeter to Excel/VB so that, every 30 seconds, the average and standard deviation of the last data are calculated and showed to the user. How can I do this ? I have no experience with data acquisition.

I have a multimeter (digital multimeter Minipa ET-2800) which can be connected to a computer (desktop) using an interface RS-232. The cable is plugged into the printer port (serial). The multimeter provides readings at intervals of 2-4 seconds.

A second question: In the case of a laptop to be used instead of a desktop computer, how would the data acquisition by Excel/VB be made ? The connection is of type RS-232/USB. Can I use the same "DLL"?