none
Extract only Sentences to Excel RRS feed

  • Question

  • Hi,

    Is there any reference macro which can extract only sentences from the word document into excel?

    Thanks & Regards,

    ShailShin

    Tuesday, July 8, 2014 12:03 PM

Answers

  • Hi,

    I'm not sure how you want to extract sentences from a Word document into an Excel workbook. There is no method to extract content from Word to Excel or import data from Word in Excel directly.

    We could create a new blank workbook or open an existing workbook in Word VBA macro and copy the sentences into the workbook.

    In Word VBA, we could add a reference of Microsoft Excel xx.x Object Library to manipulate Excel Object Model. Workbooks.Open Method could be used to open an existing workbook. Workbooks.Add method could be used to generate a new workbook. Then you could access to the Sentences Object of Word document and then extract them into specified cells of the workbook. Here is a sample to create a new Excel workbook.

    Sub CopySentenceToExcel()
    Dim app As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set app = CreateObject("Excel.application")
    app.Visible = True
    Set wb = app.Workbooks.Add
    Set ws = wb.Worksheets("Sheet1")
    End Sub

    If you want to set the value of a range with the sentence, you could refer to the code below.

    ws.Range("A1").Value = ActiveDocument.Sentences(1)


    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.

    Wednesday, July 9, 2014 3:25 AM
    Moderator
  • Although Word's VBA Object Model has a Sentences collection, that has nothing to do with grammatical sentences. For example, consider the following:

    Mr. Smith went to Dr. John's Grocery Store, to buy 1kg of Mrs. Green's Mt. Pleasant macadamia nuts.

    For you and me, that would probably count as one sentence; for VBA it counts as 5...


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, July 9, 2014 4:24 AM
  • Unless you create a lookup array or something such with every possible abbreviation, there is no way for VBA to recognise one. And, even if you do, a given abbreviation may or may not terminate a sentence.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, July 9, 2014 5:15 AM

All replies

  • Hi,

    I'm not sure how you want to extract sentences from a Word document into an Excel workbook. There is no method to extract content from Word to Excel or import data from Word in Excel directly.

    We could create a new blank workbook or open an existing workbook in Word VBA macro and copy the sentences into the workbook.

    In Word VBA, we could add a reference of Microsoft Excel xx.x Object Library to manipulate Excel Object Model. Workbooks.Open Method could be used to open an existing workbook. Workbooks.Add method could be used to generate a new workbook. Then you could access to the Sentences Object of Word document and then extract them into specified cells of the workbook. Here is a sample to create a new Excel workbook.

    Sub CopySentenceToExcel()
    Dim app As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set app = CreateObject("Excel.application")
    app.Visible = True
    Set wb = app.Workbooks.Add
    Set ws = wb.Worksheets("Sheet1")
    End Sub

    If you want to set the value of a range with the sentence, you could refer to the code below.

    ws.Range("A1").Value = ActiveDocument.Sentences(1)


    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.

    Wednesday, July 9, 2014 3:25 AM
    Moderator
  • Although Word's VBA Object Model has a Sentences collection, that has nothing to do with grammatical sentences. For example, consider the following:

    Mr. Smith went to Dr. John's Grocery Store, to buy 1kg of Mrs. Green's Mt. Pleasant macadamia nuts.

    For you and me, that would probably count as one sentence; for VBA it counts as 5...


    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, July 9, 2014 4:24 AM
  • Hi Paul,

    To avoid the VBA count for sentence can it be possible to extract sentences with the delimiters [.,?,!] and for the case of abbreviation which comes in between the sentences can this look into the exclude arrays containing abbreviations.

    Thanks & Regards,

    ShailShin

    Wednesday, July 9, 2014 4:59 AM
  • Unless you create a lookup array or something such with every possible abbreviation, there is no way for VBA to recognise one. And, even if you do, a given abbreviation may or may not terminate a sentence.

    Cheers
    Paul Edstein
    [MS MVP - Word]

    Wednesday, July 9, 2014 5:15 AM