How to clear excel memory

piercet

New Member
Joined
Jul 16, 2018
Messages
3
I am using Excel 2016 as a means to capture data from a piece of equipment that has a web IP address/USB port. If I use a web browser, the URL would be http://127.0.0.1:44789/evox/bacnet/2/0/51/117 and would get back a response <int< font=""> xmlns:trane="</int<>trane:evox" href="/evox/bacnet/2/0/51/117" val="64"/>
The val represents °F

I use Excel to loop through many different points and verify the point are working. Then we change the units on the equipment from Imperial to SI (metric) and recheck to make sure the equipment response in the correct units.
Excel appears to look at memory and not get the new information. It shows the old value.
If I do it with a web page (chrome or IE) it is correct.
If I close Excel and reopen it and will get the updated information.

My question is how do I get Excel to not use what is cached and get a fresh value


Rich (BB code):
Rich (BB code):
Public Sub Looping()


Dim xmlhttp As New MSXML2.xmlhttp, myurl As String, xmlresponse As New DOMDocument


On Error GoTo ErrorHandler1


For Each cell In Range("D90:D200")
    If Not IsEmpty(cell.Value) And cell.Value = "Pause" Then
        MsgBox ("In Tracer TU, Utilities - Controller - Controller Settings - Protocol, change the units, then click OK")
    ElseIf Not IsEmpty(cell.Value) And cell.Value = "Pause1" Then
        msgboxResult = MsgBox("Did the Unit change?", vbYesNo)
        If msgboxResult = vbNo Then Exit Sub
     
    
    ElseIf Not IsEmpty(cell.Value) Then
        myurl = cell.Value
        xmlhttp.Open "GET", myurl, False
        xmlhttp.send
        xmlresponse.LoadXML (xmlhttp.responseText)
        strRet = xmlhttp.responseText


        StrOut = Right(strRet, Len(strRet) - 40)
        cell.Offset(0, 1).Value = StrOut
    End If
Next


ErrorHandler1:
    StrOut = ""
    Resume Next


End Sub
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
Get a fresh value from where? The code is looking in col D, but the new value is placed in col E.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,747
Office Version
2010
Platform
Windows
I don't follow that. What does "repeats it self" mean?
 

piercet

New Member
Joined
Jul 16, 2018
Messages
3
1. Unit Selection Testing
MainTest_100.100.000
(Note: This test verifies BACnet engineering units can be changed through Tracer TU. When verifying the current
units by copying the addresses in the table below to an Internet Explorer browser, the value to note will be
displayed as “val = (some number)”. This number is found in the BACnet standards guide but I have listed
the numbers in parenthesis under the “Instance Tested” column.
A. Select a BACnet address for each type of sensor from the table below.
HDWA
TemperatureOutdoor Air Temperaturehttp://127.0.0.1:44789/evox/bacnet/2/0/51/117<int href="/evox/bacnet/2/0/51/117" val="64" xmlns:trane="trane:evox" />
PressureEvap Refrig Pressurehttp://127.0.0.1:44789/evox/bacnet/2/0/35/117<int href="/evox/bacnet/2/0/35/117" val="56" xmlns:trane="trane:evox" />
Cooling Capacity / PowerUnit Power Consumptionhttp://127.0.0.1:44789/evox/bacnet/2/0/53/117<int href="/evox/bacnet/2/0/53/117" val="48" xmlns:trane="trane:evox" />
FlowApprox Evap Water Flowhttp://127.0.0.1:44789/evox/bacnet/2/0/19/117<int href="/evox/bacnet/2/0/19/117" val="89" xmlns:trane="trane:evox" />
B. Click on the URI address in the table for the chiller being tested to the Internet Explorer browser.
C. Verify the current engineering units.
D. Record the values that display in the browser window.
Expected results:
q Verify that the values correspond with the expected engineering units
of Deg C, kPa, kW, and %. See the following table.
Type of SensorUnits
TemperatureDeg C (62)
Deg F (64)
PressurekPa (54)
PSI (56)
Cooling Capacity/PowerkW (48)
Tons (52)
FlowL/m (88)
U.S. G/m(89)
E. In Tracer TU, Utilities àController àController Settings àProtocol, change the units.
F. Save the configuration.Pause
Expected results:
q Verify that the chiller saves the configuration successfully.
(Note: For HDWA, the Unit Power Consumption units remain in kW.)
G. Select a BACnet address for each type of sensor from the table below.
RTAE / HDWA / CTV /
TemperatureOutdoor Air Temperaturehttp://127.0.0.1:44789/evox/bacnet/2/0/51/117<int href="/evox/bacnet/2/0/51/117" val="64" xmlns:trane="trane:evox" />
PressureEvap Refrig Pressurehttp://127.0.0.1:44789/evox/bacnet/2/0/35/117<int href="/evox/bacnet/2/0/35/117" val="56" xmlns:trane="trane:evox" />
Cooling Capacity / PowerUnit Power Consumptionhttp://127.0.0.1:44789/evox/bacnet/2/0/53/117<int href="/evox/bacnet/2/0/53/117" val="48" xmlns:trane="trane:evox" />
FlowApprox Evap Water Flowhttp://127.0.0.1:44789/evox/bacnet/2/0/19/117<int href="/evox/bacnet/2/0/19/117" val="89" xmlns:trane="trane:evox" />

<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>
89

<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,100,064
Messages
5,472,249
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top