Excel and Atom Feeds

arkusM

arkusM

Well-known Member
Joined
Apr 12, 2007
Messages
560
Does anyone know how to access Atom data feeds through Excel? The data I am trying to get at is on a secure server, so I also need authentication, but I am having trouble finding examples through a google search.

Cheers
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Accessing and reading ATOM feed example, it's the same as for other XML feeds:

Code: 
Sub ReadAtomXML()
Dim xmlReq As ServerXMLHTTP60
Dim xmlDoc As MSXML2.DOMDocument
Dim root As IXMLDOMElement, myNode As IXMLDOMElement
Dim i As Long, n As Long

Set xmlReq = New ServerXMLHTTP60
xmlReq.Open "GET", "http://allboxing.ru/atom.xml"
xmlReq.send

If xmlReq.Status <> 200 Then
    MsgBox xmlReq.statusText: Exit Sub
Else
    Set xmlDoc = New MSXML2.DOMDocument
    xmlDoc.LoadXML xmlReq.responseText
End If

If xmlDoc.parseError.ErrorCode <> 0 Then
    MsgBox xmlDoc.parseError.reason
Else
    Set root = xmlDoc.DocumentElement
End If

For i = 0 To root.getElementsByTagName("item").Length - 1
    Set myNode = root.getElementsByTagName("item").Item(i)
    For n = 0 To myNode.ChildNodes.Length - 1
        Debug.Print myNode.ChildNodes(n).Text
    Next n
Next i

End Sub

As for logging in to a secure website, it's hard to give a direction without looking at a particular website. I usually do this kind of task by using the same ServerXMLHTTP60 as you see above to send a POST request with username/email and password, then get a session ID from the server response; then attaching the session ID in ServerXMLHTTP60.setRequestHeader method to keep the session so that the web server won't redirect you back to the login page.
 
Last edited:
Upvote 0
poolhall said:
Accessing and reading ATOM feed example, it's the same as for other XML feeds:

As for logging in to a secure website, it's hard to give a direction without looking at a particular website. I usually do this kind of task by using the same ServerXMLHTTP60 as you see above to send a POST request with username/email and password, then get a session ID from the server response; then attaching the session ID in ServerXMLHTTP60.setRequestHeader method to keep the session so that the web server won't redirect you back to the login page.
Click to expand...

Wow, you make it sound so simple, but I am out of my depth here for sure.
This is the documentation that is provided not sure if it is helpful
[h=2]Authentication[/h]Web service requests must be made over HTTPS. Requests made to web services over HTTP will be redirected to HTTPS.
Web service requests are authenticated using HTTP Basic Authentication. Typical basic authentication interaction involves the server "challenging" any un-authenticated requests that do not include the "Authorization" header on the request. However, NGX web service requests that do not include a basic authentication "Authorization" header will not receive a challenge response. Instead such a request is redirected to a form based login page. This behaviour exists to force human users using a web browser to authenticate using form based authentication.

In order for your web service client to authenticate using basic authentication, the first HTTPS request must include the "Authorization" header specifying the account credentials to authenticate with. The header for a such a request would look like this:
GET /ngxcs/trade.xml HTTP/1.0 Host: secure.ngx.com Authorization: Basic QWxhZGRpbjpvcGVuIHNlc2FtZQ== </PRE>

Alternatively, a request can be made that forces the basic authentication challenge to occur by adding the request parameter "authType=basic". The header for a such a request would look like this:
GET /ngxcs/trade.xml?authType=basic HTTP/1.0 Host: secure.ngx.com </PRE>
This request would result in a basic authentication challenge that your web service client could then respond to.
[h=2]Atom Feeds[/h]
NGX also provides access to data through Atom feeds. To access the Atom feed for a resource list use the ".atom" extension in the url. For example, to access the Atom feed of trade data use the url: https://secure.ngx.com/ngxcs/trade.atom
Click to expand...
Not sure if you can help me beyond this, but thanks what you have provided I appreciate it.
Thanks for your help
 
Upvote 0
Without login credentials, I cannot test it if this is successful, you please do it

This requires VBE references set to Microsoft XML, v6.0 and Microsoft Forms 2.0 Object Library.
Put the code into a standard module. In sub Test_login change "mypassword123" and "myusername123" to your actual login name and password and run "Test_login".
The last sub will put a server response (webpage source code) to the clipboard. Paste it into a text editor and see if the login attempt was successful. Please let me know

Code: 
Dim SessionID As String, LtValue As String


Public Type Ngx_Session
    SessionID As String
    FormActionURL As String
    LtValue As String
