locked
Looking for some guidance on wrapping text with XML tags; VBA in Excel & Word. RRS feed

  • General discussion



  • Greetings all

    The best way to begin is probably by explaining my workflow a bit. If you don't care, skip to the bottom part where I pose the actual question.

    I start by converting a CSV file to word documents with a VBA script; One .docx file per row. Multiple columns of data copy & pasted. The format of each document looks like this:

    <keyword 1>
       <keyword 1 body paragraphs>
    <keyword 2>
       <keyword 2 body paragraphs>

    After this process, I then:

    1. Open each file individually and edit for content/grammar.
    2. Run VBA script to remove consecutive carriage returns & wrap each paragraph with <p></p> HTML tags.
    3. Select a keyword and run VBA script that finds all instances of the keyword and changes text format to red color and bold.
    4. Repeat step 4 for the second keyword. 
    5. I then copy & paste each section (headline 1, body 1, headline 2, body 2) individually into our CMS

      What I'd like to do is automate step 5. The reason I copy & paste each section is to weave it into our XML structure. The structure is the same for each file, however so I'm thinking it can be easily automated. When I'm done, it would ultimately to look like this:
    <section_1_tag>
        <headline_tag><keyword 1></headline_tag>
        <paragraph_tag><keyword 1 body paragraphs></paragraph_tag>
    </section_1_tag>
    
    <section_2_tag>
        <headline_tag><keyword 2></headline_tag>
        <paragraph_tag><keyword 2 body paragraphs></paragraph_tag>
    </section_2_tag>

    SOO that was a long-winded way of saying I want to wrap sections of content with XML tags. I have a working knowledge of VBA but I want advice on the best way to tackle this. I'll post my ideas below, bud I'd love to get some feedback and/or pointers! Thanks!

    Could I wrap the sections when I copy from excel using VBA? How do I wrap each individual paragraph in <p></p> tags; How do I handle removing the extra carriage returns.

    I was also thinking of making "classes" for the text using Quick Styles in word and running a for loop for each paragraph in the document and wrap it in the tag based on the "class" of the text. But how do I implement section tags?

    Any thoughts are very much appreciated!

    Thursday, January 31, 2013 11:57 PM

All replies

  • Just thought I'd make it clear that I'm not looking for code; I'm just looking for some direction as this is a bit over my scope of abilities.
    Friday, February 1, 2013 9:46 PM