I need to run a Word macro from my app:

Set_Property( @Window, "DDECOMMAND", 'Macro1()' )

This works well as long as the macro is stored in Normal.dot or in the target document (the open document) itself. But I get a problem when the macro is stored in a different document than the target document. All the possible target documents reside in the same folder, and I'd like the macro to be stored in a separate MacroRepository.doc or .dot file in the same folder.

Must I somehow first attach that other MacroRepository template to make the macro available? In case - how?

Must I also refer to the macro with a different and more specific syntax (including template and/or project and/or module)? In case - what exactly is that syntax?

- Oystein -

Oystein ..

My recollection is that the document in which the macro (or any other target, for that matter), must be open in Word in order for DDE and the like to function. I think the reason for that is that Word itself processes the macro in the context of the currently open document. It might be possible if the document containing the macro was embedded in all documents (somewhat like normal.dot). Interesting problem, though .. but then again, all of yours are.

Don M.

See-thru-Ink

"There are some mistakes that are way too much fun not to repeat"

Don,

What I want to do is a Word mail merge, with some pre- and/or post-processing. I have put all the necessary programming into one Word macro. It's this macro I'd like to store in a separate MacroRepository.doc or MacroRepository.dot.

As you know a Word mail merge inserts data from a "data source" into "merge fields" in a "main document". The target documents I talk about are the "main documents" of the merge. My app writes the data to be merged to a "data source" - a delimited text file with a header line with field names. Then it uses DDE to open the relevant "main document". Finally it runs the macro, also with DDE.

The merge "main documents" will come from two sources - myself (the developer) and my clients. I'll supply the system with some basic "main documents". Knowledgeable clients will add their own.

I assume the solution to my specific problem is to have all "main documents" based on MacroRepository.dot. Clients who make their own "main documents" must start out from MacroRepository.dot.

I'd still like to know the answer(s) to my original question(s). For next time.

- Oystein -

Got a bit further since yesterday.

But here's a different question: What's the syntax for DDE-calling a macro that's got parameters.

I thought the following syntax must be correct:

DDECommand=Macro1('XXX')"

UnUsed=Set_Property( @Window, "DDECOMMAND", DDECommand )

('XXX' is the parameter).

But I get a "Compile error" message. The error is in macro TmpDDE, which I think must be a temporary macro that Word (or whatever) creates when a macro is called via DDE. Here's the offending TmpDDE:

Sub TmpDDE()

