| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Angie Akachi
0
   |
Posted - 2013.04.30 13:42:00 -
[1] - Quote
Hi,
after taking a little break from EvE, i just noticed that my old Excel import sheets are no longer working. The only difference i noticed is that the API Calls now redirects from a Http to Https.
So is this the reason why Excel 2007 is not able to import via the "datasource->XML" option anymore, since i get a "no datasource available error".
Does someone know a "direct" working solution to import EvE API data into Excel, like the AssetList now? I guess importing it via google and than reimporting it to excel might work, but i would like to keep all in Excel if possible.
Thx |
Angie Akachi
0
|
Posted - 2013.05.02 14:27:00 -
[2] - Quote
mhh no one is using Excel 2007 to directly URL import EvE-Api XML data?
What about Excel 2010/2013?
Can someone at least confirm that its possible to import Api-xml data directly via URL into Excel now or did the https change break Excel "compatibility" ?
thx |
Tonto Auri
Vhero' Multipurpose Corp
136
|
Posted - 2013.05.02 15:24:00 -
[3] - Quote
If changing http:// to https:// URL's don't do the trick, it may be that Excel does not recognize the certificate.
The two most common elements in the universe are hydrogen and stupidity.
---áHarlan Ellison |
Angie Akachi
0
|
Posted - 2013.05.03 09:52:00 -
[4] - Quote
Tonto Auri wrote:If changing http:// to https:// URL's don't do the trick, it may be that Excel does not recognize the certificate.
Sounds plausible, but i have no clue how to fix this problem. I found some other Excel related posts and https XML imports, but those posts got no answers/solutions either.
This means that atm, u cant directly use the URL/XML method to get EvE Api data into Excel.
I found 3 workarounds so far:
1) Some complex VBA functions to directly handle the URL/XML import, but so far the ones i tried did not work with https either.
2) Use a browser to get the XML and save/export it and use the local xml files as datasource for excel.
3) Let googledocs import the XML and remap Excel to the googledocs sheet as datasource than.
I don't like any of those workarounds, compared to how it worked before this change, when i could simply hit "refresh" and got my data imported. |
molto scarso
Manson Family
The Retirement Club
1
|
Posted - 2013.05.03 17:13:00 -
[5] - Quote
Hi,
try this
=importXML("https://api.eveonline.com/corp/AccountBalance.xml.aspx?keyID=*******&vCode=*********","/eveapi/result/rowset//row/@balance")
i do import my assets into a spread sheet but i use a small php script to filler it as it contains allot of information after years of playing.
list of all the API functions can be found here
http://wiki.eveonline.com/en/wiki/EVE_API_Character_Contact_Notifications |
Angie Akachi
0
|
Posted - 2013.05.04 11:13:00 -
[6] - Quote
molto scarso wrote:
try this
=importXML("https://api.eveonline.com/corp/AccountBalance.xml.aspx?keyID=*******&vCode=*********","/eveapi/result/rowset//row/@balance")
Thx, but u do realize im talking about Excel and not Googledocs? Excel has no "ImportXML" function, so ofc this does not work.
The only way for Excel 2007-2012 is to use the "Web Import" or "Import XML" functionality.
I did read that Excel 2013 has a new "FilterXML" function, similar to googledocs, but unfortunately i only have 2007 atm.
If this works for u in Excel, can u please post the VBA script that has the "importXML" function, so i can try your function?
Thx |
Victoria Anne
Rage Innovations
Equinox Rising
0
|
Posted - 2013.05.08 07:15:00 -
[7] - Quote
Hi Angie, I am currently making use of Excel 2010 for an industry spreadsheet. There is a way to import the xml into the webpage by inserting it under the Data tab. Basically you do a web query input the http address and it returns the xml data which you then insert into the desired cell that you like and then modify what you would like to show in the resultant pivot table etc.
As my data is based on eve-centrals pricing and I make use of macros to construct the xml query string, I'm not sure if there is an issue re the API and the xml output and importing into excel. As for the VBA script this is what I have;
This is the VBA script that I use to import the data to already defined tables;
ThisWorkbook.XmlMaps(xml_map).AppendOnImport = False
ThisWorkbook.XmlMaps(xml_map).Import (searchUrl)
Where;
'xml_map' is the previously imported xml map name created using the import from web button on the data page (for 2010).
'searchUrl' would be the API url you are using, for mine its the constructed one in the macro
False/True is the logical argument as to whether you want the data appended to the data already in the table or to delete it and fill it from the beginning.
I also did a search for filterxml but it came up with zero in my help files. I'm always looking for better ways to do the importing of data. Unfortunately I don't know anything about SQL and excel gets the job done even if it is painful at times.
Sorry I can't help re if there is a way to replicate the importxml functionality of googledocs so that a simple refresh would update the data. My knowledge of VBA is very basic as I've created my stuff by trial and error, but I hope it helps. |
Angie Chatter
University of Caille
Gallente Federation
0
|
Posted - 2013.05.09 12:41:00 -
[8] - Quote
Victoria Anne wrote:There is a way to import the xml into the webpage by inserting it under the Data tab. Basically you do a web query input the http address and it returns the xml data which you then insert into the desired cell that you like and then modify what you would like to show in the resultant pivot table etc.
Like noted by the OP, the problem is not that u cant import XML in Excel, the problem is that the EvE-API was changed to redirect always to safer "HTTPS" requests. Now if u try to import any EvE-API XML data, u get several errors or are prompt to enter user/password, which wont work either.
So as example u cant import your Char/Corp assetList via the EvE-APi using Excel, without external/custom VBA scripts.
So ofc any normal "HTTP" request will still work, but Excel seem to-be unable to correctly handle HTTPS requests. The excel dataimport window using IE will even show the correct redirected XML site, but will then fail to import it into a actual sheet.
So if u are able to import any EvE-Api XML data into your Excel worksheet, pls post a detailed instruction how u perform this.
Thx |
Aineko Macx
Royal Amarr Institute
Amarr Empire
261
|
Posted - 2013.07.27 05:31:00 -
[9] - Quote
Here is an example of a simple VBA function to get the last known location of a character. To work with VBA and XML you need to activate the Developer ribbon in Excel (under File > Options > Customize Ribbon), and from the VBA Editor window go to Tools > References and check Microsoft XML v3.0.
Quote:Public Function lastKnownLocation(charId As String, keyId As String, vCode As String) As String
Dim xDoc As MSXML2.DOMDocument30
Set xDoc = New MSXML2.DOMDocument30
xDoc.async = False
xDoc.Load ("https://api.eveonline.com/eve/CharacterInfo.xml.aspx?keyid=" & keyId & "&vcode=" & vCode & "&characterID=" & charId)
xDoc.setProperty "SelectionLanguage", "XPath"
lastKnownLocation = xDoc.SelectSingleNode("/eveapi/result/lastKnownLocation").Text
End Function
Use it in a cell like you would other functions. Tested in Excel 2010. |
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |