Solved

Invoke VBA from lotusscript after mailmerge

Posted on 2014-12-13
11
223 Views
Last Modified: 2014-12-25
I invoke MS Excel and MS Word 2013 from IBM Notes R9 to perform a mail merge to PDF.  After the merge (and before creating the PDF) I need to run a macro on the merged data.  I have the macro working.  My problem is I cannot invoke it via lotusscript.  It is in the Word Merge Template and also in Normal.dotm.

I get errors like
Instance member SHADE does not exist
Instance member MODULE1 does not exist
Microsoft Word: 'Run' is not a property.
depending on how I attempt to invoke it.
If I invoke manually it works fine.
0
Comment
Question by:Steve-Johnson
  • 3
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40498657
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?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40499831
HI,

Have you tried ?

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

Regards
0
 

Expert Comment

by:Steve Johnson
ID: 40500542
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?
0
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 40500550
I suppose you have to activate beforehand the right document
0
 

Expert Comment

by:Steve Johnson
ID: 40500588
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
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40501267
Have you tried using the Word PrintOut method:
wordobj.ActiveDocument.PrintOut

Open in new window

0
 

Author Comment

by:Steve-Johnson
ID: 40501338
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40501465
After the merge, the result document in the ActiveDocument, so that needs specified to be in the 'Shade' macro.
0
 

Expert Comment

by:Steve Johnson
ID: 40502311
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

Open 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

Open 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
0
 

Accepted Solution

by:
Steve-Johnson earned 0 total points
ID: 40511061
I have abandoned this project and so no longer require a solution.  I am now using MS Publisher to do the merge and it is so much simpler and just works.
0
 

Author Closing Comment

by:Steve-Johnson
ID: 40517458
It seems what I was attempting was hard or impossible to achieve in MS Word.  In MS Publisher it was child's play.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now