Macro1()'XXX') End Sub You can clearly see the messed-up Macro1 call. Next I tried DDECommand=Macro1 'XXX'" Result: A different error in a different-looking TmpDDE - "Argument not optional": Sub TmpDDE() WordBasic.Call "Macro1" 'XXX' End Sub Next I tried DDECommand=Macro1 "XXX"' Result: A different error again - "Expected Sub, Function or Property": Sub TmpDDE() Dim Macro1 Macro1 "XXX" End Sub Next I tried DDECommand=Macro1 XXX'" There are no error messages, but nothing happens. The VB Debugger tells me Macro1 isn't run at all. Probably what happens is the system tries to run a macro with the name 'Macro1 XXX'. So now I'm anxious to know if somebody knows the right syntax. The error messages I get make sense of course, but why can't I get a proper TmpDDE, like Sub TmpDDE() Macro1('XXX') End Sub or something? Btw - is it possible to get an error status from Word back to the app? DDEERROR consistently returns OK (0). Except when once I had got a duplicate TmpDDE in my system - one from an earlier and trivial DDE call that for some reason didn't get deleted. - Oystein - </QUOTE> —- === At 06 JUN 2002 12:21PM M Plasko wrote: === <QUOTE>Oystein, I've not tackled this subject, but this link might be helpful: http://msdn.microsoft.com/library/en-us/dnoffdev/html/vsofficedev.asp?frame=true </QUOTE> —- === At 06 JUN 2002 12:22PM M Plasko wrote: === <QUOTE> </QUOTE> —- === At 06 JUN 2002 04:01PM Bob Carten wrote: === <QUOTE>Oystein: the following is an example of how to do this with ole automation. It will not work without editing for your needs. save the following as doMerge.vbs Edit it to reference the macro document and name you want to run, see if it helps. to call it you would use soething like cmd=doMerge.vbs ' : docname : ' ' : datafile : ' ' : macrofile call Utility('RUNWIN', cmd ) hth Bob ' DoMrg.vbs ' Description: ' Open An Instance of MS Word ' Run a macro to perform a multi-value friendly mail merge ' ' Arguments ' (0)=Name of the merge text document, ' (1)=Name of the merge data document ' 'On Error Resume Next Option Explicit Dim oWrd Dim oMergeOut Dim strMainDoc Dim strDataDoc Dim strMacroDoc dim scriptpath dim scriptfullname dim scriptname dim oMainDoc dim ofs Dim i ' Get Arguments if wScript.Arguments.Count ]1 then strMainDoc=WScript.Arguments(0) strDataDoc=WScript.Arguments(1) else msgbox "Merge Script:Missing Arguments" strMaindoc=" strDataDoc=" end if if wScript.Arguments.Count ] 2 then strMacroDoc=WScript.Arguments(2) else strMacroDoc=cm_merge.dot" end if scriptfullname=Wscript.ScriptFullName scriptname=Wscript.ScriptName scriptpath=left(scriptfullname, Instr(1,scriptfullname,scriptname)-1) strMacrodoc=scriptpath & strmacrodoc 'Start Word unless already running set oMainDoc=WScript.GetObject(strMainDoc) set oWrd=oMainDoc.Application ' Comment this line to make it invisible oWrd.visible=true ' Include the Macro File oWrd.AddIns.Add strMacroDoc, -1 ' Perform the merge With oMainDoc.MailMerge .SuppressBlankLines=True .MainDocumentType=0 'msgbox strDatadoc .OpenDataSource strDatadoc, 0, False, True, True, False, "", "", False, "", "", "", "", "" With .DataSource .FirstRecord=1 ' wdDefaultFirstRecord .LastRecord=-16 ' wdDefaultLastRecord End With .Execute True End With '– ' Turn multivalues into table rows ' Note the full path to the macro, as in MacroSheet.Module.MacroName ' not just macroname '– Set oMergeOut=oWrd.Documents(1) oWrd.run "MultiMerge.NewMacros.Expand_Mailmerge_Tables" oMergeOut.Repaginate oMergeOut.PrintOut False dim oDoc if oWrd.Visible then for each oDoc in oWrd.Documents odoc.Close 0 next else oMergeOut.Close False oMainDoc.Close end if ' Delete data file set ofs=Createobject("Scripting.FileSystemObject") ofs.DeleteFile strDataDoc set ofs=nothing set oMainDoc=nothing set oMergeOut=Nothing set oWrd=Nothing </QUOTE> —- === At 07 JUN 2002 04:04AM Oystein Reigem wrote: === <QUOTE>Mark, I found the solution to the parameter problem. First I had to make sure the parameter - a String - was enclosed in double quotes. That would be obvious to everybody more familiar with VB programming than me. Second, there is a trick. Inside Word I had to rename my macro to Macro1, i.e, append two underscores to the name. For some reason, Word adds two underscores to the name of the macro when passed to it using DDE. It seems to happen only with macros that have parameters.

Here's a web article that describes two ways of passing parameters to a macro in Word, from a program called Dragon NaturallySpeaking: . The first method is the one I use from my OI app - by DDE. The article describes the trick I mentioned.

The second method works by first sending a special keystroke (e.g, Shift+Ctrl+Alt+F) activating the macro, then sending a parameter as keystrokes, which the macro captures with an InputBox. (An Enter keystroke appended to the parameter closes the InputBox.) The special keystroke must be defined as a shortcut key in Word. The article claims this method is more reliable than DDE. I don't know if it can be made to work from OI. I'd be interested to know. Who can tell me that?

- Oystein -

Bob,

Great! Now what kind of tool do I need to work with VBSs? I've got piles and piles of CDs through my MSDN subscription. Visual Studio .NET? VBA SDK? Or will plain ol' Word do???

- Oystein -

Bob,

It would be interesting to see that Expand_Mailmerge_Tables macro of yours. I don't know if you're willing to reveal the source.

I've known for a long time I need some mv-expanding programming, and I'm certainly capable of writing my own, but it's time-consuming and dull work…

Another question: How complicated are your tables, i.e, the places in your Word documents where you put your multivalues? I think the tables must be fairly simple for a general expansion macro to work.

Here's an example of the kind of output some of my clients might want to see. It's got a table with a complex structure, containing all output fields of the record. Now take that area I've highlighted in yellow. It contains a few fields describing the originator of the depicted threepiece suit. Now what if these originator fields are multi-valued, containing more than one originator? I can't see how the yellow range of cells could be expanded unless with a fairly advanced program. If only Word tables could be nested, making the structure more modular!:

- Oystein -

Oystein

you get vbScript with win98 or above,

or IE 4.0x or above.

