Run Word macro stored in different doc (OpenInsight 16-Bit Specific) [Revelation On-Line Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]


==== Run Word macro stored in different doc (OpenInsight 16-Bit Specific) ====
=== At 05 JUN 2002 07:26:09AM Oystein Reigem wrote: ===
 
{{tag>"OpenInsight 16-Bit Specific"}}
 
<QUOTE>
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 -




</QUOTE>
----

=== At 05 JUN 2002 07:51AM Don Miller - C3 Inc. wrote:  ===

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

</QUOTE>

----

=== At 05 JUN 2002 08:16AM Oystein Reigem wrote:  ===

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





</QUOTE>

----

=== At 06 JUN 2002 08:00AM Oystein Reigem wrote:  ===

<QUOTE>Got a bit further since yesterday.



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



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 [i] might [/i] 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 -



</QUOTE>

----

=== At 07 JUN 2002 04:51AM Oystein Reigem wrote:  ===

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





</QUOTE>

----

=== At 07 JUN 2002 07:05AM Oystein Reigem wrote:  ===

<QUOTE>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 [i]originator[/i] of the depicted threepiece suit. Now what if these originator fields are [i]multi-valued[/i], containing [i]more than one[/i] 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!:

[img]http://www.hit.uib.no/oystein/scratch/form.gif[/img]



- Oystein -



</QUOTE>

----

=== At 07 JUN 2002 09:02AM Bob Carten wrote:  ===

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

</QUOTE>

----

=== At 07 JUN 2002 10:28AM Oystein Reigem wrote:  ===

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



</QUOTE>

----

=== At 07 JUN 2002 04:48PM Bob Carten wrote:  ===

<QUOTE>

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



</QUOTE>

----

=== At 10 JUN 2002 07:04AM Oystein Reigem wrote:  ===

<QUOTE>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 [b]text file[/b], the result is partly in Courier New 10.



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

[size=12]Name: Långstrump, [/size][size=10]Pippilotta Viktualia Rullgardina Krusmynta Efraimsdotter[/size]



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 -



</QUOTE>

----

=== At 10 JUN 2002 09:21AM Oystein Reigem wrote:  ===

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



</QUOTE>

----

=== At 10 JUN 2002 09:41AM Oystein Reigem wrote:  ===

<QUOTE>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 [i]simple tables put on top of each other[/i]. 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 -

</QUOTE>

----

=== At 10 JUN 2002 09:45AM Oystein Reigem wrote:  ===

<QUOTE>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 [i]simple tables put on top of each other[/i]. 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 -

</QUOTE>

----

=== At 10 JUN 2002 09:47AM Oystein Reigem wrote:  ===

<QUOTE>Close enough. Grrr. :-)



- Oystein -



</QUOTE>

----

=== At 11 JUN 2002 08:18AM Oystein Reigem wrote:  ===

<QUOTE>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 [i]default font[/i] 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 -





</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=6882DDB6353CD3AF85256BCF003ED1CC|View this thread on the  forum...]]