本記事は、TWELITE PALで測定したデータを自動でExcelファイルに取り込む方法を示します。センサとして、温度、湿度、照度を測定できる環境センサーパル（AMBIENT SENSE PAL）を使いました。データの切り出し位置を変えることにより、他のセンサーパルに応用することもできるはずです。
TWELITE PALの測定データをExcel VBAまたはVisual Studioで取得したい
前回の記事で、MONOSTICKとTWELITE PAL（AMBIENT SENSE PAL）間で通信する手順を示しました。この結果、環境センサーパルで測定した温度、湿度、照度が得られました。ただし、値を取得・表示するソフトは、TWELITE STAGEでした。
TWELITE STAGEはデータを保存する機能が無いので、長時間の連続データを取るのは容易ではありません。
TWELITEのサイトを確認すると、ログを取るためには、Tera Termを使うことが提案されています。
でも、ExcelにはVBA（マクロ）があります。VBAでデータを取得・処理・保存するコードが書ければ応用が利いて便利です。それが不可能でも、Visual Basic等を用いて、データの取得からExcelファイルへの保存までのコードが書ければ、大幅に手間を省くことができます。
本記事は、VBA又はVisual BasicでTWELITE PALからのデータを取得・処理し、Excelのシートに保存することをゴールとします。
環境センサーパルのデータ構造
TWELITE PALからデータを取得できても、それを解析できなければ宝の持ち腐れです。
そこで、あらかじめ、データ構造を見ておくことにします。
測定結果だけ使えればいいという方は、この章を飛ばしていただいて結構です。
本記事で用いる環境センサーパルのデータ構造は、こちらのサイト→パルアプリ→環境センサーパルから確認することができます。他のセンサについても同じページに記載されています。
僕がTWELITE STAGEを使って、たまたま机上で取得したデータは次の通りでした。元データに区切りはありません。しかし、分かり易くなるよう、ここでは区切りを入れています。
環境センサーパルは、温度、湿度、照度が測れます。
先ず、それらのデータから解析します。
温度
温度の値は、23番目の区切りの0783です。これは、16進数です。先ずは、これを10進数に直します。
この変換は簡単です。Excelのあるセルを選び、「=HEX2DEC(“0783”)」と入力すると、10進数の1923が得られます。温度は100倍されているので、温度の測定値は19.23 ℃ということになります。
湿度
湿度の値は、28番目の区切りの117Cです。10進に直すと4476であり、100で割った44.76 %が湿度の測定値になります。
照度
照度の値は、33番目の区切りの0000018Dです。10進に直すと397であり、397 lx（ルクス）が照度になります。
光の単位は複雑なので、軽く触れておきます。まず、ろうそく１つ分の光度が１cd（カンデラ）です。カンデラとキャンドルは音が似ているので、覚えやすいですね。
1 cdのろうそくが全方向（4π sr（ステラジアン））を照らすと考えたとき、その全ての光の量、すなわち、光束は4πlm（12.56ルーメン）です。
この12.56 lmの光束が、1m2の面積に満遍なく当たったときの照度は、12.56 lxになります。1m2の面積を持つ球の半径は、約28cmです。これは、大きいビーチボールくらいの大きさです。
つまり、僕の机の上の明るさは、32本のろうそくを大きめのビーチボールの中心に持ってきたときのビーチボールの内側の明るさと同等ということがわかりました。
LQI
電源電圧の値など、温度、湿度、照度以外のデータもいくつか取得できます。
その中で、僕が興味あるのがLQIです。LQIはLink Quality Indicatorの略で、電波通信品質を示しています。これを用いると、簡易的に機器間の距離を知ることもできます。
なお、こちらのサイトによれば、LQIと受信エネルギの間には、次の関係があるとのことです。
![$$ P\left[dBm\right]=\frac{7\cdot LQI-1970}{20} $$](https://miscellaneous.tokyo/wp-content/ql-cache/quicklatex.com-e424f0afb8675678baff849622abc891_l3.png "Rendered by QuickLaTeX.com")
dBmは、1mWを基準にした単位系です。つまり、LQIは電力強度を示しています。
LQIの値は2番目の区切りのC3です。10進に直すと、195です。これを変換式に当てはめると、
![$$ P=\frac{7\times195-1970}{20}=\bf -30.25\left[dBm\right] $$](https://miscellaneous.tokyo/wp-content/ql-cache/quicklatex.com-ebaac0892e7b8cfa2c323e432cb6e77b_l3.png "Rendered by QuickLaTeX.com")
です。これをW（ワット）で表すと、
![$$ P=10^{-30.25/10}=0.000944\left[mW\right]=\bf 0.944\left[\mu W\right] $$](https://miscellaneous.tokyo/wp-content/ql-cache/quicklatex.com-eb226786b7f65620b42d2bb7ed4a98bf_l3.png "Rendered by QuickLaTeX.com")
となります。
この結果はちょっと驚きでした。僕はTWELITEのREDを使っているので、送信出力は10 mW（+10 dBm）です。この電力を30 cmしか離れていないところで送受信しただけで、4桁も小さい値になってしまっています。
受信感度は-95 dBmくらいあるので、問題ありません。でも、この値も驚きです。なんと320 pWです。
Excel VBAによるデータ取得
MONOSTICKは仮想COMポートとして認識されます。
昔のExcel VBAは、MSCommというコントロールを用いてCOMポート通信ができました。しかし、最近のVBAではサポートされていません。よって、こちらのサイトにあるように、kernel32のCreateFileやReadFileを使って同等の通信をすることになります。
僕も当初、MONOSTICKに対して、CreateFileやReadFileで通信を試みました。Excelの64ビット版ではデータが取得できる場合もありました。順調な時には１回の受信で約100バイトのデータが取得できます。でも、何らかのきっかけで、１~２バイトのデータしか取得できなくなり、戻らないことがありました。また、32ビット版のExcelでは、常に１~２バイトのデータしかとることができませんでした。このような異常は、MONOSTICKだけの現象です。他のCOMポート通信機器では、データが問題なく取れます。とても不思議です。
仕方がないので、ここでは、MSCommによるコードを示します。動作確認される場合は古いWindows PCが必要となりますので、ご了承ください。
なお、PCにMSComm（C:\Windows\System32\MSCOMM32.OCX）がインストールされていても「サブジェクトは指定された操作に対して信頼されていません」というエラーが出ることがあるようです。そのようなときには、こちらのサイトのようにレジストリを変更する対策が必要になるかもしれません。レジストリを変更すると、動かないソフトが出てくる場合がありますので、バックアップをとるなどして、自己責任で対応してください。
Excel VBAによるデータ取得手順
- MONOSTICKをUSBポートに挿す
- コントロールパネル→デバイスマネージャー→ポート（COMとLPT）と辿り、MONOSTICKのCOMポート番号を調べる
- Excelを起動→開発タブ→Visual Basic→現ファイルのVBAProjectを右クリック→挿入→ユーザーフォーム
※ 開発タブが見つからない方はこちらの記事を参考にしてタブを表示してください。
- ツール→その他のコントロール→Microsoft Communicaations Control, version 6.0にチェック→OK
電話のアイコンをフォーム上にドラッグ
- 電話のアイコン上でクリックし、そのままフォーム上にドラッグするとMSCommアイコンがフォーム上に現れます
- F7キーを押してコードを表示する→下記コードをコピー＆ペーストする
Option Explicit
'-----------------------------------------------------------------------
' Summary: COMポートから受診した時の処理
'-----------------------------------------------------------------------
Private Sub MSComm1_OnComm()
Dim lngRow As Long
Dim strInput As String
If MSComm1.CommEvent Then
strInput = MSComm1.Input
lngRow = Range("A" & Rows.Count).End(xlUp).Row + 1
Range("A" & lngRow).Value = Now
Range("B" & lngRow).Value = (7 * ("&H" & Mid(strInput, 10, 2)) - 1970) / 20
Range("C" & lngRow).Value = ("&H" & Mid(strInput, 64, 4)) / 100
Range("D" & lngRow).Value = ("&H" & Mid(strInput, 76, 4)) / 100
Range("E" & lngRow).Value = CInt("&H" & Mid(strInput, 88, 8))
End If
End Sub
'-----------------------------------------------------------------------
' Summary: フォームを開く時の処理
'-----------------------------------------------------------------------
Private Sub UserForm_Initialize()
' タイトル行を示す
Range("A1:E1") = Split("時 刻,LQI dBm,温度 ℃,湿度 %,照度 lx", ",")
With MSComm1
' ポートが開いていたら閉じる
If .PortOpen = True Then .PortOpen = False
' ポート番号やボーレートを設定する
.CommPort = 3 ' →環境に合わせて番号を変える
.RThreshold = 1
.Settings = "115200,N,8,1"
' ポートを開く
.PortOpen = True
End With
End Sub
'-----------------------------------------------------------------------
' Summary: フォームを閉じるときの処理
'-----------------------------------------------------------------------
Private Sub UserForm_Terminate()
' ポートを閉じる
MSComm1.PortOpen = False
End Sub- コード中のCommPort = 3の数字を、MONOSTICKのCOMポート番号に変更する
- 現ファイルのVBAProjectを右クリック→挿入→標準モジュール
- 下記コードを貼り付ける
Option Explicit
'-----------------------------------------------------------------------
' Summary: 測定を開始する
'-----------------------------------------------------------------------
Public Sub Start()
' UserForm1を開く
Call UserForm1.Show
End Sub- ワークシートに戻り、開発タブ→マクロ→Start→実行
Visual Basicによるデータ取得
ここに示すコードは、Visual Basic 2019にて作成しました。
- MONOSTICKをUSBポートに挿す
- コントロールパネル→デバイスマネージャー→ポート（COMとLPT）と辿り、MONOSTICKのCOMポート番号を調べる
- Visual Studioを立ち上げる
- Visual BasicのWindowsフォームアプリケーション（.NET Framework）プロジェクトを適当な名前で作成する（僕はAmbientSensePalというプロジェクト名にしました）
フォームの上にSerialPortをドラッグ
- ツールボックス→コンポーネント→SerialPortをクリックし、フォームの上にドラッグ
- F7キーでコードを表示して、次のコードをコピー＆ペースト
Imports Microsoft.Office.Interop
Imports System.ComponentModel
Imports System.IO.Ports
Public Class Form1
'-------------------------------------------------------------------
' Summary: Excelアクセス用オブジェクト
'-------------------------------------------------------------------
Private m_xlApp As New Excel.Application
Private m_xlBook As Excel.Workbook
Private m_xlSheet As Excel.Worksheet
'-------------------------------------------------------------------
' Summary: フォームをロードしたときの処理
'-------------------------------------------------------------------
Private Sub Form1_Load(sender As Object, e As EventArgs) _
Handles Me.Load
' エラーメッセージをクリア
Dim strErr As String = ""
' Excelファイル名をデスクトップのAmbientSensePal.xlsxにする
Dim strFilePath As String = System.Environment _
.GetFolderPath(Environment.SpecialFolder _
.DesktopDirectory) & "\" & My.Application _
.Info.AssemblyName & ".xlsx"
' Excelファイルがあれば開き、なければ作成する
If System.IO.File.Exists(strFilePath) Then
Try
m_xlBook = m_xlApp.Workbooks.Open(strFilePath)
Catch ex As Exception
strErr = $"{My.Application.Info.AssemblyName}" &
".xlsx が開けませんでした"
GoTo PostHandler
End Try
Else
Try
m_xlBook = m_xlApp.Workbooks.Add
m_xlApp.DisplayAlerts = False
m_xlBook.SaveAs(strFilePath)
m_xlApp.DisplayAlerts = True
Catch ex As Exception
strErr = $"{My.Application.Info.AssemblyName}" &
".xlsx を作成できませんでした"
GoTo PostHandler
End Try
End If
' タイトル行をセットする
Dim strTitle(,) As Object =
{{"時 刻", "LQI dbm", "温度 ℃", "湿度 %", "照度 lx"}}
m_xlSheet = DirectCast(m_xlBook.Worksheets(1), Excel.Worksheet)
m_xlSheet.Range("A1:E1").Value = strTitle
' シリアルポートを開く
With Me.SerialPort1
' ポートが開いていたらいったん閉じる
If .IsOpen Then .Close()
' ポート番号やボーレートの設定
.PortName = "COM3" ' ← 環境に応じて番号を変更する
.BaudRate = 115200
.DataBits = 8
.Parity = IO.Ports.Parity.None
.StopBits = IO.Ports.StopBits.One
' ポートを開く
Try
.Open()
Catch ex As Exception
strErr = $"{ .PortName}が開けませんでした"
GoTo PostHandler
End Try
End With
PostHandler:
' エラーが発生していたら表示してフォームを閉じる
If strErr <> "" Then
MessageBox.Show(strErr)
Me.Close()
End If
End Sub
'-------------------------------------------------------------------
' Summary: SerialPort1の受信時の処理
' データ構造は、以下のURL→パルアプリ→環境センサーパル
'https://mono-wireless.com/jp/products/TWE-APPS/App_Wings/parent.html
'-------------------------------------------------------------------
Private Sub SerialPort1_DataReceived(
sender As Object,
e As SerialDataReceivedEventArgs) _
Handles SerialPort1.DataReceived
' 受信データを内部変数に保持
Dim strData As String = Me.SerialPort1.ReadExisting
If strData.Length < 88 + 8 Then Exit Sub
With m_xlSheet
' 入力行(最下記載済行の次の行)を取得する
Dim lngRow As Long = .Range("A" & .Rows.Count) _
.End(Excel.XlDirection.xlUp).Row + 1
' 時刻の入力
.Range("A" & lngRow).Value = Now
' LQIから電界強度[dBm]の算出
.Range("B" & lngRow).Value =
(7 * CLng("&H" & Mid(strData, 10, 2)) - 1970) / 20
' 温度データは100倍されている
.Range("C" & lngRow).Value =
CLng("&H" & Mid(strData, 64, 4)) / 100
' 湿度データは100倍されている
.Range("D" & lngRow).Value =
CLng("&H" & Mid(strData, 76, 4)) / 100
' 照度は整数値で得られる
.Range("E" & lngRow).Value =
CLng("&H" & Mid(strData, 88, 8))
' 都度保存する
m_xlApp.DisplayAlerts = False
m_xlBook.Save()
m_xlApp.DisplayAlerts = True
End With
End Sub
'-------------------------------------------------------------------
' Summary: フォームを閉じるときの処理
'-------------------------------------------------------------------
Private Sub Form1_Closing(
sender As Object,
e As CancelEventArgs) _
Handles Me.Closing
' シリアルポートを閉じる
Me.SerialPort1.Close()
' Excelファイルを念のため保存する
m_xlApp.DisplayAlerts = False
m_xlBook.Save()
m_xlApp.DisplayAlerts = True
' Excelを閉じる
m_xlSheet = Nothing
m_xlBook = Nothing
m_xlApp.Quit()
m_xlApp = Nothing
End Sub
End Class- .PortName = “COM3″を、MONOSTICKのCOMポート番号に変更する
- F5キーを押して実行
すると、デスクトップに「プロジェクト名.xlsx」というファイルが作成され、その中にデータが入ります。
Excelシートの例
どちらの場合も、以下のように取得できます。
Excelシートの例
A列のフォーマットは秒まで見えるように書式を変更してあります。
取得周期は60秒にセットしてありますが、64～67秒くらいでバラついていますね。
このデータを取得したときは、直射日光の下に置いておいたので、短い時間で温度が次第に上がり、照度も40 000 lxくらいの大きな値が得られました。
まとめ
TWELITEの環境センサーパル（AMBIENT SENSE PAL）の測定データをExcelファイルに自動的に保存することができました。今後、TWELITEを使っていくための第一歩だと思います。
ただ、厳密には、温度が負の場合には対応できていませんので、氷点下で使用される場合は、ご注意ください。多分、１桁目が「F」だったら、その左に「FFFF」を付けてから10進に変換すれば良いと思います。でも、冷蔵庫等で冷やすのが面倒なので、試してはいません。
コメント