Bushdoctor Vanderspliff
Omen Industries -Entropy-
0
|
Posted - 2015.11.12 11:31:48 -
[1] - Quote
Good day,
I would like to know how to limit my request for an API asset list to certain stations or systems only.
Currently I am importing my entire asset list in to Excel, and I retrieve information from that list with formulas that have Index & Match commands. The problem is that I'm dealing with a huge asset list and also a huge amount of Index/Match formulas, which results in my Excel file locking up for several minutes after inserting a line or refreshing the asset list.
The command to import my asset list looks as follows:
https://api.eveonline.com/char/AssetList.xml.aspx?keyID=123456&vCode=ABCDEF123456&characterID=123456
Is it possible to have the asset list only dump information about specific systems or stations? For that purpose, is it possible to insert something in the API request like: SystemID=30000142,30002187 If that is possible, how do I inserted it in the API request?
Greets and thanks, Doc. |
Hel O'Ween
Men On A Mission
138
|
Posted - 2015.11.12 17:18:06 -
[4] - Quote
Bushdoctor Vanderspliff wrote: Thank you for the swift answer.
So, there is NO way I can split the Asset list into smaller parts (per system or station), so that I can use Vlookup instead of the Index/Match commands?
Well, there are ways around this. It's just that there's no way of limiting the assets list download.
So you would need to
1) download the XML 2) split it as needed 3) do your VLOOKUP
1) and 2) can be easily done with VBA. As I'm terrible at Excel, I unforutnately can't tell you how 2) needs to done so that it can be used by 3).
As for downloading a XML with VBA. have a look at my Excel VBA sample
Here's the general-purpose XML download method from that sample:
Public Function ewaGetXML(ByVal sURL As String, Optional ByVal sParams As String = vbNullString) As String '------------------------------------------------------------------------------ 'Purpose : Retrieves a XML file from a web service (web server) ' 'Prereq. : Reference to MS XML (6.0), set in menu Extras -> References 'Parameter: sURL - URL to query, i.e. https://api.eveonline.com/server/ServerStatus.xml.aspx ' sParams - Parameters to be passed to the web service ' i.e. keyID=MyAPIKeyID&vCode=MyVCode=&characterID=12345 'Returns : Contents of XML 'Note : - ' ' Author: Hel O'Ween 07.06.2014 ' Source: - ' Changed: - '------------------------------------------------------------------------------ Dim oHTTP As MSXML2.ServerXMLHTTP Dim bolResult As Boolean Dim sHTTPStatus As String, sResult As String Dim lAPIResult As Long, sAPIText As String, lRetry As Long
On Error GoTo GetXMLErrHandler
' Create instance of MS XML parser Set oHTTP = New MSXML2.ServerXMLHTTP ' ** Timeouts ' Set timeouts (in milliseconds) if needed, default timeouts are: ' - ResolveTo: 0 ' - ConnectTo: 60000 ' - SendTo: 300000 ' - ReceiveTo: 4800000 oHTTP.setTimeouts eHTTPTimeouts.ResolveTo, eHTTPTimeouts.ConnectTo, eHTTPTimeouts.SentTo, eHTTPTimeouts.ReceiveTo ' Prepare the HTTP POST command oHTTP.Open "POST", sURL, True
' ** Proxy ' Configure web proxy, if necessary ' specify the proxy server either via its host name or IP address 'oHTTP.setProxy SXH_PROXY_SET_PROXY, my.proxy.tld
' In case the proxy needs authentification ... 'oHTTP.setProxyCredentials MyUsername, MyPassword
' ** Misc. HTTP headers ' * The folowing part sets HTTP headers. ' Be polite and let the server know the name of your tool oHTTP.setRequestHeader "USER-AGENT", "MyTool" ' Required header for letting the server know we're using the HTTP POST ' method to query for information and that we're passing parameters to the ' server oHTTP.setRequestHeader "CONTENT-TYPE", "application/x-www-form-urlencoded" oHTTP.setRequestHeader "CONTENT-LENGTH", CStr(Len(sParams))
' Info headers. CCP requests that you provide contact information via X-headers ' in order to contact you in case you're tool runs 'amok' on their servers. ' They might otherwise just ban your IP address from the API servers oHTTP.setRequestHeader "X-Developer-Contact", "[email protected]" ' Duplicate the tool information oHTTP.setRequestHeader "X-Application-Info", "MyTool" ' Add the necessary parameters for the query ' i.e. keyID=MyAPIKeyID&vCode=MyVCode=&characterID=12345 oHTTP.send sParams
' Asynchronous sending of request to the web service Do While oHTTP.readyState < eHTTPReadyState.Loaded DoEvents
' Count retries so we don't end up in an endless loop lRetry = lRetry + 1 ' Keep waiting for the server's answer oHTTP.waitForResponse eHTTPTimeouts.ConnectTo If lRetry > 5 Then Exit Do End If Loop
lRetry = 0
Do While oHTTP.readyState < eHTTPReadyState.Completed DoEvents
' Count retries so we don't end up in an endless loop lRetry = lRetry + 1 ' Keep waiting for the server's answer oHTTP.waitForResponse eHTTPTimeouts.ReceiveTo
If lRetry > 5 Then Exit Do End If Loop
' Remember the web server's status answer sHTTPStatus = CStr(oHTTP.Status) & vbNewLine & oHTTP.statusText
' We received a OK - 200? If oHTTP.Status = 200 Then
sResult = oHTTP.responseXML.XML ' We receive a response, but is there content? If Len(sResult) Then bolResult = True Else bolResult = False End If Else ' Even though the server returned a status other than OK - 200, it still might ' send an XML response, see https://forums.eveonline.com/default.aspx?g=posts&t=287747&find=unread sResult = oHTTP.responseXML.XML If Len(sResult) Then bolResult = True Else bolResult = False End If
End If
'------------------ GetXMLErrExit:
Set oHTTP = Nothing If bolResult = True Then ewaGetXML = sResult Else ewaGetXML = "No results returned" End If
On Error GoTo 0 Exit Function
'------------------ GetXMLErrHandler:
' ** Do some logging/error reporting here bolResult = False Resume GetXMLErrExit
End Function '============================================================================== ...
EVEWalletAware - an offline wallet manager.
|