Jiwa 7 with Excel VBA

[indikad]

Is there a way to make use of the Jiwa 7 objects within VBA ( excel) to login and then create a sales order etc ?
I have tried to reference a dll but was not successful.
If there are any sample code available much appreciated.

[SBarnes]

Short answer is you can't as you need COM DLLS, which the Jiwa DLLs aren't set up to do i.e. they haven't been annotated to achieve this with appropriate CLSIDs etc.

But the way around the the problem would be to write a .net application that uses Farpoint Spread (Attkey own a commercial license to this) and this can load in an excel spreadsheet using the OpenExcel method.

You could even better yet write your own Maintenance form that used the JiwaGrid and build it into Jiwa in a plugin, so that you don't have to worry about deploying it as it goes along with the Jiwa client, this is not that hard to do and is probably the better option.

[Mike.Sheen]

Do you have to use VBA? I thought VSTO was the preferred or new way to use .NET assemblies from Office products.

[SBarnes]

VSTO is but I would steer clear of it given I've seen some weird and wonderful windows permission issues with it where the addins created with it don't work/load all the time in Excel.

[pricerc]

JIWA 7 did with breakouts/plugins what MS *should* have done with Office - switched from COM to .NET as the actual underlying technology.

Unfortunately, Office is still largely COM (well, even lower-level than that, I think), which is why VBA (which is really just restricted VB6) is still the best way of doing many things in Office itself. E.g. if you record a macro, it creates a VBA method.

I tried using VSTO years ago, and it was a right pain. And I don't believe it's improved greatly, especially if all you're wanting to do is add a small bit of code to a spreadsheet (like, say a custom function).

I've actually had quite a bit of success going the other way - writing (in Visual Studio.NET) COM wrappers for .NET assemblies - I had to do one recently for a JIWA 6 client who needed their JIWA to talk to a third-party library supplied as a .NET assembly.

And in my last job, I used wrote a whole .NET library to do some complicated calculations, only to discover that most of the use-cases for the library were COM-only environments. Or Microsoft Office users. So I ended up writing a COM-visible wrapper for my .NET library, and got people to use that.

So while it's not something you probably want to do too much of, you could build a COM-visible .NET 'assembly' (DLL) that calls the .NET JIWA objects. If you have a limited, well-defined use case, then it shouldn't even be too tedious. The biggest thing I learnt I had to do to make it work reliably, is to create explicit interfaces that COM will use, and set your class attributes correctly.

[SBarnes]

All this sound fairly complicated to get around an antiquated technology like COM when Excel is in reality is only storing the data.

If something else can be used to read in the data such as the JiwaGrid or saving the excel as csv and then using straight .net code to read it in then that's got to be far simpler and avoids having to register COM DLLs all over the place.

[pricerc]

Although I haven't tried using the REST interface yet myself, would that not be an option?

I made an Access database (using VBA) talk to an XML-based REST service a couple of years back, so I know that can make VBA talk REST.

edit: Google first response to "VBA REST" search:

https://github.com/VBA-tools/VBA-Web

VBA-Web (formerly Excel-REST) makes working with complex webservices and APIs easy with VBA on Windows and Mac. It includes support for authentication, automatically converting and parsing JSON, working with cookies and headers, and much more.

[Mike.Sheen]

Caveat: I've not tried this yet.

So, it looks like regasm can be used to generate the COM registry entries needed to allow applications using COM invoke a .NET assembly (and hence allow VBA to use Jiwa assemblies).

From docs.microsoft.com:

The Assembly Registration tool reads the metadata within an assembly and adds the necessary entries to the registry, which allows COM clients to create .NET Framework classes transparently. Once a class is registered, any COM client can use it as though the class were a COM class.

So, If my tired eyes are reading this right - you can run regasm to add the registry entries (or generate a .reg) file for any .NET assembly, which will create the registry entries needed for VBA to be able to use the Jiwa assemblies. Of course, you'd need to run that or run the generated .reg file on every machine you want to consume the COM interfaces on.

Mike

[pricerc]

Caveat: I've not tried this yet.

So, it looks like regasm can be used to generate the COM registry entries needed to allow applications using COM invoke a .NET assembly (and hence allow VBA to use Jiwa assemblies).

Mike

From painful experience in getting VBA talking to .NET assemblies, unless the .NET code has been written with COM interop in mind, the odds of getting a satisfactory experience are not great. There are all sorts of things that COM just can't see that we take for granted in .NET. Things like parameterized constructors, generics and overloaded methods. And then there are the data type differences. Anything that can't be mapped automatically is just invisible to COM. The only way I got it to work is by explicitly coding for it.

https://docs.microsoft.com/en-us/dotnet ... nts-to-com has a whole lot more information on the topic

So there's a fighting chance that you'll be able to use regasm to get JIWA assemblies *usable* in VBA (helped by the fact that JIWA has a lot of factory methods for constructing business objects). But I think a fighting chance is about as good as it'll get.

As a quick test, I regasm'd 7.2's JiwaApplication and referenced the TLB from MS Access. Object browser then shows me, well, nothing:

MS Access Screen shot showing references

With nothing browsable, I'm not sure how you'd go about instantiating anything.

[SBarnes]

I think Ryan is correct unless the .net classes have been decorated with the correct annotations for COM CLSIDs etc I can't see how it would work as you need these annotations to make the COM run time work based on the registry entries to know how to call and execute the code, you have to do this also even with C++ classes under technologies that I have use before such as C++ ATL.

Besides in this project the only reason the code is in VBA is that's what the data is in and the original examples in excel from Jiwa from way back when were adopted and modified to do the job, it would be far simpler to approach the issue from the other direction and use a plugin to either read the data as excel or csv and work with it inside Jiwa as there is nothing special in the VBA code that C# won't do.