OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

VB.NET Automation of MailMerge.Execute(args) UNO API
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Mon Jun 25, 2007 8:36 pm    Post subject: VB.NET Automation of MailMerge.Execute(args) UNO API Reply with quote

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:

Code:

++++++++++++++++++++++++++++++
Public Sub PrintMMDoc_OO_Write(ByVal strMMDataDir As String, ByVal strMMDataFile As String, ByVal strMMDocDir As String, ByVal strMMDDocFile As String)
        Dim objServiceManager, objDesktop As Object 'root objects from OOo API
        Dim objCoreReflection, objDocument As Object '    objects from OOo API
        Dim objMailMerge As Object '    objects from OOo API

        '2007-06-21 Ted Palmer
        'This Try-Catch block uses MailMerge (functionality) Service of the Open Office Write program
        'to print an instance of the OO-Write document
        'passed as a parameter to this subroutine using the MailMerge data file
        'passed as a parameter to this subroutine.
        Try
            'The service manager is always the starting point
            'If there is no office running then an instance of office is started up (It's magic -- Ted Palmer)
            objServiceManager = CreateObject("com.sun.star.ServiceManager")
            Dim args(7) As Object '<--  Declaring the array with an integer size
            'Name of the datasource linked in OO (change it accordingly to your needs)
            args(0) = OOoPropertyValueStr("DataSourceName", "AutoSubroData")
            'Url of the source document
            args(1) = OOoPropertyValueStr("DocumentURL", "file:///C:\AutoSubrogate\FormsLibrary\OpenOffice\Insured\Acknwldgmnt2Insd.odt")
            'Takes data from 0=tables 1=links 2=queries
            args(2) = OOoPropertyValueInt("CommandType", 0)
            'You must also say which table you're using <-- taken from OOoForum.org
            args(3) = OOoPropertyValueStr("Command", "Acknwldgmnt2Insd")
            'Output to 1=printer 2=file
            args(4) = OOoPropertyValueInt("OutputType", 2)
            'OutputURL file path
            args(5) = OOoPropertyValueStr("OutputURL", "file:///C:\")
            'OutputURL File name. This is supposed to come from a column in a table of the DataSourceName.
            args(6) = OOoPropertyValueBool("FileNameFromColumn", False)
            'OutputURL File name. This is supposed to come from a column in a table of the DataSourceName.
            args(7) = OOoPropertyValueStr("FileNamePrefix", "temp.odt")
            '2007-06-22 Ted Palmer: Instantiate a MailMerge object
            objMailMerge = objServiceManager.createInstance("com.sun.star.text.MailMerge")
            objMailMerge.Execute(args) '<-- Exception thrown by this statement.
        Catch ex As Exception
            MessageBox.Show("In Module MainModule >> Sub PrintMMDoc_OO_Write, there was an Error in the MailMerge process. " _
            & "If this error persists contact your programmer: " & ex.Message, _
            "Subrogation Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try

    End Sub 'PrintMMDoc_OO_Write
++++++++++++++++++++++++++++++

The following block of code is not as voluminous as is looks. There are 3 functions that are identical except for the fact that the second parameter has a different datatype: String, Integer, and Boolean. I tried using Variant datatype for the second parameter, but I kept getting UNO exceptions. Splitting it up was a way of making those exceptions go away.

Code:

++++++++++++++++++++++++++++++
    'Instantiate an object that returns a encapsulated values.
    Function OOoPropertyValueStr(ByVal cName As String, ByVal uValue As String) As Object
        Dim objServiceManager As Object 'root objects from OOo API
        Dim oPropertyValue As Object

        Try
            'The service manager is always the starting point
            'If there is no office running then an instance of office is started up (It's magic -- Ted Palmer)
            objServiceManager = CreateObject("com.sun.star.ServiceManager")
            oPropertyValue = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
            oPropertyValue.Name = cName
            oPropertyValue.Value = uValue
            OOoPropertyValueStr = oPropertyValue
        Catch ex As Exception
            MessageBox.Show("In Module MainModule >> Function OOoPropertyValueStr, there was an Error in the MailMerge process. " _
            & "If this error persists contact your programmer: " & ex.Message, _
            "Subrogation Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try
    End Function 'OOoPropertyValueStr

    'Instantiate an object that returns a encapsulated values.
    Function OOoPropertyValueInt(ByVal cName As String, ByVal uValue As Integer) As Object
        Dim objServiceManager As Object 'root objects from OOo API
        Dim oPropertyValue As Object

        Try
            'The service manager is always the starting point
            'If there is no office running then an instance of office is started up (It's magic -- Ted Palmer)
            objServiceManager = CreateObject("com.sun.star.ServiceManager")
            oPropertyValue = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
            oPropertyValue.Name = cName
            oPropertyValue.Value = uValue
            OOoPropertyValueInt = oPropertyValue
        Catch ex As Exception
            MessageBox.Show("In Module MainModule >> Function OOoPropertyValueInt, there was an Error in the MailMerge process. " _
            & "If this error persists contact your programmer: " & ex.Message, _
            "Subrogation Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try
    End Function 'OOoPropertyValueInt

    'Instantiate an object that returns a encapsulated values.
    Function OOoPropertyValueBool(ByVal cName As String, ByVal uValue As Boolean) As Object
        Dim objServiceManager As Object 'root objects from OOo API
        Dim oPropertyValue As Object

        Try
            'The service manager is always the starting point
            'If there is no office running then an instance of office is started up (It's magic -- Ted Palmer)
            objServiceManager = CreateObject("com.sun.star.ServiceManager")
            oPropertyValue = objServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
            oPropertyValue.Name = cName
            oPropertyValue.Value = uValue
            OOoPropertyValueBool = oPropertyValue
        Catch ex As Exception
            MessageBox.Show("In Module MainModule >> Function OOoPropertyValueBool, there was an Error in the MailMerge process. " _
            & "If this error persists contact your programmer: " & ex.Message, _
            "Subrogation Exception:", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        End Try
    End Function 'OOoPropertyValueBool
++++++++++++++++++++++++++++++


When I single step through the code in the debugger, I can see that the exception always gets thrown in the line of code:
Code:

objMailMerge.Execute(args)

The MailMerge Service has a problem with the value of one or more of the property objects in array args() and it is not telling me the name of the property for which it does not like the value. At this point I have to acknowledge that I have seen (none that I can recall -- I have looked at so much) no other examples of VB.NET code on this forum that is working or near working. All the examples of VB code that I have seen it appears to me to be VB6 code. It would help if those seeking assistance with their code would at least post more information about their environment; i.e., what OS and programming language are they using.

The absence of VB.NET specimens suggests to me the possibility that Microsoft made changes in the binary representation of datatypes between VB6 and VB.NET. That gives what to me seems a plausible explanation for why UNO doesn't like my values for the properties in args(). If that is the case, than there is no hope that I will ever be able to make this work in VB.NET unless I want to make changes to the OpenOffice.org code base itself. I wasn't ready to do that, but it is a possibility.

So my question is does anybody have an idea what is wrong just by looking at my source code? Does anybody know of a way that I can get UNO to tell me which property value it does not like?
_________________
Ted Palmer
www.tedpalmer.com


Last edited by TedPalmer on Tue Jun 26, 2007 6:51 am; edited 2 times in total
Back to top
View user's profile Send private message Visit poster's website
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Tue Jun 26, 2007 12:23 am    Post subject: Reply with quote

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 :
Code:
Sub seeURL
print convertToURL("C:\AutoSubrogate\FormsLibrary\OpenOffice\Insured\Acknwldgmnt2Insd.odt")
End Sub

There may be other errors in your code, I don't know.
______
Bernard
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Tue Jun 26, 2007 7:06 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Tue Jun 26, 2007 12:22 pm    Post subject: Fixed file URLs Reply with quote

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.
Code:

'Url of the source document
args(1) = OOoPropertyValueStr("DocumentURL", "file:///C:/AutoSubrogate/FormsLibrary/OpenOffice/Insured/Acknwldgmnt2Insd.odt")
'OutputURL file path
args(5) = OOoPropertyValueStr("OutputURL", "file:///C:/")


I'm still getting the exception message with ". . . value is not of same or derived type!".

But I now know for sure what a correct file URL should look like. Before I was not sure.

Thank you,
_________________
Ted Palmer
www.tedpalmer.com
Back to top
View user's profile Send private message Visit poster's website
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Tue Jun 26, 2007 9:26 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Wed Jun 27, 2007 8:18 am    Post subject: Reply with quote

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:
Code:

+++++++++++++++++++++++++++
objServiceManager = CreateObject("com.sun.star.ServiceManager")
'Create the CoreReflection service that is later used to create structs
objCoreReflection = objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
'Create the Desktop
objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
'Open a new empty writer document
Dim args(-1) As Object '<--  initializing the array with a -1
'2007-06-20 Ted Palmer following line of code works for opening an existing document. Keep for an example.
objDocument = objDesktop.loadComponentFromURL("file:///C:/AutoSubrogate/FormsLibrary/OpenOffice/Insured/Acknwldgmnt2Insd.odt", "_blank", 0, args)
'Create a text object
objText = objDocument.getText
'Create a cursor object
objCursor = objText.createTextCursor
'Inserting some Text
objText.insertString(objCursor, "The first line in the newly created text document." & vbLf, False)
+++++++++++++++++++++++++++


For what I'm doing now with the MailMerge.Execute(args) I don't have any code statements for objDocument which seems a little strange to me but is consistent with the few bits of example code that I have found mostly in VB6.

The array that I am using to pass the argument list of properties is declared as "Dim args(7) As Object" so it should work OK. I'll have to find some other property to experiment with per your suggestion about setting the macro execution level. At this point I am not running a macro in the "Automation" process. I am just executing UNO API methods. I'll experiment with that some more and post back the results here.

Thank you for your help,
_________________
Ted Palmer
www.tedpalmer.com
Back to top
View user's profile Send private message Visit poster's website
B Marcelly
Super User
Super User


Joined: 12 May 2004
Posts: 1453
Location: France

PostPosted: Wed Jun 27, 2007 11:00 am    Post subject: Reply with quote

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
Code:
Dim noOpt(-1) As Object

objServiceManager = CreateObject("com.sun.star.ServiceManager")
objMailMerge = objServiceManager.createInstance("com.sun.star.text.MailMerge")
objMailMerge.DataSourceName = "AutoSubroData"
objMailMerge.DocumentURL = "your template document URL here"
objMailMerge.CommandType = 0
objMailMerge.Command = "Acknwldgmnt2Insd"
objMailMerge.OutputType = 2
objMailMerge.OutputURL = "your output folder URL here"
objMailMerge.FileNameFromColumn = False
objMailMerge.FileNamePrefix = "tempXX"

objMailMerge.execute(noOpts)

______
Bernard
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Wed Jun 27, 2007 12:10 pm    Post subject: Reply with quote

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


Last edited by TedPalmer on Thu Jun 28, 2007 4:11 am; edited 1 time in total
Back to top
View user's profile Send private message Visit poster's website
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Wed Jun 27, 2007 1:07 pm    Post subject: Reply with quote

Now, see if you can load the document AND pass arguments
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Thu Jun 28, 2007 4:39 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Thu Jun 28, 2007 5:58 am    Post subject: File Lock -- Where is it coming from..?? Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
elizas
General User
General User


Joined: 04 Feb 2010
Posts: 6

PostPosted: Tue Mar 23, 2010 4:51 am    Post subject: Working with mail merge word document using VB.NET Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Tue Mar 23, 2010 6:41 pm    Post subject: I worte a really good reply to the last post but. . . . Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
TedPalmer
General User
General User


Joined: 12 Jun 2007
Posts: 21
Location: St. Louis, MO USA

PostPosted: Tue Mar 23, 2010 6:45 pm    Post subject: When I am I going to learn to quit being so trusting? Reply with quote

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
Back to top
View user's profile Send private message Visit poster's website
alexkn
Newbie
Newbie


Joined: 28 Apr 2012
Posts: 1

PostPosted: Sun Apr 29, 2012 10:59 am    Post subject: VB.NET Automation of MailMerge.Execute(args) UNO API Reply with quote

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

Dim noOpt(-1) As Object

objServiceManager = CreateObject("com.sun.star.ServiceManager")
objMailMerge = objServiceManager.createInstance("com.sun.star.text.MailMerge")
objMailMerge.DataSourceName = "AutoSubroData"
objMailMerge.DocumentURL = "your template document URL here"
objMailMerge.CommandType = 0
objMailMerge.Command = "Acknwldgmnt2Insd"
objMailMerge.OutputType = 2
objMailMerge.OutputURL = "your output folder URL here"
objMailMerge.FileNameFromColumn = False
objMailMerge.FileNamePrefix = "tempXX"

objMailMerge.execute(noOpt)

I have set my paths to all files, but on the line objMailMerge.execute(noOpt) my program gives a mistake. Some days I try to overcome the difficulties, but not succesfully...
I have some ambiguities about line
objMailMerge.DataSourceName = "AutoSubroData"
What must be in the right part?
Path to the file or something else?
Thanx in advance
Alex
P.S. I wanted to send my files as attachment (it's not big) but I didn't find this opportunity on the forum...
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group