Welcome to our site!

Electro Tech is an online community (with over 170,000 members) who enjoy talking about and building electronic circuits, projects and gadgets. To participate you need to register. Registration is free. Click here to register now.

  • Welcome to our site! Electro Tech is an online community (with over 170,000 members) who enjoy talking about and building electronic circuits, projects and gadgets. To participate you need to register. Registration is free. Click here to register now.

PIC Data to Excel - How May It Be Done?

Status
Not open for further replies.
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.

8447-Temperature%20to%20Hyperterminal_reduced.jpg
 

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 
e1 = READBYTE 
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

  • Pic_Excel.jpg
    Pic_Excel.jpg
    221.6 KB · Views: 927

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.
 

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"?

Thanks in advance.
 
Status
Not open for further replies.

Latest threads

EE World Online Articles

Loading
Top