Word 07 Interop madness

[DPrometheus]

Hey there,

 

Some serious problem occurred the other day.

I'm building a reporting module. At the moment I have a .NET Application which consists of several buttons to the right side of the form. on the mid - left side there is Word 2007 hosted in a panel. The .NET Application loads a .NET DLL for some functionality for those buttons. Word loads a specific template which strips most of the ribbon and adds our own buttons and controls. (With VBA Scripts). These buttons calls the same .NET DLL as the application. Now I need to have access to a patient Identifier (for now perhaps more fields later on) which resides in the .NET DLL.

I made a simple class EccPatientManager which holds the patient ID.

 

Public Class EccPatientManager

   ' Shared ID
   private shared _id As Long = -1

   ''' <summary>
   ''' Switch to patient
   ''' </summary>
   ''' <param name="value"></param>
   ''' <remarks></remarks>
   Public Shared Sub setPatient(ByVal value As Long)
       _id = value
   End Sub

   ''' <summary>
   ''' Retrieve patient ID
   ''' </summary>
   ''' <value></value>
   ''' <returns></returns>
   ''' <remarks></remarks>
   Public ReadOnly Property ID() As Long
       Get
           Return _id
       End Get
   End Property

 

If I fill this in the .NET Application with valid data and call this from word (with vba ) I always get back -1. Probably because the .NET DLL is loaded twice, once for each process.

Now I looked a little into IPC but with DDE I think this is a no-go with my current knowledge about it and the time constraint on the project.

Also .NET solutions as .NET Remoting don't apply in my opinion as WinWord is not a .NET application, and I can't modify it's source

 

Does someone can put me in the right direction to send data (as simple as integer value-types) from my application to the VBA script? I need to execute some code in VBA [create a form (from the .NET DLL) and show it, as well as supply the patient Identifier]. (No option to move the calling code to the .NET DLL as the VBA handles the GUI of the template for WinWord).

 

Thanks for reading, let me know if you've got any suggestions..

 

~ DP

[Cindy Meister]

It's very hard to offer an opinion, since you don't include any of the VBA code you're using to access the .NET App. Nor do you show in the .NET app how you're exposing the functionality to VBA (COM).

 

But I agree that VBA isn't "seeing" the same class instance your .NET app is using. What should happen to this data (ID, etc.) down the line? Should it be written to a database of some kind? Can't VBA access the stored information (as opposed to the "live" information being held in memory)?

[DPrometheus]

Thanks for your opinion thus far.

 

since you don't include any of the VBA code you're using to access the .NET App. Nor do you show in the .NET app how you're exposing the functionality to VBA (COM).

In the load event of the Main Form I call this line in the .NET App:

_wDoc = EcAppPanel1.LoadDocument(My.Computer.FileSystem.CurrentDirectory & "\Spec.dotm")

which opens the file in the hosted Word Instance.

In this macro enabled template I got only one function like this:

Sub AutoNew()

    AddIns.Add ("K:\mso\AppInApp\Word07Def.dotm")

End Sub

[/Code]

Which adds another macro enabled template to the word instance.

This is done like this so the template stays active even after switching documents.

This Word07.dotm template modifies the ribbon together with some additional functionality. For example

[Code]
Sub AdresseringButton_Click(control As IRibbonControl)
Dim Address As New Specialist07.EcfAdresseringForm
Address.Show
End Sub
[/Code]

Where Specialist07 is a .NET DLL registered with regasm & gacutil and marked as COM exposed. EcfAdresseringForm is a .NET form. The resulting tlb file is linked to the dotm file by extra->References in the VBA Editor.

 

What should happen to this data (ID, etc.) down the line?

It should check other properties for a patient in a db (like name and disease and stuff) do some transformations with it (insert predefined text from other table (based on patient ID, actually disease and some other variables but that is linked to the ID) & replace special characters in the text with those variables of this selected patient) and finally it should add this piece of text (with the inserted variables) into the word document.

 

Later the document is saved in another table in the DB.

 

btw the patient ID can change anytime by another application (This is handled in the .NET assembly, but the VBA can't update (since it doesn't share the same patient Manager object))

 

~ DP

[DPrometheus]

Update

=-=-=-

 

The problem grew today in size as I also need a reference to some document objects. Those can be set with a document manager (custom class like above patient manager with additional functions). But the references are lost in the VBA code (if I set it within the .NET assembly), same goes for the other way 'round.

 

Anyone has tips for a workaround or something?

 

thanks in advance,

~DP