利用Excel VBA利用串列埠RS232讀取Arduino資料

使用：NetCommOCX

我的電腦係win7 32位元 請至 

NETCommOCX Information Page下載

 Updated installer for Windows 7

依NETCommOCX Information Page介紹安裝以系統管理員身分執行Setup.exe

參考：Visual Basic for Applications NETcomm Serial Port Driver Example有安裝控制項的說明

Excel 2007 -->開發人員-->Visual Basic-->

新增一個NETComm1、TextBox1、CommandButton1、CommandButton2

NETComm1的commport記得要填上arduino使用的COM

將下列程式碼複制到VBA程式碼

Private Sub CommandButton1_Click()
    On Error Resume Next
    NETComm1.SThreshold = 1
    NETComm1.RThreshold = 1
    NETComm1.CommPort = 4
    If NETComm1.PortOpen = False Then  'if the serial port is closed
        NETComm1.PortOpen = True       'open the serial port
    End If
    If Err Then MsgBox Error$, 48
End Sub

Private Sub CommandButton2_Click()
    'Close serial port on exit
    On Error Resume Next    'Error handler
    
    If NETComm1.PortOpen = True Then    'check if the serial port is open
        NETComm1.PortOpen = False       'close the serial port
    End If
    If Err Then MsgBox Error$, 48     'Display error in message box
    End
End Sub

Private Sub NETComm1_OnComm()
    Dim d As Date
    Static Buffer As String
    Dim CRLFPos As Integer
    Buffer = Buffer & NETComm1.InputData 'or whatever name you use for the instance of NETComm
    
    CRLFPos = InStr(Buffer, vbCr)
    If CRLFPos > 0 Then
        Dim MyData As String
        d = Now()
        MyData = d & Mid(Buffer, 1, CRLFPos - 1) & vbCrLf
        TextBox1.Text = TextBox1.Text & MyData
        Buffer = ""
    End If
End Sub
Private Sub UserForm_Terminate()
    'Close serial port on exit
    On Error Resume Next    'Error handler
    
    If NETComm1.PortOpen = True Then    'check if the serial port is open
        NETComm1.PortOpen = False       'close the serial port
    End If
    If Err Then MsgBox Error$, 48     'Display error in message box
    
End Sub

Arduino接線圖

Arduino端程式碼

#include <DHT.h>
#define DHTPIN 2    // 設定DHT的接腳
#define DHTTYPE DHT11   // DHT 11 定義DHT的類型為DHT11
//#define DHTTYPE DHT22   // DHT 22  (AM2302), AM2321
DHT dht(DHTPIN, DHTTYPE);
void setup() {
  Serial.begin(9600);
  //Serial.println("DHT11 test!");
  dht.begin(); //啟動DHT
}
 
void loop() {
  // 每次偵測間隔2秒
  char ch=13;
  delay(2000);
  
  //讀取濕度
  float h = dht.readHumidity();
  //讀取攝氏溫度
  float t = dht.readTemperature();
  //讀取華氏溫度
  float f = dht.readTemperature(true);
 
  //檢查是不是有讀到資料
  if (isnan(h) || isnan(t) || isnan(f)) {
    Serial.println("Failed to read from DHT sensor! \n");
    return;
  }
  Serial.print("Humidity:");
  Serial.print(h,1); //顯示到小數點後一位
  Serial.print("%/");
  Serial.print("Temperature:");
  Serial.print(t,1);//顯示到小數點後一位
  Serial.print("C");
  Serial.print(ch);

}

Arduino透過串列埠傳送溫溼度數值給電腦的Excel VBA