How to call a VBA function from a hyperlink VBScript?

[Ullrichvon_Bassewitz]

I'm currently in the process of porting an extension for ArcGIS 9 written in VBA to ArcGIS 10. Most of it works without much rework.

However, there's one thing left that I couldn't solve despite hours of googling: In ArcGIS 9, we used the a modified version of Kristina Callahans AccessHyperlink script. It was called as a macro.

In ArcGIS 10, I have to write a VBScript function (or python or whatever), I cannot just call one that already exists in the VBA environment as before. So how can I do that?

Say for example, I have an VBA project named "MyProject" that contains a module "modHyperlink" with a VBA function "Hyperlink". I want to call this VBA function when the hyperlink tool is used, passing some data as parameters. The actual VBA function is rather large and does lots of database work, so I don't want to recreate it in VBScript.

Ideas? Thanks in advance for any help!

Regards, Uz

[TedKowal]

Does this article help you any ... I have used VBS functions to open Access modules functions, forms etc ..

https://community.esri.com/thread/32176#post117532

[Ullrichvon_Bassewitz]

Thank you for your kind answer. Unfortunately I've already seen the referenced page and it doesn't help.

The answer shows how to open a form that is contained in a separate access database. What I want to do is to call a VBA function which is part of my ArcGis project mxd. I need to know how to locate the VBA projects located in the mxd, and call a VBA function within one of the modules.

Regards, Uz

[DuncanHornby]

I've just been tinkering around with this subject and have not been able to get VBScript to get a reference to the Application object. I thought the GetObject() function would work and it just throws an error. The idea is that once you have got a handle on the currently running application you could drill down the macro and call it. When I set the hyperlink script to be this it just fails:

Function OpenLink ( [Name] )
  Dim app
  set app = GetObject(,"esriArcMap.Application")
End Function

I'm fast coming to the conclusion that you can't do what you are asking with VBScript, but very happy for someone else out there to shoot me down in flames?

An alternative is to abandon the Hyperlink tool and create you own UIControl. This you could embed as VBA or better still create an AddIn using .Net but then you would need to distribute that to your users.

[Ullrichvon_Bassewitz]

Thank you, Duncan. Do you think, the problem can be solved using Python?

Calling the VBA function shouldn't be more than a few lines, so actually I don't care if it's done using VBScript, Python or anything else.

Regards, Uz

[TedKowal]

Here is one methodology to take:

In vb.net or C#  Make a reference to ADO object (ActiveX Data Object) connect to your database and use the command object to run any functions from your access database.  Set up an parameters in code you need to pass..  You will have to set this up as an addin or extension to your mxd.

Some references:

How to use the ESRI OLE DB provider with a geodatabase

Preparing and Executing Commands

This is not an easy task 

Some python possibilities that look promising but you will have to find and install various modules because python does not natively talk with ADO/ActiveX objects

Mayukh's World

While I was searching I did find this on calling Access from VBScript:

Calling function in Access from Vbscript

[Ullrichvon_Bassewitz]

Thank you. I will have a look at the .NET solution. But you're right, it doesn't look like an easy task.

Regards, Uz

[DuncanHornby]

I'm not aware of any method that can run a VBA macro in an MXD via python. If if was possible I would have been looking at the MapDocument object as "the place" to go but it clear that it does not expose the VBA code of an MXD. I would also be surprised if it had done as ESRI are planning to drop support for VBA which is a real shame as its a great language for knocking out short useful bits of code to full blown tools. But I guess that is progress...

So it feels like the VBScript hyper link is only useful for opening browsers and not much else. I would agree with Ted here you are probably going to need to create a Tool AddIn and a bunch of code to call the VBA code stored in the MXD. At this point I would question what's the point of keeping the code in the MXD, a language that not even the software vendor wants?

Duncan

[Ullrichvon_Bassewitz]

Ok, thanks. It wasn't exactly the information I hopped to get, but it's helpful anyway. I will now have to check with my boss how to proceed.

Regards, Uz

[TedKowal]

Thinking about that, yes I believe you cannot reference an access object with VBScript... I know you can in VBA but ESRI has terminated that so another way I think you can do it is through  Net  C# or VB.Net where you can reference the object but you would have to write an addin or extension to do it.

My knowledge of python is not adequate to speculate whether or not this could be done -- I know the ODBC api is quite buggy when I played around with that.