Invoke VBA from lotusscript after mailmerge

The syntax for the Run command can be a bit tricky. Perhaps we should see your code.
What do you mean by invoking it manually?

HI,

Have you tried ?

Dim app as Variant
Set app = CreateObject("Word.Application")
Call app.Run("myMacro")

Regards

RGonzon1971

Spot on:  I actually already got that working before reading your post using:

Call wordobj.Run("shade")

My problem now is that it sometimes runs against the merged data which is good, but sometimes runs on the template which is no-good.

How do I determine that it always runs on the merged file?

I suppose you have to activate beforehand the right document

Thanks for your interest GrahamSkan.

I have made the code run from Normal .dot using this syntax. Remember this is in lotusscript...

With maindoc.MailMerge
      .MainDocumentType = wdFormLetters
      If OutputFormat = "PDF" Then
            .Destination = wdSendToNewDocument
      Else
            .Destination = wdSendToPrinter      
      End If
      .SuppressBlankLines = True
      ' No need to OPEN the datasource as this is fixed and defined int he template.
      With .DataSource      
            .FirstRecord = wdDefaultFirstRecord
            If WhatToPrint = "All" Then
                  .LastRecord = wdDefaultLastRecord
            Else
                  .LastRecord = 1
            End If
      End With
      .Execute False            
End With
      
If WhatToPrint = "All" Then
      Print "Maindoc.Shade after merge..."
      ' Call maindoc.Shade
      ' Got error Instance member SHADE does not exist on line 43
      ' Call wordobj.maindoc.shade
      ' Got error Instance member MAINDOC does not exist on line 45
      ' Call wordobj.shade
      ' Got error Instance member SHADE does not exist on line 47
      ' Call wordobj.Run("Normal.Module1.shade")
      ' Got error Microsoft Word: Unable to run the specified macro on line 49
      ' Call wordobj.Run("Normal.shade")
      ' Got error Microsoft Word: Unable to run the specified macro on line 51
      Call wordobj.Run("shade")
      ' Runs without error
      Print "Shaded after merge"
End If
      
There are some problems
Depending in whether I select to print to PDF or to Printer I either get
For PDF shading works but some images are missing
For Printer Prints perfectly if (template.dot) but there is no shading
For Printer Prints perfectly if (template.dotx) but there is no shading and there are some missing and or repeated images.

I have read elsewhere that 201/2013 style Templates are known to be problematic with this use case.

Some background to the application

The source data I need to print is located in a Lotus Notes database.  The code I am writing is mainly in Lotusscript.  A template has been developed in Word and is distributed to the users workstation by the Notes application along with a customised normal.dotm file which is placed in the appropriate place on the C: drive.  This normal.dotm file contains a script called shade (which is what I want to run).

The word merge document and its datafile (which is in excel) work fine.  They are invoked from lotusscript and typically run in the background,  there is a diagnostic mode where they are made visible.

The output file of the merge has 1 to many pages, and each page contains a table.  The code I want to run looks at every table and based on 4 fields in the data (file repeated for each page). chooses whether or not to change the background colour of certain cells.  this all works fine.  If I make Word run in the foreground I can invoke shade using: Developer - Macros - Select the global template - select Shade - click run and the table is shaded appropriately.
Steps.pdf
steps.png

Have you tried using the Word PrintOut method:

wordobj.ActiveDocument.PrintOut

Select allOpen in new window

Good Evening Graham

Not sure why I'd want to try wordobj.ActiveDocument.PrintOut but I have and I get the same result.

Surely there must be a way to get a handle on the merged document before it is printed so I can shade the table.  The only think I can shade is the Template. Alternatively there is perhaps a way to shade the background of a cell based on the value of a field in the data file.

Basically all I am trying to achieve is colour the background of a cell Orange if a corresponding field has a value of "Yes" else leave it yellow.

Thanks for your help on this.

A secondary problem is that  is entirely inconsistent whether or not my images appear in the merged data.  That's why I am using a .dot template, but, even that is inconsistent.  A .dotx template is certainly worse.

After the merge, the result document in the ActiveDocument, so that needs specified to be in the 'Shade' macro.

Graham

Here is the Shade macro...

Sub Shade()
'
' Shade Macro
'
'
Dim objExcel As Object
Set objExcel = CreateObject("Excel.Application")

Dim Row0SSH As String
Dim Row1SSH As String
Dim Row2SSH As String
Dim Row3SSH As String

Set exWb = objExcel.Workbooks.Open("C:\StdOpsV3\XLData.xlsx")


Dim PageCounter As Integer
PageCounter = 1
Dim oTbl As Table
Dim oCel As Cell
Dim oRng As Range
Dim cellcount As Integer
Dim doshade As Boolean

For Each oTbl In ActiveDocument.Tables
    cellcount = 0
    For Each oCel In oTbl.Range.Cells
        doshade = False
        cellcount = cellcount + 1
        Set oRng = oCel.Range
        oRng.End = oRng.End - 1
        Select Case cellcount
        Case 1
            PageCounter = PageCounter + 1
            Row0SSH = exWb.Sheets("Sheet1").Cells(PageCounter, 4)
            Row1SSH = exWb.Sheets("Sheet1").Cells(PageCounter, 5)
            Row2SSH = exWb.Sheets("Sheet1").Cells(PageCounter, 6)
            Row3SSH = exWb.Sheets("Sheet1").Cells(PageCounter, 7)
        Case 14, 15
            If Row0SSH = "Yes" Then
                doshade = True
                oCel.Shading.BackgroundPatternColor = RGB(222, 129, 0)
            End If
        Case 21, 22
            If Row1SSH = "Yes" Then
                doshade = True
                oCel.Shading.BackgroundPatternColor = RGB(222, 129, 0)
            End If
        Case 28, 29
            If Row2SSH = "Yes" Then
                doshade = True
                oCel.Shading.BackgroundPatternColor = RGB(222, 129, 0)
            End If
        Case 35, 36
            If Row3SSH = "Yes" Then
                doshade = True
                oCel.Shading.BackgroundPatternColor = RGB(222, 129, 0)
            End If
        End Select
        If doshade Then
            ' this is every cell in Quality Section
        Else
            ' These are cells other than Quality Cells
        End If
    Next
Next
End Sub

Select allOpen in new window

It seems that it works if I specify...
OutputFormat as "PDF" in this code...

With maindoc.MailMerge
		If OutputFormat = "PDF" Then 
			.Destination = wdSendToNewDocument
		Else
			.Destination = wdSendToPrinter	
		End If
		.MainDocumentType = wdFormLetters
		.SuppressBlankLines = True
		' No need to OPEN the datasource as this is fixed and defined int he template.
		With .DataSource	
			.FirstRecord = wdDefaultFirstRecord
			If WhatToPrint = "All" Then
				.LastRecord = wdDefaultLastRecord
			Else
				.LastRecord = 1
			End If
		End With
		.Execute true 'False  (pause)		
	End With

Select allOpen in new window

I infer from this that ActiveDocument is valid for merge printing to PDF but not for Direct to Printer.  I actually print to 'Microsoft XPS Document Writer' for testing.

Am I missing something?

Regards Steve

It seems what I was attempting was hard or impossible to achieve in MS Word.  In MS Publisher it was child's play.