MailMerge OpenDataSource CU680

nav4all
nav4all Member Posts: 3
edited 2013-07-11 in NAV/Navision Classic Client
Hi

To overcome the 64 field limit in HTM file MailMerge funcionality I tried this in a vb script and it worked exactly as it should.
Problems start when I want to implement it in CU680, NAV don't want my parameters..

First, I changed the extension on the merge file to HTML. Then Word understand nothing and think it's an external datasource, which is good, no 64 field boundary.

Here is vbs :

Dim wrdApp
Dim wrdDoc
Dim dsName
Dim FileName

Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add

dsname = "C:\NAV\MBS KM\MAILMERGE\MergeSource.HTML"
FileName = "C:\NAV\MBS KM\MAILMERGE\MergeTarget.DOC"

wrdDoc.MailMerge.OpenDataSource _
dsName, _
false , _
false , _
true , _
false , _
0 , _
"" , _
"" , _
0 , _
"" , _
"" , _
"" , _
"SELECT * FROM `Table`" , _
"", _
true, _
1

wrdDoc.SaveAs FileName, 0

wrdDoc.ActiveWindow.Caption = "VBS Test"
wrdDoc.Saved = TRUE
wrdApp.Visible = TRUE

Set wrdDoc = Nothing
Set wrdApp = Nothing


When I try to change :

wrdDoc.MailMerge.OpenDataSource(MergeFileName,ParamInt);

in CU680

to:

MergeFileName := 'C:\NAV\MBS KM\MAILMERGE\MergeSource.HTML';

dsName := MergeFileName;
dsConfirmConversions := FALSE;
dsReadOnly := FALSE;
dsLinkToSource := TRUE;
dsAddToRecentFiles := FALSE;
dsPasswordDocument := 0;
dsPasswordTemplate := '';
dsWritePasswordDocument := 0;
dsWritePasswordTemplate := 0;
dsRevert := FALSE;
dsFormat := 7;
dsConnection := '';
dsSQLStatement := 'SELECT * FROM ''TABLE''';
dsSQLStatement1 := '';
dsOpenExclusive := 0;
dsSubType := 0;

wrdDoc.MailMerge.OpenDataSource
(
dsName,
dsConfirmConversions,
dsReadOnly,
dsLinkToSource,
dsAddToRecentFiles,
dsPasswordDocument,
dsPasswordTemplate,
dsWritePasswordDocument,
dsWritePasswordTemplate,
dsRevert,
dsFormat,
dsConnection,
dsSQLStatement,
dsSQLStatement1,
dsOpenExclusive,
dsSubType
);

Then it will not run, error : One or more of the arguments could not be coerced.

All variables is declared Variant and assigned an value, OpenDataSource obviously don't like Byvalue..

Anybody out there who have any ideas how to solve this ?

Comments

  • nav4all
    nav4all Member Posts: 3
    The solution was very simple..when you find it...

    In CU681 change the following in function ConstMergeSourceFileName

    //FileName := "3TierAutoMgt".EnvironFileName(Text002 + DocNo + '.','HTM');
    FileName := "3TierAutoMgt".EnvironFileName(Text002 + DocNo + '.','HTML');

    In CU680, functions CreateMailMergeDocuments and OpenmailmergeDocuments

    Move the following statement before wrdDoc.MailMerge.OpenDataSource(MergeFileName,ParamInt);

    wrdApp.Visible := TRUE;

    As far as I know there is no way to get rid of the "select table" dialog box when you use HTML file as MergeSource but we can live with that, it's not an daily procedure.

    Enjoy
  • Hannes_Holst
    Hannes_Holst Member Posts: 1
    hi,

    Thanks for sharing your workingprocess. it helps me alot.

    from my perspective I changed the following to get it work:

    - i changed the DataType of the variables to this: 
    dsConfirmConversions [Boolean]
dsReadOnly [Boolean]
dsLinkToSource [Boolean]
dsAddToRecentFiles [Boolean]
dsPasswordDocument [Text30]
dasPasswordTEmplate [Text30]
dsWritePasswordDocument [Text30]
dsWirtePasswordTemplate [Text30]
dsRevert [Boolean]
dsConnect [Text30]
dsSQLStatement [Text30]
dsSQLStatement1 [Text30]
dsOpenExclusive [Boolean]
dsSubType [Integer]
dsFilename [Text260]
dsFormat [Integer]

    - and i changed the order of the parameters in OpenDataSource to this: 
    wrdDocument.MailMerge.OpenDataSource(dsFilename,
                                     dsFormat,
                                     dsConfirmConversions,
                                     dsReadOnly,
                                     dsLinkToSource,
                                     dsAddToRecentFiles,
                                     dsPasswordDocument,
                                     dasPasswordTEmplate,
                                     dsRevert,
                                     dsWritePasswordDocument,
                                     dsWirtePasswordTemplate,
                                     dsConnect,
                                     dsSQLStatement,
                                     dsSQLStatement1,
                                     dsOpenExclusive,
                                     dsSubType);

    - and I changed the initvalues for the parameters to this: 
    dsConfirmConversions := FALSE;
dsReadOnly := FALSE;
dsLinkToSource := TRUE;
dsAddToRecentFiles := FALSE;
dsPasswordDocument := '';
dasPasswordTEmplate := '';
dsWritePasswordDocument := '';
dsWirtePasswordTemplate := '';
dsRevert := FALSE;
dsConnect := '';
dsSQLStatement := 'SELECT * FROM `Table`';
dsSQLStatement1 := '';
dsOpenExclusive := FALSE;
dsSubType := 0;
dsFilename := 'C:\yourfile.html';
dsFormat := 7;

    - it is really important to change the extension of the file from .HTM to .HTML
    - and I think it depends on the value of "dsSQLSatement" that Word will not show any Dialog while opening

    With the described changes the MailMerge works fine for me.
    I tested with 170 fields (I think till 255 will be possible). I will go on a productive system soon.

    Best,
    Hannes

Categories