Connect to Cloudant via Excel Macro

Question

I want to connect to a Cloudant database from an Excel Macro that I have written. The macro would essentially need to find data in a specific field searching on another field. Example: For ID="2", return data in field "Name". Does anyone know how this is possible?

Thanks.

Edit: I have posted an answer to my own question. The answer I posted gets all docs from a specified database. From here you can query, etc. to get the specific data you are looking for. You can also use an excel macro JSON parser, found here to help sort through the data. The Base64Encoder I used can be found here.

score 2 · Answer 1 · answered Feb 23 '15 at 17:31

2

From the documentation:

All requests to Cloudant go over the web, which means any system that can speak to the web, can speak to Cloudant. All language-specific libraries for Cloudant are really just wrappers that provide some convenience and linguistic niceties to help you work with a simple API.

Now, to your question:

Does anyone know if/how this is possible?

VBA capable of sending and receiving HTTP requests, ergo, this is possible using Excel (or any other application that can run VBA).

answered Feb 23 '15 at 17:31

David Zemens

53,033
11
81
130

Do you have an example? I'm unsure of how to do that. – Julie Feb 23 '15 at 21:08
I've never heard of Cloudant until you asked this question today, so I would not be able to assist you with any specifics on that end. What, precisely are you asking about now? Because your edit suggests you're looking for someone to teach you everything from scratch. Do you already know VBA? – David Zemens Feb 23 '15 at 21:22
I can write the entire macro aside from the actual database connection. I've written plenty of macros but never connecting to a Cloudant Database. I've looked at some [examples](http://stackoverflow.com/questions/1120674/accessing-sql-database-in-excel-vba) of connecting to a SQL database but I don't know how to relate this to Cloudant and I don't know how to do For ID="2", return data in field "Name" because I am unfamiliar with database things. – Julie Feb 23 '15 at 21:39
Well, they have a whole website devoted to documentation, it's a bit thin but I would start there, or [here](http://stackoverflow.com/questions/28339910/how-can-i-translate-my-sql-queries-to-cloudant-couchdb) – David Zemens Feb 23 '15 at 22:04
[This](http://stackoverflow.com/questions/28339910/how-can-i-translate-my-sql-queries-to-cloudant-couchdb) might be useful. You'll need to connect to the database and then execute the equivalent of a SQL select statement: `Select [Name] From Table_Name Where [ID] = 2`. – David Zemens Feb 23 '15 at 22:07

score 2 · Accepted Answer · answered Mar 19 '15 at 00:18

In case anyone else ever searches for this, I figured I would upload an actual response to this question (nearly a month later) rather than just a "yes, this is possible".

Since Cloudant requires Basic Auth, the way that I have found to do this is below:

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Dim response As String
    'Sameple URL: https://ibmcds.cloudant.com/sandbox/_all_docs
URL = "https://" + CloudantUsername + ".cloudant.com/" + DatabaseName + "/_all_docs"
With objHTTP
    .Open "GET", URL, False
    .SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    .SetRequestHeader "Content-Type", "application/json"
    .SetRequestHeader "Accept", "application/json"
    .SetRequestHeader "Authorization", "Basic " + Base64Encode(CloudantUsername + ":" + CloudantPassword)
    .Send ("")
End With
response = objHTTP.responseText

In this example, the Base64Encode function just encodes the given string. You can use any Base64 Encoder for this.

And if you want to check the status of the request you can use:

If objHTTP.Status = 200 Then
    response = objHTTP.responseText
    MsgBox "This request is valid.", vbOKOnly
Else
    MsgBox "This request is not valid.", vbOKOnly
End If

Or something similar.

I hope this helps anyone else who may be looking to do something like this.

Connect to Cloudant via Excel Macro

2 Answers2