none
Create a word document from Excel data RRS feed

  • Question

  • Environment: Word and Excel 2013

    Windows 8.1

    I have reviewed the suggested answers and feel that they are just not hitting the nub of my issue and/or going a bit too far beyond my comfort zone.  I am willing to be stretched a bit so to say but am looking for some 'simple' code examples, if available and appropriate, please.

    I have written quite a lot of VBA for Excel but this would be my first foray into its use in Word.

    Basically I have  a quite large Excel spreadsheet about my ancestry.  Each line contains various basic data about one of my ancestors, e.g. First Name, Surname, Birth Date, Marriage Date, Death Date etc.

    I am trying to create a printed work folder from the basic data that I have already.

    I know about mailing labels merging, but this is way beyond that, though in essence the required bottom line result is very similar.  I have seen references to XML in another answer but that is, as I have hinted, maybe pushing my comfort zone a bit far, and I would like to avoid that if I can.

    In essence I would like to be able to do the following, in pseudo code -

    Have a Word Document that has this line at the beginning

    {Build_Family_History_Workbook\First_Ancestor\Last_Ancestor}

    and then have a VBA module that includes the following function

    Function Build_Family_History_Workbook (First_Ancestor, Last_Ancestor)

    DO I = First_Ancestor to Last_Ancestor

    Access record I from Ancestors excel workbook

    IF I is even Write a mid page straight line

    Write Name: <First_Name> <Surname>

    Write Birth:  <Date of Birth>       [tab]   Place of Birth: <Place of Birth>

    Write Marriage:    <Date of Marriage> [tab]  Place of Marriage: <Place of Marriage>

    Write Death: <Date of Death>    [tab]  Place of Birth: <Place of Death>

    Write Occupation:                                       

    Write Ancestor Number: <Ancestor_Number>                         

    Write Relationship to D, A, S, & P: <Relationship> - e.g. Great-Great Grand Father                                                

    Write Relationship to and Name of Immediate Descendant in Tree: <Name and Relationship of Immediate Descendant>  

    Write Documents Bundle Number: __________________     

    If I is even then Write a New Page Break

    Next I

    Along the way I will probably need to be setting values like Before and/or After points, line spacing etc. so, if the actual code for my pseudo-coded Write has such parameters then, I would welcome some guidance and/or examples regarding same please.

    In the above pseudo code the italicised items are 'VBA code', elements outside <> brackets are literals, while those in <> are record data drawn from the appropriate row on the Excel spreadsheet.

    I am not concerned over how long these procedures will take to run as they will only be run a few times, the documents then printed, and I move on with my project.  The reason I want code is that I am talking about the creation of documents that are hundreds, and sometimes thousands, of pages long.

    In conclusion I would ask, in the light of my last sentence, does anyone know what the maximum number of pages and/or maximum number of characters in a Word 2013 file is?

    With many thanks in anticipation.

    Philip

    Bendigo, Victoria

    Australia




    Friday, August 29, 2014 1:01 PM

Answers

  • You can either use a Letter merge, which will start a new page each time the record changes, or you can create a Directory/Catalog merge which will simply repeat the document for the next record immediately after the preceding one, thus you can get two records (or more) on a page depending on how long the text including fields is. See

    Mailmerge with Word 2007/2010

    If you want to list values from several records in a common 'letter', then  Word merge alone cannot readily do that, so you could use instead http://www.gmayor.com/ManyToOne.htm   



    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, September 1, 2014 6:54 AM

All replies

  • I have read the requirements a couple of times and it looks like a letter mail merge to me? What am I missing that makes this require a macro process?


    Graham Mayor - Word MVP
    www.gmayor.com

    Saturday, August 30, 2014 10:01 AM
  • As you describe it I think Graham is right.  I always end up needing to do some special processing of the data so I use VBA and bookmarks to fill in a Word template.  Look at:

    http://www.wiseowl.co.uk/blog/s199/word-bookmarks.htm

    Saturday, August 30, 2014 12:59 PM
  • Thank you for that input, Graham. 

    I just had one question, please.  How do I go on to the next line / 'record' half way through the page / 'letter'?

    I also assume that the system will just automatically go on to the next record, i.e. the third, fifth etc. when it creates the second, third etc. page, after having already gone on to the second, fourth etc. through whatever code I insert by doing whatever you tell me as an answer to my question, above.

    I look forward to your reply and express many thanks already for drawing my attention to what is basically so simple a solution.

    Best regards

    Philip

     


    Philip Hunt Flora Hill, Bendigo, Victoria Webmaster www.howandwhywonderbooks.info (Phase 1 operative)

    Sunday, August 31, 2014 10:26 PM
  • You can either use a Letter merge, which will start a new page each time the record changes, or you can create a Directory/Catalog merge which will simply repeat the document for the next record immediately after the preceding one, thus you can get two records (or more) on a page depending on how long the text including fields is. See

    Mailmerge with Word 2007/2010

    If you want to list values from several records in a common 'letter', then  Word merge alone cannot readily do that, so you could use instead http://www.gmayor.com/ManyToOne.htm   



    Graham Mayor - Word MVP
    www.gmayor.com

    Monday, September 1, 2014 6:54 AM