What can you learn here?
- basic authentication
- xmlhttp
- cBrowser
Quick examples get it now
Basic Authentication over Http is implemented as an option in the cBrowser class. It took me a while to track this down, but I eventually found out how to and wish to acknowledge http://pastie.org/1192157 for the final story on how to do this
What to download
All the examples contain all the classes needed for them to work and all projects can be found here.
If you just want the main utility classes used throughout this site, in the Downloads section cDataSet.xlsm includes everything you need including some examples.
What is Http Authentication?
This is a standard way , supported by all browsers, that a username and password can be supplied to a web site that needs it. The provided credentials are encoded using Base64, but this can be easily decoded, so it is not a particularly secure method – even though it is very common.
Why do we need it here?
Although most rest Apis use a developer key or some other method, some sites require basic http authentication. In order for the Rest to Excel library to be able to access these sites, I had to build basic authentication into the cBrowser class.
Specify userName and password
The cRest class now has a couple of addition arguments to the .init() method that allow username and password to specified. If these are present, then the rest session will commence with an authorization attempt. Here’s an example calling a library entry that needs a username and password.
<pre>Public Sub testneedsPass()
Dim cr As cRest
Set cr = restQuery("needsPass", "needsPass", _
, , , , , True, , , True, , "someuser", "somepassword")
End Sub</pre>
The code
Actually, although this was hard to track down, the code is rather trivial – the section in blue is all that was needed to enable it. In summary, and encoded version of the user and password combination is inserted into the http request header to accomplish the authentication.
<pre>ublic Function httpGET(fn As String, _
Optional authUser As String = vbNullString, _
Optional authPass As String = vbNullString) As String
pHtml = fn
Dim oHttp As Object
Set oHttp = CreateObject("Microsoft.XMLHTTP")
Call oHttp.Open("GET", pHtml, False)
If (authUser <> vbNullString) Then
' need to do basic authentication
' acknowledgement to http://pastie.org/1192157
oHttp.SetRequestHeader "Content-Type", "application/json"
oHttp.SetRequestHeader "Accept", "application/json"
oHttp.SetRequestHeader "Authorization", "Basic " + _
Base64Encode(authUser + ":" + authPass)
End If
Call oHttp.Send("")
httpGET = oHttp.ResponseText
Set oHttp = Nothing
End Function</pre>
Base64 encoding
I also needed a Base64 encoding algorithm which I found from the same source
<pre>Function Base64Encode(sText)
Dim oXML, oNode
Set oXML = CreateObject("Msxml2.DOMDocument.3.0")
Set oNode = oXML.createElement("base64")
oNode.DataType = "bin.base64"
oNode.nodeTypedValue = Stream_StringToBinary(sText)
Base64Encode = oNode.Text
Set oNode = Nothing
Set oXML = Nothing
End Function
'Stream_StringToBinary Function
'2003 Antonin Foller, http://www.motobit.com
'Text - string parameter To convert To binary data
Function Stream_StringToBinary(Text)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.WriteText Text
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeBinary
'Ignore first two bytes - sign of
BinaryStream.Position = 0
'Open the stream And get binary data from the object
Stream_StringToBinary = BinaryStream.Read
Set BinaryStream = Nothing
End Function
'Stream_BinaryToString Function
'2003 Antonin Foller, http://www.motobit.com
'Binary - VT_UI1 | VT_ARRAY data To convert To a string
Function Stream_BinaryToString(Binary)
Const adTypeText = 2
Const adTypeBinary = 1
'Create Stream object
Dim BinaryStream 'As New Stream
Set BinaryStream = CreateObject("ADODB.Stream")
'Specify stream type - we want To save text/string data.
BinaryStream.Type = adTypeBinary
'Open the stream And write text/string data To the object
BinaryStream.Open
BinaryStream.Write Binary
'Change stream type To binary
BinaryStream.Position = 0
BinaryStream.Type = adTypeText
'Specify charset For the source text (unicode) data.
BinaryStream.Charset = "us-ascii"
'Open the stream And get binary data from the object
Stream_BinaryToString = BinaryStream.ReadText
Set BinaryStream = Nothing
End Function</pre>
Take a look at Rest to Excel library for more on this topic