Excel keeps using 25% CPU after XML import

[stian]

So I finnaly got a workaround for importing data from my Web Service to Excel.
This is probably a veeery bad way of doing it.

It is currently working, but after I have imported the data I see from the Task Manager that Excel keeps using 25% CPU. And it also sometimes lags and mini freezes in Excel.
If I delete the row that was juts imported it immediately goes down to 0% CPU use and the lagg/freezes are gone.

I really have no clue what's cuasing this. Any ideas what can cause this?

Here is some of the code I am using to get the data. Tried adding comments so its understandable.

Beginning:

<rs>Sub SQLcall(dato_f As String, dato_t As String, x As String)
Dim str_usrSQL As String
Dim sURL As String
Dim sEnv As String
Dim dato_fra_fixed, dato_til_fixed As String
Dim xmlhtp As New MSXML2.XMLHTTP
Dim Resp As New MSXML2.DOMDocument
Dim xx, y, z, rr As Integer
Dim t, f As String

Resp.async = False
Resp.validateOnParse = False

dato_fra_fixed = Functions.Fix_dato(dato_f)
dato_til_fixed = Functions.Fix_dato(CStr(CDate(dato_t) + 1))

<rs>
<rs>Next the part where I get the XML
I found out I could change the methode I used, which was this:

sURL = " removed "
 
sEnv = "<!--?xml version='1.0' encoding='utf-8'?-->"
sEnv = sEnv & "<soap:envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">"
sEnv = sEnv & "  <soap:body>"
sEnv = sEnv & "    <sql_query_dbhtl xmlns="http://tempuri.org/"> "
sEnv = sEnv & "      <str_usrsql>'proc=s;para1=alle;para2=" & dato_fra_fixed & ";para3=" & dato_til_fixed & ";para4=" & k & ";'</str_usrsql>"
sEnv = sEnv & "    </sql_query_dbhtl>"
sEnv = sEnv & "  </soap:body>"
sEnv = sEnv & "</soap:envelope>"

With xmlhtp
    .Open "post", sURL, False
    .setRequestHeader "Host", " removed "
    .setRequestHeader "Content-Type", "text/xml; charset=utf-8"
    .setRequestHeader "soapAction", "http://tempuri.org/SQL_Query_dbhtl"
    .send sEnv
End With
Dim row As Integer


Resp.LoadXML xmlhtp.responseText

With just this:

xmlhtp.Open "GET", "http://1.1.1.1/webservice_db/service.asmx/SQL_Query_dbhtl?str_usrSQL='removed_string'", False
xmlhtp.send

Resp.LoadXML xmlhtp.responseText

But unfortunatly it didnt help.

Then its the crazy part where I go through the XML:

xml = Split(Resp.Text, "<rs>")
xml_string = xmlhtp.responseText


row = UBound(xml) 'number of row
count = row - 1
xx = 0 'row counter 
rr = 13 'start row

' Crazy method of splitting XML string and printing the data I want

Do While row > xx
    t = xml(xx)
    
    col = Split(t, "xx")
    field = UBound(col) 'inner rows count
    y = 1
    z = 1
    
    Do While field > y
        u = col(y)
        te = Right(u, Len(u) - 3) 'remove first 3 characters
        If (te = "xml:space=""preserve""> <!--") Then 'for the blank fields
            Sheets(1).Cells(rr, z).value = ""
            z = z + 1
            y = y + 2
        Else
            u = Trim(Replace(u, vbLf, ""))
            f = Right(u, Len(u) - 3) 'remove first 3 characters
            f = Left(f, Len(f) - 2) 'remove last 2 characters
            Sheets(1).Cells(rr, z).value = Trim(f) 'print f to cell
            z = z + 1
        y = y + 2
        End If
    Loop
rr = rr + 1
xx = xx + 1
Loop

Which works fine.

I have also made some interesting discoveries.
I have a method that locks and unlocks the workbook since i have som rectangles and other graphic elements you shouldnt be able to move.

The methods look like this:

Function WB_Lock()
ActiveSheet.Protect DrawingObjects:=True, Contents:=False, Scenarios:=False, userinterfaceonly:=True
End Function

Function WB_UnLock()
ActiveSheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:=False, userinterfaceonly:=False
End Function

What I basicly have found out is that if the sheet is unlocked all the time and I manually lock it like this:
http://i.imgur.com/xORJjLS.jpg
The CPU goes to 0

If i lock it automaticly with the method it still stands at 25%

When Excel is using 25% CPU after the method is run normaly and I click the "protect sheet" button, the CPU goes down to 0 when the dialog is open and then back up to 25% when I close it :S

So if I comment out the lock and unlock method then unprotect the sheet and run the code. The CPU goes to 25% after its finished but when i Lock it manually with the "proect sheet" button it goes to 0% CPU and stays there.

This is so wierd. Never seen anything like it. And it really is a deal breaker with my program untill I get this fixed. So all help is really appreciated.

Thank you for reading and I hope you can help me </rs></rs></rs>

 

[VoG]

Cross posted Excel keeps using 25% CPU after XML import

Please read Excelguru Help Site - A message to forum cross posters