Sending and receiving ASCII commands in excel

[gir]

Hi All,

I have a Fluke Multimeter that i am trying to communicate with to automatically import readings from the multimeter into Excel. Fluke was kind enough to provide me with the ASCII commands that work with the device and the RS-232 information (listed below). I have been looking low and high on the internet for a solution to this and have come up empty handed. I need to send the string "QM<CR>" (query primary measurement displayed value and a carriage return), receive the value and put it in a cell. It would be bonus points if I could make it into a function that could be typed into a cell such as naming the macro "get_resistance" and have the ability to enter into a cell =get_resistance() and have it run. I have tried a few different lines of code i have found on the internet but none of it has worked so far, i am pretty good at excel but unfortunately my experience with VBA is super limited. I don't know if I need to do anything special to it being a com port because its a USB device and its saying RS-232 connection and saying to use putty to communicate with the device.

Thank you in advance!!!

___________________________
BPS=115200 Baud
Data Bits = 8
parity = none
stop bits = 1
___________________________
Code tried


Sub ID()
COMPort = FreeFile
Close #COMPort
Open "COM3:115200,N,8,1" For Binary Access Read Write As #COMPort
VarString$ = "QM"
Put #COMPort, , VarString$
Close #COMPort
End Sub

[snb]

Maybe ??


Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
Msgbox input(LOF(1), 1)
close
End Sub

[gir]

that is definitely working better!! thank you so much!!!

i can see an icon appearing on the fluke showing that it is getting the communication but the window is coming back blank. Is there code that needs to be added to recieve the result? Also what would i need to change to have it output to a cell instead of a message box?



Maybe ??


Sub ID()
&nbsp;&nbsp; Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
&nbsp;&nbsp; &nbsp; Put #1,1,"QM" &amp; vbCr
&nbsp;&nbsp; &nbsp; Msgbox input(LOF(1), 1)
&nbsp;&nbsp; close
End Sub

[gir]

29402
This may help as well

[snb]

try:


Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
Msgbox inputB(LOF(1), 1)
close
End Sub

[pike]

try,,
Dim blnStop As Boolean
subID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Do While blnStop = False
Put #1, 1, "QM" & vbCr
DoEvents
‘MsgBox InputB(LOF(1), 1)
debug.print InputB(LOF(1), 1)
Loop
Close
End Sub
’ new control button with
Sub Resetstop()
blnStop = Not blnStop
End Sub

[gir]

Hey snb,

So this morning i tried your new code and unfortunately it is still giving blank message boxes. The good news is i was playing around with commands and i was able to send "RI" (ResetInstrument) to the multimeter and it did in fact reset the multimeter so i know that the commands are being sent and reaching the multimeter they just are not getting back to excel.

[gir]

Hi Pike,

This morning i tried your code and unfortunetly it told me "compile error: expected ="


try,,
Dim blnStop As Boolean
subID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Do While blnStop = False
Put #1, 1, "QM" & vbCr
DoEvents
‘MsgBox InputB(LOF(1), 1)
debug.print InputB(LOF(1), 1)
Loop
Close
End Sub
’ new control button with
Sub Resetstop()
blnStop = Not blnStop
End Sub

[pike]

Try,,
You will need DoEvents to pass control to the operating system. Control is returned after the operating system has finished processing the events in its queue.

Dim blnStop As Boolean
sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Do While blnStop = False
Put #1, 1, "QM" & vbCr
DoEvents
MsgBox InputB(LOF(1), 1)
Debug.Print InputB(LOF(1), 1)
Loop
Close
End Sub
' new control button with
Sub Resetstop()
blnStop = Not blnStop
End Sub

[gir]

Hey SNB unfortunately it still is not working but if i understand your code correctly

first thing it does is it opens the com port as read and write to a file named #1

Next it sends file #1 in the format of a longtype with the variable "QM" and a carriage return

then it makes a message box where it is using the input command to write the long form of file one to file one and display it
___________________________________________________________
would this need something with the Get command to take the information the com port is sending and write it to file #1 so that file #1 can be displayed?



try:


Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
Msgbox inputB(LOF(1), 1)
close
End Sub

[gir]

Haha I tried this code and it no longer gives me the looking for = exception but it now opens an infinite amount of blank message boxes and continuously pings the multimeter.

I stepped through the code using f8 and it goes into the do while blnstop = false and just hits loop for forever makign blank msgboxes

I read through my documentation from the multimeter and it says that it responds the answer followed by a carriage return. Could the carriage return be part of the problem? Also it says that the responses from teh device will also be in ASCII letters and digits if that makes a difference as well.

Also thank you so much for your help and looking into this


Try,,
You will need DoEvents to pass control to the operating system. Control is returned after the operating system has finished processing the events in its queue.

Dim blnStop As Boolean
sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Do While blnStop = False
Put #1, 1, "QM" & vbCr
DoEvents
MsgBox InputB(LOF(1), 1)
Debug.Print InputB(LOF(1), 1)
Loop
Close
End Sub
' new control button with
Sub Resetstop()
blnStop = Not blnStop
End Sub

[snb]

Input means: show the received signal form the device.

You could test some alternatieves


Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
Msgbox inputB(LOF(1), 1)
close
End Sub

Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
Msgbox input(LOF(1), 1)
close
End Sub

Sub ID()
dim sn() as byte
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1,1,"QM" & vbCr
redim sn(LOF(1))
Get #1, , sn
close
End Sub

[gir]

I GOT IT WORKING!!!! I changed the "number" to be an actual number so that is grabbing the first 30 characters of file 1 and it is returning exactly that. This is a solution that I can use but is there a way to get it to actually dynamically adjusting how many characters to return so it is not just returning a fixed number?


Sub ID()
Open "COM3:115200,N,8,1" For Binary Access Read Write As #1
Put #1, 1, "qm" & vbCr
MsgBox (Input(30, #1))
Close
End Sub

[snb]

Nice, but LOF(1) should be doing just what you are asking for : all characters = Length Of File

you might try

Input(LOF(1)-1, 1)

[RussetLodge]

I came across this code from Github user "Serialcomms". It works extremely well for serial port communication from within Excel VBA.
https://github.com/Serialcomms/Serial-Ports-in-VBA-new-for-2022

Have a look through the Howto (https://github.com/Serialcomms/Serial-Ports-in-VBA-new-for-2022/blob/main/HowTo) and the functions list and there is basically one file to download and include as a module in your VBA project.
The Howto has got a lengthy-looking procedure, but each step is actually really simple.

This code also overcame the problem of VBA and Excel not recognising COM ports greater than 16. Worked fine on a USB adapter, COM73, (Win 10)

Congratulations and thanks to Serialcomms.:bow: