none
macro to copy styled text from word to excel RRS feed

  • Question

  • Hi, i need to solve the following problem:

    - i have a text document that is list of requirements structured in a word heading (1,2,3, etc..).

    i want to copy the text to the excel table in a following way:

    - requirement number (which is a numbering, not text) will appear in one column

    - requirement text will appear in a second column respectively

    So, the question if it could be done with macro?

    Thanks!

    Monday, November 16, 2015 2:04 PM

Answers

  • Based on the information you give us, the answer to your question is: "Yes"

    Although you should not use Copy/Paste.

    If you want more detailed help with this you need to provide information about how the document and worksheet are structured. The code has to be able to find/recognize the requirements (vs. other things) in the document.


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, November 16, 2015 5:00 PM
    Moderator
  • Hi,

    Assume that you want to copy the heading text in Word document into spreadsheets and the headings are formatted

    with numbering style or with a prefix of numbers.

    We can get the headings in the document via code below:

    ActiveDocument.GetCrossReferenceItems(wdRefTypeHeading)
    

    And to separate the string as we wanted, we can use regular expression. Here is a demo code that separate the numbers and text in the headings of word and copy them to the worksheet for your reference:

     Dim regEx As New RegExp
     Dim strPattern As String
    strPattern = "(\d+\.)(\D+)"
    
    
      With regEx
                    .Global = True
                    .MultiLine = True
                    .IgnoreCase = False
                    .Pattern = strPattern
                End With
    
    Set excelApp = CreateObject("Excel.application")
    Set aWorkbook = excelApp.workbooks.Add
    
    Dim rowIndex As Integer
    rowIndex = 1
    For Each aHeader In ActiveDocument.GetCrossReferenceItems(wdRefTypeHeading)
    
    aWorkbook.Worksheets(1).Range("A" & rowIndex).Value = regEx.Replace(aHeader, "$1")
    aWorkbook.Worksheets(1).Range("B" & rowIndex).Value = regEx.Replace(aHeader, "$2")
    
    rowIndex = rowIndex + 1
    Next
    
    excelApp.Visible = True

    To run the code above, we need to add VBA reference to "Microsoft VBScript Regular Expressions 5.5".

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 19, 2015 3:22 AM
    Moderator

All replies

  • Hi, i need to solve the following problem:

    - i have a text document that is list of requirements structured in a word heading (1,2,3, etc..).

    i want to copy the text to the excel table in a following way:

    - requirement number (which is a numbering, not text) will appear in one column

    - requirement text will appear in a second column respectively

    So, the question if it could be done with macro?

    Thanks!

    Sunday, November 15, 2015 2:06 PM
  • Hi Evgeni,

    This is the forum to discuss questions and feedback for Microsoft Word, if you want to get your result by using macro I'll move your question to the MSDN forum for General Office Development:
    https://social.msdn.microsoft.com/Forums/en-US/home?forum=officegeneral

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Monday, November 16, 2015 7:28 AM
  • Based on the information you give us, the answer to your question is: "Yes"

    Although you should not use Copy/Paste.

    If you want more detailed help with this you need to provide information about how the document and worksheet are structured. The code has to be able to find/recognize the requirements (vs. other things) in the document.


    Cindy Meister, Office Developer/Word MVP, <a href="http://blogs.msmvps.com/wordmeister"> my blog</a>

    Monday, November 16, 2015 5:00 PM
    Moderator
  • Hi,

    Assume that you want to copy the heading text in Word document into spreadsheets and the headings are formatted

    with numbering style or with a prefix of numbers.

    We can get the headings in the document via code below:

    ActiveDocument.GetCrossReferenceItems(wdRefTypeHeading)
    

    And to separate the string as we wanted, we can use regular expression. Here is a demo code that separate the numbers and text in the headings of word and copy them to the worksheet for your reference:

     Dim regEx As New RegExp
     Dim strPattern As String
    strPattern = "(\d+\.)(\D+)"
    
    
      With regEx
                    .Global = True
                    .MultiLine = True
                    .IgnoreCase = False
                    .Pattern = strPattern
                End With
    
    Set excelApp = CreateObject("Excel.application")
    Set aWorkbook = excelApp.workbooks.Add
    
    Dim rowIndex As Integer
    rowIndex = 1
    For Each aHeader In ActiveDocument.GetCrossReferenceItems(wdRefTypeHeading)
    
    aWorkbook.Worksheets(1).Range("A" & rowIndex).Value = regEx.Replace(aHeader, "$1")
    aWorkbook.Worksheets(1).Range("B" & rowIndex).Value = regEx.Replace(aHeader, "$2")
    
    rowIndex = rowIndex + 1
    Next
    
    excelApp.Visible = True

    To run the code above, we need to add VBA reference to "Microsoft VBScript Regular Expressions 5.5".

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 19, 2015 3:22 AM
    Moderator