Retrieving weight from scale in Excel using VBA

[bw277]

0
down vote
favorite
I have connected a weighing scale to my PC via an RS-232 to USB converter cable. My goal was to create a command button in excel 2007 that would place the weight from the scale into the selected cell. I got it to work using the following code in a userform.

Private Sub XMCommCRC1_OnComm()
    Static sInput As String
    Dim sTerminator As String
    Dim Buffer As Variant


' Branch according to the CommEvent property
    Select Case XMCommCRC1.CommEvent
    Case XMCOMM_EV_RECEIVE
        Buffer = XMCommCRC1.InputData ' Use Input property for MSComm
        sInput = sInput & Buffer
        If Worksheets("Settings").Range("Terminator") = "CR/LF" Then
            sTerminator = vbCrLf
        Else
            sTerminaotr = vbCr
        End If
        If Right$(sInput, Len(sTerminator)) = sTerminator Then
            XMCommCRC1.PortOpen = False
            sInput = Left$(sInput, Len(sInput) - Len(sTerminator))
            Select Case Left$(sInput, 2)
            Case "ST", "S "
                ActiveCell.Value = CDbl(Mid$(sInput, 7, 8))
                ActiveCell.Activate
            Case "US", "SD"
                MsgBox "The balance is unstable."
            Case "OL", "SI"
                MsgBox "The balance is showing an eror value."
            End Select
            sInput = ""
        End If
    End Select
End Sub




Public Sub RequestBalanceData()
    With Worksheets("Settings")
' Configure and open the COM port
        If Not XMCommCRC1.PortOpen Then
            XMCommCRC1.RThreshold = 1
            XMCommCRC1.RTSEnable = True
            XMCommCRC1.CommPort = .Range("COM_Port")
            XMCommCRC1.Settings = .Range("Baud_Rate") & "," & _
            .Range("Parity") & "," & _
            .Range("Data_Bits") & "," & _
            .Range("Stop_Bits")
            XMCommCRC1.PortOpen = True
        End If


' Send balance's "SI" (Send Immediate) command
' to request weighing data immediately
        If .Range("Terminator") = "CR/LF" Then
            XMCommCRC1.Output = "R" & vbCrLf
        Else
            XMCommCRC1.Output = "R" & vbCr
        End If
    End With
End Sub

I then created a command button with the following code.

Private Sub CommandButton1_Click()


    UserForm1.RequestBalanceData


End Sub

When I click on the command button the weight is placed in the selected cell. However, this does not consistently happen. Sometimes when I click the button nothing will be placed in the cell, and I will have to click it multiple times until the weight is placed in the cell. I would like to fix this, but I'm not sure where to start. Is it a problem with the code itself, or is it more likely a problem with the converter or the scale itself?


Any help is appreciated.


Here is the scale: Weighing Scales | Rancho Cucamonga CA USA | Optima Scale


Here is the converter cable: Amazon.com: AV Access 6FT/2M USB to RS232 DB9 Cable,Computer Serial Cable for Windows 10/8/7/Vista/XP/2000/ME/98SE Linux/Mac: Cell Phones & Accessories


Here is the tutorial I used for the code: MSC-LIMS Laboratory Information Management System


Here is the ActiveX control from the tutorial that I used: XMComm Information Page

 

[AKR22]

try inserting the "requestbalancedata" code into the "commandbutton1".