VBA in Word 2007 - to split a mail merge, just one line needed!

[CaterinaAnna]

Basically I'd like to to pick up the nth to the (n+x)th characters in a document and have them replace LTrim$(Str$(Counter))in this line of a macro:

 

DocName = "Myletter" & LTrim$(Str$(Counter)) 

 

Problem is, as someone who has only used VBA in Excel before - & is something of a novice there - I don't know the syntax or how to find out the character number of a particular bit of text. (Well, I could probably have a go at guessing the latter, but is a return a single character? or none? or 80?) And Word's own VBA help files & articles - on & offline - are still above my head.

 

Why?

 

I've a mail merge to generate a set of reports that should be uploaded to our Parent Information System (we use MCAS). To do that I need each student's page to be saved as a separate document when I run the merge. I've found a bit of code that does that, & even got it to work (more or less), but it saves each document with a title in the form "My Letterx" where x is , as you can see from the code, a counter. Now that's a step along the way, but I want the admin number or something so that I don't have to go through and manually rename each one before I upload it to the Bromcom DMS. The number is, of course, in the data source (a separate spreadsheet) and I'm hoping that if I insert it into the document right at the start as a merge field a solution along the lines of what I've asked for above would do the trick. Or would it just pick up the field reference?? Or is there a way of picking it up directly from the data source?

 

I know that if we had all our assessment data in Bromcom or AM this could be done automagically, but ... *sigh* don't ask!

 

Thanks in advance for any light anyone can shed on this.

[LosOjos]

Basically I'd like to to pick up the nth to the (n+x)th characters in a document and have them replace LTrim$(Str$(Counter))in this line of a macro:

 

DocName = "Myletter" & LTrim$(Str$(Counter)) 

 

Problem is, as someone who has only used VBA in Excel before - & is something of a novice there - I don't know the syntax or how to find out the character number of a particular bit of text. (Well, I could probably have a go at guessing the latter, but is a return a single character? or none? or 80?) And Word's own VBA help files & articles - on & offline - are still above my head.

 

Why?

 

I've a mail merge to generate a set of reports that should be uploaded to our Parent Information System (we use MCAS). To do that I need each student's page to be saved as a separate document when I run the merge. I've found a bit of code that does that, & even got it to work (more or less), but it saves each document with a title in the form "My Letterx" where x is , as you can see from the code, a counter. Now that's a step along the way, but I want the admin number or something so that I don't have to go through and manually rename each one before I upload it to the Bromcom DMS. The number is, of course, in the data source (a separate spreadsheet) and I'm hoping that if I insert it into the document right at the start as a merge field a solution along the lines of what I've asked for above would do the trick. Or would it just pick up the field reference?? Or is there a way of picking it up directly from the data source?

 

I know that if we had all our assessment data in Bromcom or AM this could be done automagically, but ... *sigh* don't ask!

 

Thanks in advance for any light anyone can shed on this.

 

OK, I'm not entirely sure I'm on the right lines here, but the quickest way I can think to have a piece of data ready to read from mail merge, is to create a single row, single column table at the top of the document and put the field in there, you can then access it with something like:

 

ActiveDocument.Tables(1).Columns(1).Cells(1).Range.Text

 

That way, you just treat the above as you would a cell in Excel. There might be an easier way, but as I also come form an Excel VBA background, that was the most logical way my mind could come up with!

 

If you don't want the table to show on the final document, either colour the text and borders white, or just before the document is saved, use this to delete it:

 

ActiveDocument.Tables(1).Delete

 

As a final note, the table number depends on where in the document the table is if there are other tables in the document, if you have more than one, write a macro to cycle through all the tables and number them first to help you identify the one you need!

[CaterinaAnna]

Thanks LO, I'm about to run off for the weekend, but I'll give it a try nextweek and let you know how I get on.