How to connect Mongodb from Excel

Question

I want to connect to a mongodb database using excel macros, does anybody knows how to acomplish this task?

Answer 1

The Shell Approach

Pretty much anything that interfaces with the Command Line can be accessed with Shell.

Here's a bare-bones example that connects to a running MongoDB instance and prints a query to the Immediate Window. You'll need to add a reference to the Windows Script Host Object Model.

Private Sub Test()      Dim wsh As New WshShell     Dim proc As WshExec     Dim line As String          Set proc = wsh.Exec("mongo")          With proc         .StdIn.WriteLine "use test"         .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"         .StdIn.WriteLine "quit()"                  Do While .Status = WshRunning             line = .StdOut.ReadLine             If line = "Type ""it"" for more" Then                 .StdIn.WriteLine "it"             ElseIf line Like "{*" Then                 Debug.Print line             End If             DoEvents         Loop     End With End Sub 

Just printing the raw JSON strings isn't very exciting or useful, however. You could write your own JSON parser but, for this example, we will use VBA-JSON by Tim Hall (you can find it on GitHub).

At the time of writing, there is one issue with VBA-JSON that has to be tackled when using it to parse strings returned from MongoDB. Any values that contain parentheses, e.g. "_id": ObjectId("..."), will throw an error. A quick and dirty fix for this is to use RegEx to clean the string for the parser. You will need to reference the Microsoft VBScript Regular Expressions 5.5 library for the following function to work.

Private Function CleanString(str As String) As String      Dim temp As String     Dim rx As New RegExp          With rx         .IgnoreCase = True         .Global = True                  .Pattern = "[a-z]*\(" ' Left         temp = .Replace(str, "")         .Pattern = "\)" ' Right         temp = .Replace(temp, "")     End With          CleanString = temp End Function 

We can then parse the JSON returned from MongoDB and add each object to a Collection. Accessing the values becomes quite simple.

Private Sub Mongo()      Dim wsh As New WshShell     Dim proc As WshExec     Dim line As String     Dim response As New Collection     Dim json As Object          Set proc = wsh.Exec("mongo")          With proc         .StdIn.WriteLine "use test"         .StdIn.WriteLine "db.restaurants.find({""address.zipcode"":""10075""})"         .StdIn.WriteLine "quit()"                  Do While .Status = WshRunning             line = .StdOut.ReadLine             If line = "Type ""it"" for more" Then                 .StdIn.WriteLine "it"             ElseIf line Like "{*" Then                 response.Add ParseJson(CleanString(line))             End If             DoEvents         Loop     End With          For Each json In response         Debug.Print json("name"), json("address")("street")     Next End Sub 

... Which will produce the following output from the MongoDB Example Dataset.

Nectar Coffee Shop          Madison Avenue Viand Cafe                  Madison Avenue Don Filippo Restaurant      Lexington Avenue Lusardi'S Restaurant        Second Avenue Due                         Third Avenue Lenox Hill Grill/Pizza      Lexington Avenue Quatorze Bistro             East   79 Street Luke'S Bar & Grill          Third Avenue Starbucks Coffee            Lexington Avenue New York Jr. League         East   80 Street Doc Watsons                 2 Avenue Serafina Fabulous Pizza     Madison Avenue Canyon Road Grill           1 Avenue Sushi Of Gari East          78 Street 

---

Gotchas

• ReadLine and WriteLine are blocking functions.
• The window opened by Exec can't be hidden.

A workaround for both of the above would be to use a two-layer approach, where VBA calls a hidden script using wsh.Run, which then runs the Exec (as well as any other code that interacts with the proc). The downside to this approach is that StdIn (and to an extent StdOut) has to be written to a file.

Answer 2

Simple way is

1. create a C# dll to interact with Mongo db through available c# drivers.
2. Make it Com visible (in Assemblyinfo.cs), build it and register it
3. Go to the excel macro - > visual basic editor
4. Click tools->reference, and select your registered assembly
5. And use it in your VBA, like this.

.

Private Sub CallMongo()     Dim mongoObj As New MyMongoAssembly     mongoObj.AddItem("adas"); End Sub 

thats all..