ActiveX - Word - Casting Objects to Variants

[rik_aspinall]

Hi there,

I'm just getting used to using Active X to automate MS Office tasks. I have the full suite, and the report generation tool does some things, but there's plenty more that I would like to do and I'm giving Active X a go (it's basically what NI do anyway).

I am having trouble with some of the methods as they have an input variant type, yet nobody tells me what the format of the variant is. As an example I give you this VI which looks to add a caption to a picture. I see the following documentation from Microsoft, which tells me that:

Documentation for method

Label

Type: System.Object

Required Object. The caption label to be inserted. Can be one of the WdCaptionLabelID constants.

Title

Type: System.Object

Optional Object. The string to be inserted immediately following the label in the caption (ignored if TitleAutoText is specified).

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.range.insertcaption%28v=office...

- So, I should cast my variant as "System.Object" (?!). I could probably assume that Title is just a simple string, but where would I find that out formally? My guesswork will eventually fail.

Enum for Caption type:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.wdcaptionlabelid%28v=office.15...

- I've tried casting both as a string and a number, neither work (I would assume an enum is a number, I've tried 0,1,2)

Any help would be appreciated, even if this may be more Microsoft not documenting their object structure than NI's problem, we're all working towards the same goal! It is possible that there's another mistake as to why this program doesn't run, but I still need the general method...

Note that you need to have an input file specified to make the program run.

Thanks for your help,

Rik

[Bob_Schor]

Several years ago (6?), while running Excel 2007 on Windows XP, I tried integrating Excel Workbook functionality into LabVIEW using ActiveX functions to do various tasks, such as reading/writing specific rows/columns on specific Worksheets.  It was quite a challenge, in part because of the difficulty with finding ActiveX documentation.  At that time, there were Help files that were "helpful".

Recently (about two years ago) I tried updating this code to Windows 7 and Office 2010.  However, the Help Files were now much more difficult to find, and "did not play well with others".  On the other hand, I found that the Excel Object model that LabVIEW had implemented in the reworked Report Generation Toolkit allowed me to do much of the functionality that I'd previously implemented with ActiveX, but in a "documented" and much simpler fashion.  There was still the need for an occasional call to ActiveX, but I mostly use the functions contained in the Toolkit.

I've attached an example, my EXCEL Delete WorkSheet function, which takes an NI_Report object representing an Excel "Report" and a string with the WorkSheet to be deleted.  The function "Sheet Exists?" tries to do an Excel Get Worksheet call -- if it fails with error -41110, then the Worksheet does not exist (and this particular error is cleared).  If the Worksheet does exist, I use the Get ActiveX References function to get a reference to the Worksheet, then use the _Worksheet Delete method to delete it.

I just looked to see where else I needed ActiveX references.  Here are the functions I needed them:

• Sheet Limits, to get the number of rows and columns used on a particular Worksheet
• Column Limits, Row Limits, similar to Sheet Limits
• Sheet Size, identical to Sheet Limits (I didn't realize I'd duplicated my efforts!)
• Workbook Active, tests if the Workbook Object is still open (fixes a bug feature of the Report Generation Toolkit)

The Workbook Active function was needed to handle the following situation -- I was using the Toolkit to open, modify, then save and close an Excel Workbook.  What I didn't anticipate was that if the Workbook was closed "manually" (using the "Close" button in the upper-right corner), and then LabVIEW tried to Save/Close the Workbook, NI's code would delete (without asking) the already-saved Excel file!  This was brought to NI's attention -- they may change this behavior, but then again, maybe they won't ...

So before I forget to attach it, here is EXCEL Delete Worksheet:

Bob Schor

[Bob_Schor]

I just noticed the word "Word" in the title of this post.  I've seen a number of "ActiveX" questions regarding Excel, and I "carelessly" replied to this post assuming we were talking about Excel and ActiveX.  I haven't looked as carefully at the Word options, but probably some of what I said will apply to Word and the NI_Report Object code, as well.

BS

[nyc_(is_out_of_here)]

---

@rik_aspinall wrote:

Hi there,

I'm just getting used to using Active X to automate MS Office tasks. I have the full suite, and the report generation tool does some things, but there's plenty more that I would like to do and I'm giving Active X a go (it's basically what NI do anyway).

I am having trouble with some of the methods as they have an input variant type, yet nobody tells me what the format of the variant is. As an example I give you this VI which looks to add a caption to a picture. I see the following documentation from Microsoft, which tells me that:

Documentation for method

Label

Type: System.Object

Required Object. The caption label to be inserted. Can be one of the WdCaptionLabelID constants.

Title

Type: System.Object

Optional Object. The string to be inserted immediately following the label in the caption (ignored if TitleAutoText is specified).

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.range.insertcaption%28v=office...

- So, I should cast my variant as "System.Object" (?!). I could probably assume that Title is just a simple string, but where would I find that out formally? My guesswork will eventually fail.

Enum for Caption type:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.word.wdcaptionlabelid%28v=office.15...

- I've tried casting both as a string and a number, neither work (I would assume an enum is a number, I've tried 0,1,2)

Any help would be appreciated, even if this may be more Microsoft not documenting their object structure than NI's problem, we're all working towards the same goal! It is possible that there's another mistake as to why this program doesn't run, but I still need the general method...

Note that you need to have an input file specified to make the program run.

Thanks for your help,

Rik

---

Posting your VI would be helpful.

[tyk007]

Lucky you, playing around with COM Interop. Since the assembly you're playing around with is a .Net wrapper around COM, the types boil up to the highest class in the .Net inheritance chain - Object.

Based on the definition:

void InsertCaption(
	ref Object Label,
	ref Object Title,
	ref Object TitleAutoText,
	ref Object Position,
	ref Object ExcludeLabel
)

I would expect that you are on the right track. Just be aware that although the MSDN documentation says "Optional" I believe LabVIEW tries to supply default values anyway (Optional Parameters in .Net aren't fully supported in LabVIEW). There is a link below that explains this.

http://digital.ni.com/public.nsf/allkb/DC41DCDA972642CF8625787E00732DDD

You should provide all values to all parameters (strings, numerics, null references etc.). So, as an example, I could go with {0, "SomeTitle", "", 0, False}

Another option is to wrap up your Interop code into another .Net assembly that only exposes value types natively translated by LabVIEW (basically the standard value types). Sometimes this is the easier option to avoid typos and let's you use the default values for the optional parameters.

I haven't had time to generate an example in your particular case but if you are still having trouble feel free to post some code.

[rik_aspinall]

Hi Folks,

Thanks for all your helpful suggestions. I have in fact managed to do it, using the run macro part of the report generation tools. I'd like to share a few thoughts.

1) One way I learned what the enum constants were was to run a vba macro, that simply said MsgBox(wdCaptionPositionBelow). This would print out the numeric value of the enum, which was not necessarily what you would expect by reading the online help and counting up from 0, it being the top of the list.

2) By opening up the report generation tools and going a few levels down, you have access to some really neat pieces of code that serve as either examples for how to use this doing labview, or subVI's that package tasks quite nicely to speed things up. I'm not a fan of the native properties/methods support that Labview has however because:

3) I ended up using the following. One caveat is that you need to go to Word -> Trust Center -> Trust center settings -> Macro settings -> Trust access to the VBA project object model and enable it. I think this way is the best as you can also find out how to do things by recording a macro, do the actions as you normally would, and then examine the macro to see the code associated with your actions and edit as necessary.

[rik_aspinall]

4) Great catch on .NET support. The limitation was not well publicized.

5) Word, Interop, ActiveX, .NET and COM all seem to be things that make this technology work. Moreover, .NET seems like it is trying to keep old technologies going, which is worrying for long term support. The fact that nobody made this easy and well documented as a solution means I'll ask an open question: is this functionality/technology becoming obsolete, and if so, what will replace this, and when?

Thanks to tyk007 and bob for your help!

Rik

[tyk007]

---

@rik_aspinall wrote:

Hi Folks,

Thanks for all your helpful suggestions. I have in fact managed to do it, using the run macro part of the report generation tools. I'd like to share a few thoughts.

1) One way I learned what the enum constants were was to run a vba macro, that simply said MsgBox(wdCaptionPositionBelow). This would print out the numeric value of the enum, which was not necessarily what you would expect by reading the online help and counting up from 0, it being the top of the list.

2) By opening up the report generation tools and going a few levels down, you have access to some really neat pieces of code that serve as either examples for how to use this doing labview, or subVI's that package tasks quite nicely to speed things up. I'm not a fan of the native properties/methods support that Labview has however because:

3) I ended up using the following. One caveat is that you need to go to Word -> Trust Center -> Trust center settings -> Macro settings -> Trust access to the VBA project object model and enable it. I think this way is the best as you can also find out how to do things by recording a macro, do the actions as you normally would, and then examine the macro to see the code associated with your actions and edit as necessary.

In .Net all objects derive from System.Object. This base class has a ToString() method that can be over-ridden by child classes to provide a custom implementation. The reason you get the enum value printed out in your point 1) above is because the Enum class has over-ridden this method to output the value of the enum when called. The wrapper around the COM method calls the ToString method internally on whatever Object (or dervied class) is passed. This is quite useful in that particular scenario where you can provide almost anything to the method and it will often "do the right thing".

Out of interest: http://msdn.microsoft.com/en-us/library/a0h36syw(v=vs.110).aspx

[tyk007 · Accepted solution]

---

@rik_aspinall wrote:

4) Great catch on .NET support. The limitation was not well publicized.

5) Word, Interop, ActiveX, .NET and COM all seem to be things that make this technology work. Moreover, .NET seems like it is trying to keep old technologies going, which is worrying for long term support. The fact that nobody made this easy and well documented as a solution means I'll ask an open question: is this functionality/technology becoming obsolete, and if so, what will replace this, and when?

Thanks to tyk007 and bob for your help!

Rik

---

---

Performing COM Interop in LabVIEW is time-consuming for a number of reasons - mostly due to the architectural design of many COM components. Well designed components are loosely coupled but this often means many calls on many objects - many of which often have optional parameters. My suggestion is to use a .Net asembly if wrapping to LabVIEW is required. It's not that it's not possible to implement directly in LabVIEW (it obviously is) but you need a lot of infrastructure in your code to ensure that you don't loose that same coupling or, more importantly, readability in your own LabVIEW extension.

In your scenario I would ask myself : Is this interface I am building a long term solution or code that I am likely to change often? If yes, then you could encapsulate the COM Interop calls and effectively create a Facade that provides the end user (eg. LabVIEW) the functionality that you need via a determined interface without exposing the changeable parts (the COM Interop). However you need to be comfortable creating said .Net assembly which can be daunting if you don't have the experience.

http://en.wikipedia.org/wiki/Facade_pattern

As far as future support goes - it's a really, really good thing that .Net is helping to keep these older technologies going. Long term support is becoming an increasing bigger concern for those markets (read - most business rather than consumer solutions) and there are millions of applications out there running right now running at least one of those technologies. This is one of the reasons I recommended to you that a Facade may be the best long term solution - there will always be an Office API. A Facade will always provide the same interface either way, so calling code is unaffected when the way you interface to the API does.

PS: Windows 8 exposes the core programming framework WinRT which is basically built on ... COM. I cant see it's predecessor going anywhere for many years yet.

[rik_aspinall]

Hi Tyk007,

I'm trying to go ahead and learn how to make .net assemblies, and I have just seen example code in Labview that describes how to call a .NET assembly, but if you had any little example of a .NET assembly (and source code) calling an office file and doing something simple like adding the caption I would be really gratful.

Thanks,

Rik