Hi.

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 so if anyone know of a better way I am glad to hear it. (Didnt work to use the normal child method since the whole result from the XML comes in one big mash in a child node)

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.

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

Beginning:
----------------------------------
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))
----------------------------------

Next the part where I get the XML
I found out I could change the methode I used, which was this:
----------------------------------
sURL = " removed "

sEnv = ""
sEnv = sEnv & ""
sEnv = sEnv & " "
sEnv = sEnv & " "
sEnv = sEnv & " 'proc=s;para1=alle;para2=" & dato_fra_fixed & ";para3=" & dato_til_fixed & ";para4=" & k & ";'"
sEnv = sEnv & " "
sEnv = sEnv & " "
sEnv = sEnv & ""

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, "")
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"">
----------------------------------

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:
-- See attached file
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.