These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

Problem retrieving Data from Eve-Central into Excel 2010
Author
Previous Topic Next Topic
White Star Research
Xanadu Atlantis Research Inc.
#1 - 2013-01-31 12:25:34 UTC
Hi everyone,

in the past months i tried to build up some excel sheets (excel 2010) to organize my life in EvE.
I use the EvE API and EVE-Central API, until late 2012 everything worked fine. Then i got a short outtime about 6-8 weeks i was not updating my sheets. Yesterday i restarted my business and looked into my excel sheet updating assets data (worked fine from EvE API) and price-data from EvE-Central. That's where my trouble started.
The XML-fetch does not work anymore.
For EvE API i made a XML connection to external data, this still works fine, but for pricing i use an internal function

Quote:

Public Function xmlQuery(xmlUrl As String, xmlPath As String) As String

Dim xmlHttp
Set xmlHttp = CreateObject("Microsoft.XMLHTTP")
Call xmlHttp.Open("POST", xmlUrl, False)
Call xmlHttp.setRequestHeader("Content-Type", "text/xml")
Call xmlHttp.send

Dim xmlDoc
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"
xmlDoc.LoadXML xmlHttp.responsetext
xmlDoc.setProperty "SelectionLanguage", "XPath"

Set ndOutput = xmlDoc.DocumentElement.SelectSingleNode(xmlPath)
xmlQuery = ndOutput.Text

End Function


I call the function with
xmlQuery("http://api.eve-central.com/api/marketstat?usesystem=30002659&typeid=34";"/evec_api/marketstat/type/sell/min")

My debug confirms the correct data until i send the data via "Call xmlHttp.send".
The response is an internal server error (500) and no data is returned.
There was never any problem regarding this fetch, and in google spreadsheets it is still working fine.
So i am wondering if there was any change to the object type in EvE-Central API, or anywhere else.
As i copied the above function a few months ago from an old forum post (but it still worked these days) i'm not an expert in XML....but my problem is, that it worked fine for some time and suddenly the function broke.
Rita Jita Dodixie
Doomheim
#2 - 2013-01-31 23:25:58 UTC
i had a similar issue not to long ago, although i'm not a programmer the problem was that eve central stop accepting "call"

instead use "get" if my memories servers me correct, of what my programmer friend said.

anyways hope this helps
White Star Research
Xanadu Atlantis Research Inc.
#3 - 2013-02-06 11:00:05 UTC  |  Edited by: White Star Research
Hi,
unfortunately this doesn't help.
Replacing the eve-central http-link with e.b. "www.yahoo.com" results in a correct return, no usable data though, but the connection returns rc 200. Whereas eve-central returns 500 (internal server error).
So something changed in the eve-central api which brok my (perhaps not perfect) xml-data-fetch.
Again, i am no xml expert, and only copied the macro "xmlQuery" from a forum post which worked fine for months, until end of 2012.
Is there anyone outside using eve-central from excel 2010 having similar issues, or perhaps with a working solution ?
Trying to implement all pricing data with web-connection is way to much work, as i get many item prices from various regions/systems this way....

Thats where i took it from

https://forums.eveonline.com/default.aspx?g=posts&t=106833
Lorellei Blackmoon
Blackmoon Cartel
#4 - 2013-02-06 12:00:45 UTC
White Star Research wrote:
Hi everyone,
There was never any problem regarding this fetch, and in google spreadsheets it is still working fine.


What caught my attention here was the fact that it still works on google spreadsheet, but not in Excel - is that correct?
You see, if there was an error in the eve-central API the call would return the same error no matter where you run the call to the API - even in a web browser.
Was there any version update on the software you have in your computer?
Try to run the code in another computer, so we can see if there is any change in code behavior. ;-)
White Star Research
Xanadu Atlantis Research Inc.
#5 - 2013-02-06 17:08:34 UTC
Well, this is a point.
There were several Windows updates since then.
MS Office definition update on 10th of January. And .NET Updates.
But what could've been changed to break the connection ?
Is there any problem with the XML definition, or object type ?
I'm totally inexperienced with this stuff, and i am not really enthusiastic resetting all the updates since then.
You're right, in google it stills works, in excel it doesn't but in google spreadsheet the call is a bit different, as there i can simply use the importXML.
Is there any suggestion to change my excel macro for the xml communication ?
Lorellei Blackmoon
Blackmoon Cartel
#6 - 2013-02-06 18:16:07 UTC
It is a hunch, since I am at work and cannot test it myself, but try to change the line:
Call xmlHttp.Open("POST", xmlUrl, False)
to
Call xmlHttp.Open("GET", xmlUrl, False)

and see if it works.

It seems like Excel is messing up with the call to the Eve-central API, and at a first glance that is the only thing standing out as a potential problem in the code.
Fubar
Eagle Eye Inc.
#7 - 2013-02-06 19:57:06 UTC  |  Edited by: Fubar
White Star Research wrote:

Public Function xmlQuery(xmlUrl As String, xmlPath As String) As String

Dim xmlHttp
Set xmlHttp = CreateObject("Microsoft.XMLHTTP")
Call xmlHttp.Open("POST", xmlUrl, False)
Call xmlHttp.setRequestHeader("Content-Type", "text/xml")
Call xmlHttp.send

Dim xmlDoc
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
xmlDoc.async = "false"
xmlDoc.LoadXML xmlHttp.responsetext
xmlDoc.setProperty "SelectionLanguage", "XPath"

Set ndOutput = xmlDoc.DocumentElement.SelectSingleNode(xmlPath)
xmlQuery = ndOutput.Text

End Function

Remove the line
Call xmlHttp.setRequestHeader("Content-Type", "text/xml")
and you should be fine. Both POST and GET methods work ok without the setRequestHeader.
White Star Research
Xanadu Atlantis Research Inc.
#8 - 2013-02-06 23:31:23 UTC
Well, what can I say ?
THANK YOU !!

Removing the Line was the solution.
Call xmlHttp.setRequestHeader("Content-Type", "text/xml")

So what changed to make this statement to break the call ?
But once again, many many thanks!
Fubar
Eagle Eye Inc.
#9 - 2013-02-07 03:15:33 UTC
White Star Research wrote:
So what changed to make this statement to break the call ?

Could of changed when they enabled CORS for all of the APIs. But I don't know for certain.