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.

3 thoughts on “NebulaXChange : Excel to MV and beyond

    • Hi Tony:This is a good idea. I agree that for many reasons mv.NET is a good product to use with this product.What i do not have so clear is to use a web service. If we are talking about Excel (Word, PPT, Outlook, etc.) we are talking about an Office environment. I agree that VBA (even with XLA so the users do not need to do anything) is not an option. The product must be in the .NET framework. For that i thonk it will be better to use VSTO. Actually VSTO is the Office .NET devolopment product. Has you explore that way?joseba

    • VSTO will only help on the client. Some method is still required to get from the client to the server. I could go direct from Excel/VSTO into MV via a custom telnet component but then I would be responsible for coding/managing communications. This product focuses on Excel integration, I don’t want to have to maintain communications code. As an application developer using mv.NET I can focus on the task at hand and I don’t need to worry about the communications at all. From the end-user perspective, most client/server products in our industry only work on an intranet precisely because they use some communications component that relies on access to a local hostname or IP address. With web services and mv.NET, anything I write will work on the LAN or over the internet. For LAN-only usage, if I use VSTO with mv.NET, I would have to install mv.NET client-side libraries. That’s another technical issue that I don’t want to deal with.

      Given all of these considerations I think I’ve chosen the right combination of technologies.

    • The web service solution/idea is a great one whether you use mv.NET or some other component in the connectivity of the web service to the mv database.  It allows for support of a large number of client processes with very few database connections and does not require special configurations or installations on the client side.  As we make mv "data" more readily available to the non mv world then we are beginning to expand the reach of the mv world.

Leave a Reply