NebulaXChange : Excel to MV and beyond

A long time ago there was a product called CrowFlight that retrieved data into Excel from MV. Some companies still have it and I occassionally get a call from someone looking to replace it. Well… I now have two ways of using Excel formulas to call into MV, and this functionality can be combined with NebulaXLite.

UPDATE: If you haven’t seen them, this page and this one also discuss this topic.

So the spreadsheets that you create from BASIC using NebulaXLite won’t necessarily need data. You will be able to put formulas in your spreadsheet which will reach back to the server for current data. I don’t have a firm API worked out but the formulas will be something like this:

• =Execute(Database, Function, DataIn, Options)
• =GetString(Database, DataIn, Options)
  DataIn has File, ID, Atb, Value, Subvalue
• =GetSchema(Database, DataIn, Options)
  DataIn has File, ID, DictName
• =GetDate(Database, Function, DataIn, Options)
• =GetNumber(Database, Function, DataIn, Options)
• =GetList(Database, Function, DataIn, Options)
  DataIn has File, SSelect Statement or similar query details
  Data returned is a range of values rather than a single value

At the moment I’m working toward offering this as a new product, NebulaXChange to compliment NebulaXLite. In anticipation of the inevitable question, I’ve considered adding Excel-to-MV functionality as an enhancement to XLite but then XLite wouldn’t be so "lite" anymore. No, these will be two different products that do different things. We’ll probably offer one at a discount when someone gets the other.

There are two ways to make this happen and I’ll give one of them away here. If you want a more elegant supported version then you can get XChange when it comes out.

In the first part of this functionality, we create a custom Function in code which can be used as =Formula() in a cell. The method I’ll document here uses a VBA macro for the code but XChange will use a .NET assembly. VBA macros must be manually entered and maintained for every user and they’re subject to MS Office security issues. A .NET assembly can be added more elegantly through Tools>Options>Addins, and the user never sees code.

Executing VBA code from Excel formulas is easy: Create a VBA Macro as a Public Function that returns a string. When you open a cell and hit the ‘=’ sign you won’t see any tooltip assistance but if you enter the formula correctly it will work. If you open the formula dialog by pressing the "fx" button you’ll see the function names under User Defined Functions. The XChange functions will also allow the user to build the formula through the formula Wizard.

Assuming you have a VBA macro that you can execute with =FunctionName(), we now need to get from that function to MV, or more specifically from any VBA script to any remote MV system. Opinions on how to do communications vary greatly and you can use a different solution than what I document here, but I like the idea of using web services. An HTTP call is made using SOAP to a web server. That web server connects into MV to execute some program or command-line statement. The data is returned to the web server. The web server wraps the data in XML and returns it to the client. The client VBA code returns it back to the Excel cell through the formula. Voilà|! To do this magic, we hear terms like XMLHTTP and MS XML and SOAP, etc. This allows any Excel user to use any MV server regardless of whether they’re local or remote. The access from the web server to MV is again a matter of choice, but I personally prefer to use mv.NET for many reasons.

Another reason why I’ve chosen to do this with web services is that it takes all of the database access code out of the client/VBA code, and puts it on the back-end. This allows the developer to pull data from any database like Oracle, or even to call other web services to aggregate data from MV, Oracle, Amazon, Google, etc. The developer is free to change back-end technologies without any changes to existing clients, which is very important when end-users have existing spreadsheets that they rely on for data, and you certainly can’t ask them to re-work their spreadsheets just because you want to change your database code.

The solution below uses web services and mv.NET. I’m not providing all of the code but if you have mv.NET, check out the video I created for using web services with mv.NET, and know something about VBA you should be able to do all of this on your own.

Open Excel and the VBA Editor. Create a new module for code, then go to Tools and set a reference to the Microsoft SOAP Type Library. My file is located here:
C:\Program Files\Common Files\MSSoap\Binaries\mssoap1.dll
Now add the following code to your module:

Option Explicit
Dim Result As String
Dim Err As String
Const Delim As String = "{~}"  ‘ unique delimiter for transport

Public Function GetMV( _
  Database As String, Operation As String, Options As String) as String
    Dim Service As MSSOAPLib.SoapClient
    Dim Info As String ‘ contains result from web service
    Dim aInfo() As String
    ‘ data passed here delimited by CRLF
    aInfo = Split(Options, vbCrLf)
    ‘ convert to unique delimiter to avoid SOAP issues
    Options = Join(aInfo, Delim)
    Set Service = New MSSOAPLib.SoapClient
    ‘ URL and service names can be set as desired
    Service.MSSoapInit _
       http://localhost/WS/Service1.asmx?wsdl, _
       "Service1", "Service1Soap"
    ‘ just pass data to service, result comes back in Info
    Info = Service.GetMV(Database, Operation, Options)
    Set Service = Nothing  ‘ avoid memory leaks
    aInfo = Split(Info, Delim)  ‘ remove custom delimiters
    Result = aInfo(4)  ‘ real result is in Atb 4 of response
    Err = aInfo(3) ‘ Error was put in Atb 3 if something failed
    return Result  ‘ this is the value passed back to the cell
End Function

So far that gets you from something like the following formula to your web server:
=GetMV("mydatabase", "execute", "GET.RESULT" & vbCRLF & "1")

On the web server, create a web service using whatever tools you want, including Java with Tomcat over apache, PHP, etc. Expose a service called Service1 with a function/method called GetMV.

To get from there to MV, again use Java, PHP, D3 Class Libraray, UniObjects, JRCS, or whatever tools you prefer. I use mv.NET and documented the entire process in the aforementioned video.

Another reason why I don’t want to use the Microsoft SOAP Toolkit or similar libraries from Microsoft is that they have all become unsupported since .NET is the new successor. I’m going with the flow and using the latest tools. Development for MS Office using .NET is much better than it was just a few years ago because Microsoft has spent a lot of time to make the connectivity much more seamless and accessible to developers.

Once I made the connections using the above code and more code that I wrapped around it, I can’t tell you how tickled I was when I started seeing lists of data getting pulled from my database. However, thinking about what a hassle it was for me to do this I knew I didn’t want to expose other developers to this, and certainly not end-users. So I re-wrote the client in a .NET assembly with C#, set that as my Automation Addin, and I got the same connectivity with no messy VBA scripting. Once I got the web service done with access into MV, it occurred to me as mentioned above that this code which is to become NebulaXChange can be used for completely non-MV applications too. I know there are other solutions out there for this but it can’t hurt to add yet one more.

Something else that should be of interest. The code that I use for processing formula/functions is so thin that it can be used from any MS Office application. This will allow NebulaXChange to be used with Word (I get requests for that all the time), Outlook, even Power Point. LOL – can you imagine a PPT presentation where you don’t need to hard-code your company annual sales or number of offices for presentations every year, you just put in a macro and your company status is automatically refreshed for every presentation. I don’t know who would want to do that but pointing this out might wake up people falling asleep in your presentations.

Stay tuned for more info on NebulaXChange – please feel free to comment on this article below, and if you’d like to discuss NebulaXChange or using these products together, please visit our new web forum.