or [url=http://msdn.microsoft.com/scripting]download the windows scripting host

The code I'm showing works with word97 or above.

It's easier in Word2000 where you can use the Split function.

Wrote this some time ago. Bet I could have used @vm instead of Tilde.

Assumes that you have tables set up in word with a row of merge fields in line 1 or two or the table, then in the merge data you have converted @vm to tilde.

Another fun trick is to simply write the entire document as html, then open it in word. Try taking your document, leave placeholders, e.g. {FIELDNAME} where you want fields to go, save as html, see if you can osread it, swap out the placeholders for real fields, oswrite as html, then open it in word, print from there. Bet you can nest tables if you do it with html. This should work for word 2000 or better. Word XP actually stores docs as XML. SWAP is our friend.

Bob

Dim oMergeOut As Document

Option Explicit

Sub Expand_Mailmerge_Tables()

'

'Purpose:

' Allow merge data to contain multi-valued fields

' After the merge, convert values to rows in tables

' Only works when mv fields are in tables

'

' ASSUMES that the multivalues are delimited by the

' DELIM$ constant

'

Const DELIM$=~"

Dim strContent As String

Dim strCellText As String

Dim oCell As cell

Dim oRow As Row

Dim oTable As Table

Dim oDataRow As Row

Dim MyRange As Range

Dim lDataRow As Long

Dim lPos As Long

Dim lstart As Long

Dim lLen As Long

Dim lColCount As Long

Dim i As Integer

Dim j As Integer

Dim lDelimCount As Long

On Error GoTo ErrorHandler

Set oMergeOut=Application.Documents(1)

If oMergeOut.Tables.Count=0 Then

  ' Nothing to do

  Exit Sub

End If

For Each oTable In oMergeOut.Tables

' Only process one or two row tables

' They are title/data or data

Select Case oTable.Rows.Count

 Case 0

   ' Nothing to do

   lDataRow=0

 Case 1

   ' Use first row as data row

   lDataRow=1

 Case Else

   ' Assume first row is headings, use second

   lDataRow=2

End Select

' Have Data?

If lDataRow ] 0 Then

  Set oDataRow=oTable.Rows(lDataRow)

  lColCount=oDataRow.Cells.Count

  ' Is it delimited?  tilde count tells me how many rows to make

  ' Want tilde count to equal the max of all cells

  ' Need to remember that data count is count(strcontent,'~') + (strcontent  "")

  lDelimCount=0

  For Each oCell In oDataRow.Range.Cells

      Set MyRange=oCell.Range

      MyRange.MoveEnd Unit:=wdCharacter, Count:=-1

      strContent=MyRange.Text

      i=0

      lstart=1

      lPos=0

      lLen=Len(strContent)

      Do

          lPos=InStr(lstart, strContent, DELIM$)

          If lPos=0 Then lPos=lLen

          If lstart  lLen

      If i ] lDelimCount Then

          lDelimCount=i

      End If

  Next oCell

  ' Create an array of data by row, column

  If lDelimCount ] 0 Then

      ReDim aCells(lDelimCount, lColCount)

      For i=1 To lDelimCount

          For j=1 To lColCount

              aCells(i, j)="

          Next

      Next

      i=1

      For Each oCell In oDataRow.Range.Cells

          Set MyRange=oCell.Range

          MyRange.MoveEnd Unit:=wdCharacter, Count:=-1

          strContent=MyRange.Text

          j=1

          lstart=1

          lPos=InStr(lstart, strContent, DELIM$)

          Do

              ' store this cell

              If lPos=0 Then lPos=Len(strContent) + 1

              lLen=lPos - lstart

              strCellText=Mid(strContent, lstart, lLen)

              aCells(j, i)=strCellText

              'read next cell

              lstart=lPos + 1

              lPos=InStr(lstart, strContent, DELIM$)

              j=j + 1

          Loop Until lstart ] Len(strContent)

          i=i + 1

      Next oCell

  End If

  ' Want to put it back into table

  ' Add new rows in reverse order, since insert to table will reverse the order too

  For i=lDelimCount To 1 Step -1

      Set oRow=oTable.Rows.Add(BeforeRow:=oTable.Rows(lDataRow))

      j=1

      For Each oCell In oRow.Cells

          oCell.Range.InsertAfter Text:=aCells(i, j)

          j=j + 1

      Next oCell

  Next

  ' delete data row that contained the data

  oTable.Rows(lDataRow + lDelimCount).Delete

End If

Next oTable

Set oTable=Nothing

Set oDataRow=Nothing

Exit Sub

ErrorHandler:

Dim x As Integer

x=MsgBox(Err.Description, vbExclamation, "Merge Error")

