We successfully developed an automated system that links our OEE (Overall Equipment Effectiveness) Spreadsheet directly to MaintainX meter hour counters using Excel VBA and the MaintainX REST API.

Purpose:

Operators are already required to fill out the OEE sheet as part of the manufacturing process. However, they would likely not consistently update meter readings in MaintainX. By automating this process, we:

• Eliminate duplicate data entry.

• Ensure accurate meter tracking.

• Automatically trigger TPM (Total Productive Maintenance) work orders based on real machine usage hours.

• Streamline the operator’s day by reducing manual tasks to only what is already required.

System Overview:

• OEE Sheet: Captures machine running hours in weekly sheets.

• Excel VBA Macros: Collect running hours from the OEE data, convert them to total hours, and send them via the MaintainX API to update the corresponding machine meter.

• Daily Trigger: VBA is configured to send updates once per day automatically when the workbook is opened, ensuring no duplicate submissions.

Key Excel VBA Code :

Sub SendMaintainXMeterReading()
    Dim http As Object
    Dim url As String
    Dim apiKey As String
    Dim meterID As String
    Dim rawVal As Variant
    Dim newReading As Double
    Dim jsonBody As String

    ' === CONFIGURATION ===
    meterID = "REDACTED_METER_ID"
    apiKey = "REDACTED_API_KEY"
    url = "https://api.getmaintainx.com/v1/meters/" & meterID & "/readings"

    ' === GET VALUE FROM EXCEL ===
    rawVal = Sheets("Mx Link").Range("C55").Value ' Change to the correct cell for each machine

    If IsNumeric(rawVal) Then
        newReading = CDbl(rawVal)
    Else
        MsgBox "? Cell must contain a numeric hour value."
        Exit Sub
    End If

    ' === JSON PAYLOAD ===
    jsonBody = "{""readingValue"":" & Format(newReading, "0.00") & "}"

    ' === SEND REQUEST ===
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "POST", url, False
    http.setRequestHeader "Content-Type", "application/json"
    http.setRequestHeader "Authorization", "Bearer " & apiKey
    http.send jsonBody

    ' === RESPONSE ===
    If http.Status = 200 Or http.Status = 201 Then
        MsgBox "? Meter reading (" & Format(newReading, "0.00") & " hrs) sent successfully!"
    Else
        MsgBox "? Error: " & http.Status & vbNewLine & http.responseText
    End If
End Sub
 

Additional Features:

• Daily Control Mechanism:

  • The VBA checks the date in a control cell (e.g., Z1) to ensure the data is only sent once per day.

• "Send Now" Button:

  • A manual button was added for immediate sending if required, without waiting for the next workbook opening.

• Scalability:

  • The system was expanded to handle multiple machines by linking each machine's hours in separate columns and corresponding MaintainX meters.

Benefits:

Automated Meter Updates: No manual entries required in MaintainX.
Accurate TPM Scheduling: Based on real-time machine usage.
Operator Efficiency: Only one data entry point required (OEE sheet).
Error Reduction: Less manual input reduces the chance of missed or incorrect meter readings.
Daily Control: Prevents multiple sends if the file is opened repeatedly.