End Type


Sub Test_login()
Dim mySession As Ngx_Session
    Ngx_EstSession mySession
    Debug.Print mySession.FormActionURL
    Debug.Print mySession.SessionID
    Debug.Print mySession.LtValue
    Ngx_Login mySession, "myusername123", "mypassword123"
End Sub


Private Sub Ngx_EstSession(sess As Ngx_Session)
Dim xmlReq As ServerXMLHTTP60
Dim servResp As String
Dim pos As Long


Set xmlReq = New ServerXMLHTTP60


xmlReq.Open "GET", "https://secure.ngx.com/sso/login?service=https%3A%2F%2Fsecure.ngx.com%3A443%2Fngxcs%2Fj_spring_cas_security_check%3Bjsessionid%3D8A50ADC364C66CD4CF0CADAA82C79866"
xmlReq.setRequestHeader "User-Agent", "Excel 2002 :)"
xmlReq.setRequestHeader "Connection", "keep-alive"
xmlReq.setRequestHeader "Referer", "https://secure.ngx.com/"
xmlReq.send


If xmlReq.Status <> 200 Then MsgBox "Error occured: " & xmlReq.statusText: Exit Sub


servResp = xmlReq.responseText


sess.SessionID = Split(Replace(xmlReq.getResponseHeader("Set-Cookie"), "JSESSIONID=", ""), ";")(0)


pos = InStr(1, servResp, "action=""", 1) + 8
sess.FormActionURL = "https://secure.ngx.com" & Mid(servResp, pos, InStr(pos, servResp, Chr(34), 1) - pos)
pos = 0


pos = InStr(1, servResp, "name=""lt"" value=""", 1) + 17
sess.LtValue = Mid(servResp, pos, InStr(pos, servResp, Chr(34), 1) - pos)


End Sub


Private Sub Ngx_Login(sess As Ngx_Session, usrName As String, pwd As String)
Dim xmlReq As ServerXMLHTTP60
Dim postBody As String
Dim SessionID As String


postBody = "username=" & usrName & "&" & _
            "password=" & pwd & "&" & _
            "lt=" & sess.LtValue & _
            "&_eventId=submit&submit=Login"


Set xmlReq = New ServerXMLHTTP60
xmlReq.Open "GET", sess.FormActionURL
xmlReq.setRequestHeader "User-Agent", "Excel 2002 :)"
xmlReq.setRequestHeader "Connection", "keep-alive"
xmlReq.setRequestHeader "Referer", "https://secure.ngx.com/sso/login?service=https%3A%2F%2Fsecure.ngx.com%3A443%2Fngxcs%2Fj_spring_cas_security_check%3Bjsessionid%3D8A50ADC364C66CD4CF0CADAA82C79866"
xmlReq.setRequestHeader "Cookie", "JSESSIONID=" & sess.SessionID
xmlReq.send postBody


If xmlReq.Status <> 200 Then MsgBox "Error occured: " & xmlReq.statusText: Exit Sub


servResp = xmlReq.responseText


Dim DataObj As New MSForms.DataObject
DataObj.SetText servResp
DataObj.PutInClipboard


End Sub
 
Last edited:
Upvote 0
Thanks for your effort. I'll have to try this opn Monday a this point. But I will definetly let you know, thank you so much..
 
Upvote 0
Ok So things freeded up and I gave it a go..

I get this Run-time error message:
-2147012867 (80072efd) a connection with the server could not be established.
at this line:

Rich (BB code): 
 Private Sub Ngx_EstSession(sess As Ngx_Session)
    .....
    xmlReq.send

Could this be a internal network issue?
 
Last edited:
Upvote 0
I suppose, it is possible. Could a company firewall or, possibly, your antivirus software blocking this request? Check that, please.

Also make sure that the URL in
xmlReq.Open method was copied properly. Try to change it to ​
https://www.google.com/ , will this work?
​
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
How to *generate* data for a waterfall chart (not the chart itself)
Replies
0
Views
139
trippknightly
T
N
  • Solved
Removing 0 from number in specific scenarios
Replies
3
Views
134
Fluff
Fluff
H
  • Question
'How to' question about a Dashboard
Replies
5
Views
307
etaf
etaf
S
  • Solved
Excel formula that purposefully always makes cell $ zero ?
Replies
7
Views
55
Joe4
Joe4
K
  • Question
Translate texts in Excel
Replies
2
Views
92
John_w
J

Forum statistics

Threads
1,216,058
Messages
6,128,538
Members
449,456
Latest member
SammMcCandless

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top