Set oTable=Nothing

Set oDataRow=Nothing

Exit Sub

End Sub

Bob,

Thanks a lot. I'll have a look at it over the weekend. First priority now is to get my family and myself to the beach. It's a balmy 26C (79F) and not a polar bear in sight.

The Windows Scripting Host… Right. But if I'm up to it I could make a VB app instead, right? If I'm worried about enabling the Windows Scripting Host.

You also make me aware of there being different versions of Word around (at clients, I mean), so I shouldn't use features incompatible with older versions.

I've already thought about html and tables, and using a web viewer instead of Word. The problem is page breaks. I need some control over page breaks.

- Oystein -

Check out what you can do with Flash and or Adobe.

Those products may be more controllable than plain html and simpler to use than word.

I am not familiar with the details, but each company is touting their latest and I am hearing good things from other users. The trick is if you can drive them with some sort of markup language like XML, and the trend is toward using markup, then it will be easy to drive them from OI.

Bob

Bob et al,

I agree XML is a Good Thing.

You mention alternatives to Word Mail Merge. Let me recapitulate what's nice about Word Mail Merge:

- With just a little extra knowledge, and not too much effort, users can create their own output formats, using a tool they know

- No extra software or expenses for client, neither to run reports nor create output formats

- Page break control!

Unless the alternatives solve these problems I think I'll stick with Word Mail Merge…

…at least if I can get around one remaining problem with formatting in Mail Merge: Sometimes the font and font size of the source document carries over to the finished result.

I have the main document in Times New Roman 12. When the source document is a Word file with Times New Roman 16 or Arial 16, the result is fine. No trace of neither Arial nor 16 points text in the finished result.

But when the source document is a Word file with Courier New 10, or, crucially, a text file, the result is partly in Courier New 10.

To be more precise: The first word of each merge field is ok. The rest of the words are in Courier New 10. Example:

Name: Långstrump, Pippilotta Viktualia Rullgardina Krusmynta Efraimsdotter

I think it must be a bug in Word Mail Merge. But I can't find anything written about it on the web. And I can't find any parameters (MailMerge properties) to override the rogue formatting.

Help!

I use Word 2000.

- Oystein -

Bob,

Thanks. Your Expand_Mailmerge_Tables() macro works like a charm.

I might change it to make it able to handle Word tables that are a bit more complicated, like the following. I hope it survives my formatting and the processing at the discussion site. And excuse me for using a rough html substitute:

colours numbers black~white one~two~three

people boy~girl

Now this isn't really a very complicated table. It's just five simple tables put on top of each other:

colours numbers black~white one~two~three

people boy~girl

So even if it's one table as Word sees it, one can "add rows below" without punity.

I think of replacing your loop over tables with a double loop over tables and rows, and for each row look for evidence of multivalues. That evidence could be the delimiter character, or it could be an explicit code put there by the creator of the document:

colours numbers mvblack~white one~two~three

people mvboy~girl

- Oystein -

Bob,

Now that formatting of mine wasn't too good. What I meant was:

I might change the macro to make it able to handle Word tables that are a bit more complicated, or to be more precise - Word tables that are made out of simple tables put on top of each other. Now pretend these five tables are glued together as one table:

Even if it's one table as Word sees it, one can "add rows below" without punity.

I need a double loop over tables and rows, and for each row look for evidence of multivalues. That evidence could be the delimiter character, or it could be an explicit code put there by the creator of the document, e.g some unique character string in the first cell of all the rows that may contain multivalues.

- Oystein -

Bob,

One last try…:

I might change the macro to make it able to handle Word tables that are a bit more complicated, or to be more precise - Word tables that are made out of simple tables put on top of each other. Now pretend these five tables are glued together as one table:

Even if it's one table as Word sees it, one can "add rows below" without punity.

I need a double loop over tables and rows, and for each row look for evidence of multivalues. That evidence could be the delimiter character, or it could be an explicit code put there by the creator of the document, e.g some unique character string in the first cell of all the rows that may contain multivalues.

- Oystein -

Close enough. Grrr.

- Oystein -

The font problem I reported occurs when the main document is in Times New Roman. I now tried to change the font to Arial in the whole or parts of the main document. Wherever I change the font to Arial, the output is fine. I guess it must be something special with Times New Roman. The only thing I can think of is that Times New Roman is the default font of my main document. If that hypothesis is right, and I really need Times New Roman in my main doc, I can work around the problem by having a different deafult font.

Sorry for bothering you all with this mail merge stuff. Are there anybody besides Bob Carten who's had any use for it?

- Oystein -

View this thread on the forum...