+ Post New Thread
Results 1 to 3 of 3
Office Software Thread, VBA in Word 2007 - to split a mail merge, just one line needed! in Technical; Basically I'd like to to pick up the nth to the (n+x)th characters in a document and have them replace ...
  1. #1
    CaterinaAnna's Avatar
    Join Date
    Jun 2008
    Location
    Coventry
    Posts
    57
    Thank Post
    21
    Thanked 2 Times in 2 Posts
    Rep Power
    13

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

    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:

    Code:
    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.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,447
    Thank Post
    1,438
    Thanked 1,168 Times in 797 Posts
    Rep Power
    707
    Quote Originally Posted by CaterinaAnna View Post
    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:

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

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

    Code:
    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!

  3. Thanks to LosOjos from:

    CaterinaAnna (9th July 2010)

  4. #3
    CaterinaAnna's Avatar
    Join Date
    Jun 2008
    Location
    Coventry
    Posts
    57
    Thank Post
    21
    Thanked 2 Times in 2 Posts
    Rep Power
    13
    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.

SHARE:
+ Post New Thread

Similar Threads

  1. Word 2007 line spacing: How do I deply via GPO?
    By Ben_Stanton in forum Office Software
    Replies: 10
    Last Post: 22nd April 2010, 12:24 PM
  2. [MS Office - 2007] Word 2007 help needed.
    By Simcfc73 in forum Office Software
    Replies: 9
    Last Post: 22nd October 2009, 12:43 PM
  3. Replies: 9
    Last Post: 9th April 2009, 07:33 AM
  4. SIms mail merge
    By KWestos in forum MIS Systems
    Replies: 1
    Last Post: 2nd October 2008, 07:31 PM
  5. Mail Merge
    By Teaboy in forum MIS Systems
    Replies: 4
    Last Post: 12th November 2007, 06:32 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •