VB.NET Automation of MailMerge.Execute(args) UNO API

[TedPalmer]

All on Planet Earth:

First I want to acknowledge the contributions of dannyB, gibson, Hffm, and Mark B. That is what has gotten me as far along as I am. Thank you..!! But I'm still not on pay dirt yet.

My operating system (OS) is Windows 2000 Pro; I am using Visual Studio 2003 -- Visual Basic and I am trying to use what Microsoft calls Automation to control the behavior of the Open Office Write program from my VB.NET program to perform the mail merge functionality of merging fields (columns) from a datasource name which is an Open Office Base Registered database named AutoSubroData table name Acknwldgmnt2Insd.

I am getting one of the 2 following exceptions thrown depending on whether or not I have commented out the statements for args(6) and args(7). With args( 6 & 7) commented out I get the following exception message:

InterfaceOleWrapper_Impl::Invoke: [automation bridge]UnoConversionUtilities<T>::createOleObjectWrapper
The VARIANT does not contain an object type!

The error message would be a lot more helpful it it said which property it was finding fault with the value. but it does not. So I would just have to guess for args(0) through args(5) the values are either datatype String or Integer. So the complaint that: "The VARIANT does not contain an object type!" doesn't make sense to me because I don't see a property that takes a value of type object.

With statements for args(6) and args(7) not commented out I get the following error message:

[automation bridge]com.sun.star.script.CannotConvertException in UnoConversionUtilities<T>::variant ToAny! Message: value is not of same or derived type!

For testing purposes I have hard coded values that will be replaced by passed parameters. Here is the source code that I am using:

[B Marcelly]

Hi,
I understand that you don't master posting on this forum.
First learn to use Code tag before and after your code ! There is a button for this in the Edit window of the post.
Use Preview button before posting.
By the way, did you know that you can edit an already posted message: click the Edit button you can see at top right of your messages.

About your code : properties DocumentURL and OutputURL are not filled with correct URL's.
Use a small Basic macro to see a correct conversion :

[TedPalmer]

Thanks Bernard. I have made the first changes to my post you requested and added some text that I hope makes my question more clear. I'm working on your second suggestion now -- the URL syntax.
_________________
Ted Palmer
www.tedpalmer.com

[TedPalmer]

Bernard:

I found the convertToURL function that you mentioned; I pasted my system file specification into the little script that help gave me to show how to use the function and, after clicking the little green run arrow, a correct file URL was returned. I made the following changes to my VB.NET source code and ran it.

[pitonyak]

I use Linux, so I can not comment on some of these issues.

Are you able to load a document using VB?

If the answer is, yes, then try another test where you specify a single argument, say, to set the macro execution level and see if you have the same problem.

The problem might be the datatype declared to hold the array of properties, it might be the arguments, etc.... It might be realted to the specific argumetn types.
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php

[TedPalmer]

Andrew,

Yes I can open an OpenOffice document from a small test program that I created in VB.NET 2003 for prof of concept. In fact it is the same OpenOffice document that has all the fields for insertion by the Mail Merge process that appears in my source code above.

I started with the VBScript (It ran OK in Windows Scripting Host on my development PC that I am using in this effort) that is included in the SDK Developers Guide. I modified it to work in VB.NET 2003. It would not work as I found it without some modification.

Here is a code snippet from that program:

[B Marcelly]

Hi Ted,
I don't practice mailmerge, but referring to an example from Laurent Godard the way you are doing it is much more complex than needed (if it can work).

Of course you must have your DataSource correctly installed.
I am not sure your FileNamePrefix value is correct : I would not put the extension part.
I would not use C:\ as the output folder, but I would use a folder within it, say C:\myMerge. You must create the output folder before running the mailmerge.

This is the equivalent code between your Try and Catch lines

[TedPalmer]

Bernard,

It worked..!! After struggling with this for so long. This brings joy to my heart and tear to my eye. Some of your friends were bragging on you here and they got it right. You are VERY VERY good and so is your friend Laurent Godard.

It is creating the file in the folder you suggested. The OpenOffice program is leaving a lock on the Mail Merge datafile.txt so I can't delete and replace it. I had that problem before. I'll have to figure out a way to deal with that.

I was able to paste your code into my Try/Catch block; make the string substitutions you said; compile it; and it ran without throwing an exception. Life is good.

I'm in a big rush. I haven't worked in a year and this recruiter wants to meet me in 1.5 hours. So I got to go. More later.

***** THANK YOU *****
_________________
Ted Palmer
www.tedpalmer.com

[pitonyak]

Now, see if you can load the document AND pass arguments
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php

[TedPalmer]

Andrew,

I was going to say that that is an experiment that I prefer to defer for now, but then another thought occurred to me. I am not sure of your meaning about passing arguments. I hope you don't mean see if I can go back to the old way I was using before Bernard gave me the solution that he did. I was passing argumentss as objects stored in array elements that were supposed to set properties for the MailMerge Service. (Now I can't remember if dannyB said Services are a collection of methods or properties. That seems a religious issue for him and I am sure that he has it right.) Like Bernard said: I was making it too hard for myself. I prefer to stay with what I have now that works.

But if you were referring to the MailMerge service filling in the fields on the Write document, it is. But that isn't the thought I had that was triggered by your suggested experiment. I had intended to give the user a print preview option like I had with MS-Word 2000. So that they could preview the document and add or change text if they want to before optionally saving it to a file and/or optionally printing it. With the Output parameters in the MailMerge service being what they are -- as best I recall -- I am not sure if or how I am going to be able to do that. I recall that the MailMerge Service done interactively does allow for that, but for now I don't see how I am going to be able to do that using "Automation" (as defined by Microsoft).

Suggestions are welcome.
_________________
Ted Palmer
www.tedpalmer.com

[TedPalmer]

All,

I found the source of the file lock that was blocking the MailMerge processing from updating the MailMergeData source flat filename.txt from being deleted and replaced. I had an instance of OOo open doing something unrelated to what I was doing from my VB.NET program using Automation to generate a document by performing the MailMerge process. After the automated MaiMerge process finished and closed its instance, this open instance was causing the lock on the flat filename.txt to be there so that the next MailMerge process couldn't delete it and replace it with new data for whatever other document was being generated by my VB.NET program.

Solution: Don't have any instances of OOo open when using my VB.NET program to produce documents. I am confident that I can train my users to adopt that procedural solution.
_________________
Ted Palmer
www.tedpalmer.com

[elizas]

This tip demonstrates how to attach the DB source and populate fields from table to a mail merged word document in VB.NET.
Code Snippet

Dim objWord As New Object
objWord = CreateObject("Word.Application") ' Creating a word application

Dim objDoc As New Object
objDoc = CreateObject("Word.Document") ' Creating a word document object

objWord.application.WindowState = 0 ' set the word window in normal state (Const wdWindowStateNormal = 0)
objDoc = objWord.Documents.Add(DocsFolder.FullName & "\YOUR WORD DOC NAME") ' Add the mail merged document file

Dim NameBeforeWordMerge As String = objWord.ActiveDocument.Name

objWord.ActiveDocument.MailMerge.OpenDataSource("YOUR DATABASE PATH", _
Connection:="dsn=DSN NAME; dbq=" & YOUR DATABASE PATH & ";", _
sqlstatement:="select * from `" & tableName & "`")

http://www.mindfiresolutions.com/working-with-mail-merge-word-document-using-vbnet-422.php
_________________
Cheers,
Eliza

[TedPalmer]

I worte a really good reply to the last post but. . . . I guess I took too long because when I clicked on "submit" I was taken to a logon screen and my post message was lost and I can't get it back. That was a lot of DAMN work for nothing.

Ted Palmer
_________________
Ted Palmer
www.tedpalmer.com

[TedPalmer]

If I have had a clue that my logon was going to time out in such a short time, I would have copied and pasted my message to an instance of Notepad or something.

DAMN that really pisses me off..!!
_________________
Ted Palmer
www.tedpalmer.com

[alexkn]

Hi all!
to B. Marcelly
I have copied yor code from forum (http://www.oooforum.org/forum/viewtopic.phtml?t=58920)