Hello all:

We are attempting to merge a text file with a Word document. We've set up an ASCII export that Word then dynamically opens and runs a macro - works great on a single-user system.

On a LAN however, we have people running the same export/merge process against different data, which means people are overwriting each other's text files. We can dynamically rename the text file that is being written by pre-pending the user's initials, but then Word fails, as it is looking for a hard-coded text file.

We think we need to be able to dynamically tell Word the name of the text file (data source) to look, based on who exported the data.

The two approaches we are thinking of:

1) As part of our RUNWIN statement, is there a command switch that can be used to tell Word the name and path of the text file? (this is more of a Word question, but hey, this is a bright group…)

2) Failing that, is there a way via OI to write some type of Windows environmental variable that we can then have Word pick up and use as the data source?

Thanks much,

Mike O

Might it be possible (and easier) to write the datafile to drive c:?

The Sprezzatura Group

World Leaders in all things RevSoft

Mike,

I think I have the solution for you.

Here's what my app does:

- Checks if Word is running, with a Windows API function

- Starts Word with RUNWIN if it isn't running already

- Checks again for good measure

- Tells Word via DDE to run a certain macro. The macro runs a mail merge. The way the macro knows which file to use is through a parameter.

Without a parameter the DDE call is something like

… … … 'macroname' … … …

With a parameter containing a file name the DDE call is something like

… … … 'macroname "filename"' … … …

There seems to be a trick that's necessary to run a macro with parameters with DDE. The macro's real name must be macroname__ and not just macroname.

And there seems to be a problem if you need two parameters. I need two parameters. But that's easy to work around.

Hope you can wait until tomorrow for a complete answer. I haven't got the latest version of my app with me where I am now. Tell me if you're interested.

- Oystein -

We use Word Merge a lot in our application. We've found that DDE works well as a way to pass the parameters to Word.

We have a hidden editline control on the main application window from which Word pulls out the parameters. The parameters are just a delimited string which is broken up in the VB macro code.

The parameters we pass include the export file, whether they want a preview (i.e. generate the merge) or a printout (generate the merge, print it and then close down the temporary documents), and whether Word was already open beforehand. If Word was not already open, and they just wanted an instant printout, then we close Word automatically at the end of it.

I hope that helps.

Cheers,

Paul

Paul,

The parameters are just a delimited string which is broken up in the VB macro code.

So you've fused several parameters into one parameter. Is the reason the same as mine - a problem with passing more than one parameter?

- Oystein -

Mike

I couldn't help but chip in here. This is a definitive solution if you are using Word 2000 or above:-

1) Set up your Word Template in the normal fashion.

2) Remove the pointers nto the header and data files

3) Save it

4) From OI export you data to an ascii file with the station Id appended to the name of the file (I use both header and data files)

5) Export a text file with the name of the document to merge, the names of the the above files and any other info you want to pass to the a vba macro, at the same time

5) Load a template blank via DDE which includes an autoopen macro

6)In the autoopen macro read the text file (5), load up the document you want to merge, apply the header and data files to the document and then run the merge.

We do sophisticated OI to Word merging so if you have any more questions please ask.

Bob Watson

I take that back - i'm sure there are other ways…

That's the way I handle a mail merge from ARev 3.12 to WordPerfect 9.0. The template documents reside on the network and the merge text file goes to the C: drive.

The template documents can be shared and there is no worry about the merge files being overwritten by other users or having to deal with additional parameters passed to the macros.

Not always possible, eg on Citrix, every one has the same drive letter if using C: and not the users own C drive which could be X or Y or ?

Could write two text files.

One which is contains the data, and one which contains the filename where the data lives.

Would then work for all users, and providing no two run it at the same time (within a few seconds) should then be OK.

Colin

]]Not always possible, eg on Citrix, every one has the same drive letter if using C: and not the users own C drive which could be X or Y or ?«

Not really fair as with Citrix Metaframe one would normally set it up so M,N etc are server drives and C: really does mean the users C: drive so I think Sprezz idea is the right approach.

Tony

Bob,

…if you have any more questions please ask.

I have a question: Mail Merge is fine when you print one record per page. But have you done reports with many records per page, e.g, columnar reports? (I have a solution that I'm not 100% happy with.)

- Oystein -

Tony - and from what we've seen the simplest by far

The Sprezzatura Group

World Leaders in all things RevSoft

Not really fair as with Citrix Metaframe one would normally set it up so M,N etc are server drives and …

Be careful of using M if you're using Exchange 2000. "M" is automatically mapped by the system for Exchange kinda of things…such is the way of the world according to msc.

Ray

Oystein

Here are two alternatives:-

1) Export the data to a text file in row/column format. Then use the DATABASE field to load your text into a table in a merged Word document. The first row can be your column headers. You can use the switches to make the table look the way you want.

