Solved

Invoke VBA from lotusscript after mailmerge

Posted on 2014-12-13
11
236 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 49

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
LEFT JOIN Access Query 5 66
Access 2016 - finding skipped numbers 11 26
Where did System.Data.Objects go? 2 29
Finding a closest match in Excel 7 48
IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

831 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