CODEWITHSUNDEEP
excelvbams-word
user1437779
user1437779

Reputation: 7

Move data from MS Word to Excel

I have a Microsoft Word document with 400 multiple choice test questions. I need to put all this text into a Microsoft Excel chart and I thought it would be alot easier if I was able to have a macro that allowed me to select all text that began with a. and ends the section at the first paragraph object after a..

I tried getting help and was told to use the below macro but the macro does not do anything. I just want the macro to select all the text only. If I were to do this in Microsoft Word manually, I would hold down ctrl and highlight all the text that begins with a. and ends at the first paragraph object. 

 Sub Aselection()
'
' Aselection Macro
'

Dim pgh As Paragraph
For Each pgh In ThisDocument.Paragraphs
With pgh
If Left(.Range.Text, 2) = "a." And Left(Right(.Range.Text, 3), 2) = "a." Then
Debug.Print .Range.Text
End If
End With
Next

End Sub

Upvotes: 1

Views: 8456

Answers (1)

147
147

Reputation: 592

ThisDocument typically refers to the template document containing the executing code. Use ActiveDocument instead.

Also as @assylias said in his comment, Debug.Print is only for code debugging purposes. Replace that entire line with .Range.Select.

This should work:

Sub Aselection()

    Dim o As Object
    Dim pgh As Paragraph

    Set o = CreateObject("excel.application")
    o.workbooks.Open ("E:\Aashay Data\Projects\Excel\Carton\Screen Printing.xlsx")
    o.ActiveWorkbook.worksheets.Add.Name = "x"
    o.ActiveWorkbook.worksheets("x").Activate


    For Each pgh In ActiveDocument.Paragraphs
        With o.ActiveWorkbook.worksheets("x")
            Debug.Print pgh.Range.Text
            If Left(pgh.Range.Text, 2) = "a." And Left(Right(pgh.Range.Text, 3), 2) = "a." Then
                .Cells(i, 2).Value = pgh.Range.Text
                i = i + 1
            End If
        End With
    Next
    o.Quit

End Sub


EDIT: After reviewing this and testing a lorem ipsum text, I realised that Word VBA does not allow you to select multiple discontinuous segments (See MS Support article KB288424 for more). I suppose the easiest way then is to simply export to excel where the debug.print is and I have edited my code accordingly.

Upvotes: 1

Related Questions