for example

{ DATABASE \d "S:\\LSPEED\\WORD\\DATA\\EASEME~T.001" \s "SELECT * FROM S:\\LSPEED\\WORD\\DATA\\EASEME~T.001" \l "1" \b "191" \h }

2) For much more control - Export the columns to a text file as seperate items of data (i.e. they don't have to be in row/column format). Export as well a text file with the names of the columns to include in your report and the text to go in the column headers. Load the templete to be merged (or a blank templte with only code in it which then loads the template - this is better). After the merge has been completed, in a post-merge macro, use VBA to insert a table with required rows and columns and plug your data in. I would have a generic routine to do this so you do many reports easily. This way you can do fancy reports in Word from OI.

Bob Watson

]

You can skip this step if your not moving your app around

Bob Watson

Yep - quite right.

Just show you how one might be led up the garden path (see my earlier post on this subject)….

In mitigation my reply was prompted by years of producing merged documents from multiple templates at once, containing tables, reports in a variety of formats either as single documents as as seperate documents. The only solution was a lot of vba. So it was just as easy at the time to apply the following two commands to a template to ensure no conflicts.

.OpenHeaderSource Name:=headName

.OpenDataSource Name:=dataName

One advantage is that all the exported data is in one place - makes it easier to support.

Bob Watson

Oh we've done it the other ways when we had to - for example a report that was customised to the language of the user, included pie charts, photos, repagination based on different paragraph lengths due to language differences, mini-spreadsheets and the like. Read the location of the data file from another data file and made branching decisions based upon the content of the data files to use other data files.

Took months to get the desired effect and it was stunning (even if we do say so ourselves). And it cost tens of thousands .

