How to clear excel memory

P

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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Get a fresh value from where? The code is looking in col D, but the new value is placed in col E.
 
Upvote 0
I don't follow that. What does "repeats it self" mean?
 
Upvote 0
[TABLE="width: 1753"]
<colgroup><col><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 3"] [TABLE="width: 1369"]
<colgroup><col><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]1. Unit Selection Testing[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MainTest_100.100.000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"](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.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]A. Select a BACnet address for each type of sensor from the table below.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]HDWA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Temperature[/TD]
[TD]Outdoor Air Temperature[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/51/117[/TD]
[TD]<int href="/evox/bacnet/2/0/51/117" val="64" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pressure[/TD]
[TD]Evap Refrig Pressure[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/35/117[/TD]
[TD]<int href="/evox/bacnet/2/0/35/117" val="56" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cooling Capacity / Power[/TD]
[TD]Unit Power Consumption[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/53/117[/TD]
[TD]<int href="/evox/bacnet/2/0/53/117" val="48" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Flow[/TD]
[TD]Approx Evap Water Flow[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/19/117[/TD]
[TD]<int href="/evox/bacnet/2/0/19/117" val="89" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]B. Click on the URI address in the table for the chiller being tested to the Internet Explorer browser.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]C. Verify the current engineering units.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]D. Record the values that display in the browser window.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Expected results:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]q Verify that the values correspond with the expected engineering units
of Deg C, kPa, kW, and %. See the following table.[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Type of Sensor[/TD]
[TD]Units[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Temperature[/TD]
[TD]Deg C (62)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Deg F (64)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pressure[/TD]
[TD]kPa (54)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]PSI (56)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cooling Capacity/Power[/TD]
[TD]kW (48)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tons (52)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Flow[/TD]
[TD]L/m (88)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]U.S. G/m(89)[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]E. In Tracer TU, Utilities àController àController Settings àProtocol, change the units.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]F. Save the configuration.[/TD]
[TD][/TD]
[TD]Pause[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Expected results:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]q Verify that the chiller saves the configuration successfully.
(Note: For HDWA, the Unit Power Consumption units remain in kW.)[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]G. Select a BACnet address for each type of sensor from the table below.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]RTAE / HDWA / CTV /[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Temperature[/TD]
[TD]Outdoor Air Temperature[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/51/117[/TD]
[TD]<int href="/evox/bacnet/2/0/51/117" val="64" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Pressure[/TD]
[TD]Evap Refrig Pressure[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/35/117[/TD]
[TD]<int href="/evox/bacnet/2/0/35/117" val="56" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Cooling Capacity / Power[/TD]
[TD]Unit Power Consumption[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/53/117[/TD]
[TD]<int href="/evox/bacnet/2/0/53/117" val="48" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Flow[/TD]
[TD]Approx Evap Water Flow[/TD]
[TD]http://127.0.0.1:44789/evox/bacnet/2/0/19/117[/TD]
[TD]<int href="/evox/bacnet/2/0/19/117" val="89" xmlns:trane="trane:evox" />[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]89[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,817
Messages
6,181,144
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top