However in this case… (Interestingly this is a solution that has been made possible by the ubiquitousness of cheap hardware. Used to be diskless workstations were all the rage to save money - haven't seen one of those for a while).

The Sprezzatura Group

World Leaders in all things RevSoft

You must be in the USA surely - you can't be replying at 4AM for God's sake!

Yes - I must write a thousand times - "you have a local hard drive - use it…"

UK and up and about early… (Same goes for sort files and temp file locations as well for AREV bods )

The Sprezzatura Group

World Leaders in all things RevSoft

Hi Ray

Hopefully no one would be daft enough to put Exchange and Citrix on the same box!! But if they did then that is all configurable. (99% sure!)

When I first used Citrix the idea of the local server disk being M: made me feel all wobbly! but in fact it is much more natural to the users. They really can treat a published app just as if it's local.

Tony

Bob,

Thanks for the ideas.

Just one question about your second alternative:

2) For much more control - Export the columns to a text file as seperate items of data (i.e. they don't have to be in row/column format). Export as well a text file with the names of the columns to include in your report and the text to go in the column headers. Load the templete to be merged

(This is the same as I do - so far.)

(or a blank templte with only code in it which then loads the template - this is better). After the merge has been completed,

What kind of merge are you referring to? I ask because….

in a post-merge macro, use VBA to insert a table with required rows and columns and plug your data in.

…here you seem to say it's your post-merge macro that inserts the data.

I would have a generic routine to do this so you do many reports easily. This way you can do fancy reports in Word from OI.

What I currently do is the following:

- I have a mail merge main document containing a table with one single row, each cell containing a merge field

- I have a separate document with the headers. The header texts are in a table with the same column widths as the main document

- I run the mail merge

- I run a post-process that

- - (a) inserts the header document at the top

- - (b) runs through the whole document and removes the section and paragraph breaks, fusing all the on-line tables into one table.

But I don't get headers on each page with this method. That would have been nice. Oh, but I can. I can set that header table row to Heading Rows Repeat in the Table menu.

- Oystein -

Tony,

Hopefully no one would be daft enough to put Exchange and Citrix on the same box!!

No I wasn't speaking about putting Citrix and Exchange on the same box, but it is possible to have Exchange and OI on the same box with W2K Server (e.g, see W2K SBS Server). If you want to maintain uniform drive mapping for all users regardless of workstation to access your OI application, e.g., "O", then you want to make sure you don't conflict with something else, such as "M" if that's used by Exchange.

But if they did then that is all configurable. (99% sure!)

Being that Microsoft has standardized on "M" for Exchange mail stuff, and being that we use Exchange, it's easier to go with the flow . It seems to me that it's easier for me to make our OI app "O" than to change Exchange . I'm no expert on Exchange, but you'll find lots of references to Exchange and the M drive on the web, etc. Therefore, if you were to use Exchange be careful of using "M" for your OI app. I'm sure it is changeable, but the question I would ask myself is "why"

Tally ho,

Ray

Ray

We're in danger of wandering off in a world of our own here but I was interested by the reference to Exchange and M drive.

We use Exchange 5.5 (and also 2000 in test mode on ADS server) and don't have any M: drives mapped on clients. We use Outlook client and as far as I know it just used UNC path or even IP to find the exchange server.

More than that, our data server that runs Exchange has an M: drive mapped to another server entirely for replication purposes (nothing to do with Exchange).

Am I missing something here as I haven't seen anything about M:. What else where you refering to?

Now you mention it our Exchange 2000 Server has a virtual drive (kicks off Terminal Services Client to Sprezz-Server) labelled "Exchange M:"… hmm the plot thickens.

The Sprezzatura Group

World Leaders in all things RevSoft including credit where credit's due

Thanks much to everyone who has responded - plenty of options to choose from at this point.

Best regards,

Mike O'Neal

I haven't tried passing multiple parameters - we just went down this route based on the advise of someone else (possibly they ran into this problem?).

We do the following:

Our main application window has hidden client and server DDE controls.

The macro is stored in a protected Word document directly under our application.

Show window (in the last step) is set depending on whether the user choose to see a preview of the report. Our argument includes the location of the merge data file and the merge template.

client.ctrl=COLLECT.DDECLIENT'

server.ctrl=COLLECT.DDESERVER'

argument=Whatever you want to pass, with whatever delims'

ctrls=client.ctrl: @rm: client.ctrl: @rm: client.ctrl: @rm: server.ctrl

props=DDESERVICE': @rm: 'DDETOPIC': @rm: 'DDELINK': @rm: 'TEXT'

vals=WINWORD': @rm: 'SYSTEM': @rm: 'HOT': @rm: argument

dum=Set_property( ctrls, props, vals)

*attach the macro document template

dum=Set_Property( client.ctrl, 'DDECOMMAND', 'AddAddIn "': drive():'\wordmrg.doc': '", 1')

dum=Set_Property( client.ctrl, 'DDECOMMAND', 'ToolsMacro .Name=VSL_MailMerge", .Run, .Show=: show.window: ', .Description=", .NewName =" ')

---

Sounds like you might be doing something similar.

Cheers,

Paul

Paul,

Thanks.

That

'AddAddIn "': drive():'\wordmrg.doc': '", 1'

command of yours looks interesting. I did manage to get the template added in by using AddAddIn, but I still couldn't run the macros. It could perhaps be that 1 parameter of yours that makes the difference. I can't remember the details, but I see from my notes I tried with no parameter, and with a t parameter.

I'll take a new look at this in a week or two.

- Oystein -

The Word security settings need to be set to Low if you have an unsigned macro document, on Word 2000 and above. On Word 97 the Add In process does not go through the security check, but Microsoft fixed this up.

If you're using macros then you probably have the security set this way already.

Paul

Oystein

]

What I'm referring to here is to actually create the report using the tables.add method in vba. I use a place marker in the document to tell the code where the report is to be placed. I insert a section break at that point and then add table new rows to a table one at time while plugging in the data. I can set the table to repeat headings across page breaks and to appear exactly the way I want it to once i've populated it. So in other words I don't use Word merge - I do it all myself with code. I keep the code to do this in blank template which is called form OI. It has an autoopen macro which then loads the document which the table is going to be merged into.

This method gives me complete control over the output.

If you want code examples let me know

Bob Watson

Bob,

Thanks for the clarification.

Your method seems perfect for columnar reports. I have form-based reports as well. I think Mail Merge works fine for the latter.

And I actually do use the table Add method, to expand tables for multivalued fields. I use Word tables for all multivalued fields (AMV or not). Initially the tables have just one row, where multivalued fields go in a delimited format. Then I run a post process where I split the content, use Add to expand the table with additional rows, and distribute the values across the rows. (Bob Carten supplied me with the original version of the expand-table function.)

Have you got multivalues in your columnar reports?

- Oystein -

Paul,

That might be it. I used a security setting of Medium.

- Oystein -

Friday afternoon ideas on Word / Excel Merging

Not all of them practical or simple, but

1. We have been testing the new ODBC driver as a data source to Word Merges. It works like a charm.

2 . For really cheap way to do word documents, try saving the document as html, then use OSREAD, swap, oswrite to a new name, utility( "RUNWIN" "newname.doc") With Office XP you can work with XML rather than HTML.

3. Don't feel like writing flat files out? Try having an inet procedure that will return the same html with the values swapped in. Have word or Excel open the URL, they will read the document right from your database.

4. Take a look at XREV.dll You might be able to write your word macro to fire up its own instance of Oengine, pull the data from OI, put it where you want.

5. Office XP supports something called 'smart tags' that lets you define keywords in an office document that will trigger a query or process. For instance, you can configure word so that if a user types customer_id then word can pop up a list of customers from your database and autofill the address. Those tags could be used to run OECGI queries.

… Hope I'm not muddying the waters here. This is a good thread, I just wanted to toss in a few ideas

Bob

Bob,

… Hope I'm not muddying the waters here. This is a good thread, I just wanted to toss in a few ideas Oi!! Don't tangle the thread!! We're competing for Thread of the Year here!!! 1. ODBC Sounds really neat compared to the solution with writing to a text file. But if we already have the latter working - is there any real advantage in changing to the ODBC method? I wouldn't assume it gives us any more control over our output. E.g, we (I) would still need to expand multivalues. Or? I might do it for personal satisfaction, though. 2. Swap values into HTML template This should appeal to those who'd like to do more stuff from Basic+, with less need for post-processing and less need for VBA knowledge. E.g, multivalues could be expanded "in-app" by some easy manipulation of html. But Word documents offer better formatting than HTML, so I believe some possibilities would be lost with this alternative. Btw - what kind of file is it you write after you've swapped in the values? Mustn't that file be html too, i.e, newname.htm? How can you run it as newname.doc? (No problem. I just ask in case there's something I didn't understand.) 3. Inet procedure Similar comments as 2. 4. XREV.dll functions from Word macro …opens up a lot of possibilities for sure! 5. Smart tags Ditto. Enough to give you agarophobia. Not exactly reporting as we know it, but… …uh …well - wow! - Oystein - </QUOTE> —- === At 01 NOV 2002 05:41PM Oystein Reigem wrote: === <QUOTE>Bob, … Hope I'm not muddying the waters here. This is a good thread, I just wanted to toss in a few ideas Oi!! Don't tangle the thread!! We're competing for Thread of the Year here!!! 1. ODBC Sounds really neat compared to the solution with writing to a text file. But if we already have the latter working - is there any real advantage in changing to the ODBC method? I wouldn't assume it gives us any more control over our output. E.g, we (I) still need to expand multivalues. Or? I might do it for personal satisfaction, though. 2. Swap values into HTML template This should appeal to those who'd like to do more stuff from Basic+, with less need for post-processing and less need for VBA knowledge. E.g, multivalues could be expanded "in-app" by some easy manipulation of html. But Word documents offer better formatting than HTML, so I believe some possibilities would be lost with this alternative. Btw - what kind of file is it you write after you've swapped in the values? Mustn't that file be html too, i.e, newname.htm? How can you run it as newname.doc? (No problem. I just ask in case there's something I didn't understand.) 3. Inet procedure Similar comments as 2. 4. XREV.dll functions from Word macro …opens up a lot of possibilities for sure! 5. Smart tags Ditto. Enough to give you agarophobia. Not exactly reporting as we know it, but… …uh …well - wow! - Oystein - </QUOTE> —- === At 01 NOV 2002 09:07PM Bob Watson wrote: === <QUOTE>Oystein ] Yes - I pass them out with linefeed delimeters. But if I was doing associated multivalues within a columnar report I would manipulate the word tables to put sub-rows within rows if you get my drift. Now to all that interesting stuff that Bob Carten suggested… Bob Watson </QUOTE> —- === At 01 NOV 2002 09:20PM Bob Watson wrote: === <QUOTE>Bob ] - I will be trying this method - we await the release of the ODBC driver. ] - I'm interested to see what the XML for a Word document looks like. ] At present I write a text file form Word that tells OI that we completed the report sucessfully. OI just waits around till it succeeds in reading the file. Nasty. This method (or the ODBC driver) is obviously one way to go. ] - now I like the sound of this! - how to impress the users. A good argument for going Office XP - so we can play with this. Bob Watson </QUOTE> View this thread on the forum...