Excel VBA: Copy selected text from word, paste in excel at selected cell, 1 line per row 1

[renigar]

I do OK in Excel VBA but Word VBA seems completely foreign. As the title says I want to select text in an open Word doc then with an Excel macro have it pasted into Excel starting at the selected cell. I want each line of Word text to be on a separate row in Excel. There is nothing fancy about the Word text. It's just straight text (although there could be sentences that wrap to another line), not more than a short paragraph. I have no code to start with as I don't know much about Word. I've looked through the forum and seen a lot of Word to Excel topics, but nothing that quite does this. Any help or suggestions would be appreciated.

 

[Andrzejek]

I want each line of Word text to be on a separate row in Excel" - that all depends on what you mean by "each line of Word text". Your entire post would be a one line of text. So your post would be on one row (cell?) in Excel?

And if you would select my reply in Word, my text will be in 2 cells in Excel, right?


---- Andy

There is a great need for a sarcasm font.

 

[renigar]

You are correct. Here is some example text. It would be one paragraph in Word. It takes twelve lines on the page. I want it to take up twelve rows in Excel.

Little excuse is needed, perhaps, for any fresh selection from the
famous "Tales of a Thousand and One Nights," provided it be
representative enough, and worthy enough, to enlist a new army of
youthful readers. Of the two hundred and sixty-four bewildering,
unparalleled stories, the true lover can hardly spare one, yet there
must always be favorites, even among these. We have chosen some of the
most delightful, in our opinion; some, too, that chanced to appeal
particularly to the genius of the artist. If, enticed by our choice and
the beauty of the pictures, we manage to attract a few thousand more
true lovers to the fountain-book, we shall have served our humble turn.
The only real danger lies in neglecting it, in rearing a child who does
not know it and has never fallen under its spell.

 

[Andrzejek]

This is a very lazy approach, but ...

I just copied your sample text (highlighted it, Ctrl-C, I can do it from here in the web browser or in Word, does not matter). I went to Excel, clicked on cell G8 (or whatever) and did Ctrl-V (for Paste).

I think the output is what you want - no VBA

---- Andy

There is a great need for a sarcasm font.

 

[renigar]

The example text is for illustrative purposes only. If the text was originally written in Word it would put the whole paragraph in the selected cell as you previously stated. So your lazy approach doesn't help my situation. Although I do appreciate your efforts.

 

[Andrzejek]

Could you post the text that you would have in Word that does illustrate your situation? And how that text would be presented in Excel.

That obviously will be different that what is shown above.


---- Andy

There is a great need for a sarcasm font.

 

[renigar]

Text as written in Word

Result when pasted in Excel

Desired result in Excel

 

[macropod]

To extract Word document content on a line-by-line basis, you'll need Word VBA code like:

Sub Demo()
Dim Ln As Line
For Each Ln In ActiveDocument.ActiveWindow.Panes(1).Pages(1).Rectangles(1).Lines
  MsgBox Ln.Range.Text
Next
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[renigar]

macropod,

That does what I want from the Word side, except I would like it to only operate on the selected text. I don't know how to implement it from the Excel side. Could you provide any insight?

Thanks, renigar

 

[macropod]

To limit the output to a selection, you might use code like:

Sub Demo()
Dim Ln As Line
For Each Ln In ActiveDocument.ActiveWindow.Panes(1).Pages(1).Rectangles(1).Lines
  If Ln.Range.InRange(Selection.Range) Then MsgBox Ln.Range.Text
Next
End Sub

As for the Excel side of things, you haven't told us enough. For example, is the output to go into a new workbook, an existing open workbook, or an exiting closed workbook? Might someone else have it open? And on what worksheet and in what range in that whould the output go? You should also check out some of the threads on automating Excel from Word. See, for example:

https://www.tek-tips.com/viewthread.cfm?qid=1677919

Cheers
Paul Edstein
[MS MVP - Word]

 

[renigar]

Thanks macropod,

As to the missing information on the Excel side of things:
I want the macro to paste the selected text in an existing open workbook, on the active worksheet, starting at the active cell paste the first line of selected Word text, drop down one row, same column, paste next line of selected Word text and repeat until all selected text is pasted.

I have resisted learning more about Word because I rarely use it. I use Excel everyday at work so what I learn sticks. I got asked to try to make this work by my boss so I'm diving in. The Word side of VBA seems quite a bit different (unfamiliar). Anyway I will check out the thread you suggested.

Thanks again for your help,
renigar

 

[macropod]

To copy the line-by-line content to the clipboard, so you can manually paste it directly into Excel, you could use:

Sub Demo()
Application.ScreenUpdating = False
Dim Ln As Line, objUndo As UndoRecord
Set objUndo = Application.UndoRecord
With objUndo
  If .IsRecordingCustomRecord = False Then
  .StartCustomRecord ("XXX")
  For Each Ln In ActiveDocument.ActiveWindow.Panes(1).Pages(1).Rectangles(1).Lines
    With Ln.Range
      If .InRange(Selection.Range) Then
        If .Characters.Last <> vbCr Then .InsertAfter Chr(11)
      End If
    End With
    Selection.Copy
  Next
  .EndCustomRecord
  ActiveDocument.Undo
  End If
End With
Application.ScreenUpdating = True
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[renigar]

Macropod,

I'll see what I can do with this and let you know next week when I get back to work.

Thanks,
renigar

 

[renigar]

macropod,

Here is what I came up with for Excel. It works but there is an inconsistency. One section of selected text will paste as desired one page line per row (cell) another section of selected text will paste one sentence (paragraph) per row (cell). You'll notice I renamed your code for my purposes. Here is a link to my Word file:
Operations Notes.docm
See example images below code, Monday and Tuesday notes worked correctly, Wednesday did not.

Sub runwordmacro()

' Connect using Early Binding.
' Remember to set the reference to the Word Object Library
' In VBE Editor Tools -> References -> Microsoft Word x.xx Object Library

Dim Wd As Word.Application

Set Wd = GetObject(, "Word.Application")

' Run word macro to put selected text into clipboard
Wd.Application.Visible = True
Wd.Application.Run "WordToClipboard"

' Paste info from clipboard starting at active cell
    ActiveCell.Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:= _
        False, NoHTMLFormatting:=True

' Release the memory
Set Wd = Nothing

Application.CutCopyMode = False

End Sub

I would really appreciate it if you had time to look at the file and let me know what I'm doing wrong.
Thanks,
renigar

 

[macropod]

You second example looks like you did a copy/paste without running the macro.

Cheers
Paul Edstein
[MS MVP - Word]

 

[renigar]

I agree with you but both images are the result of running the macro. I think your macro does a great job. I'm not the author of the Word file. I thought maybe there was something about the Word file that was throwing the macro off, but I can't see any difference from one section of text to the next.

renigar

 

[macropod]

Have you tried running the macro directly on the second area in the Word file, then pasting from the clipboard into Excel?

Cheers
Paul Edstein
[MS MVP - Word]

 

[renigar]

I've tried the macro both ways. Running from word and pasting into excel. Running from excel. The results are the same either way. The Word file is four pages of notes that cover seven days from Monday to Sunday. Each day has a header. I select only the notes for a particular day not including the header and run the macro (either way). The results are always the same. Monday and Tuesday notes copy and paste as intended splitting the paragraph into lines. The rest of the days go across like a simple copy and paste. I still don't see an explanation for it.

 

[combo]

Code directly in excel without clipboard, without rectangle type testing:

Sub ParseWordSelection()

' Connect using Early Binding.
' Remember to set the reference to the Word Object Library
' In VBE Editor Tools -> References -> Microsoft Word x.xx Object Library

Dim wdApp As Word.Application, wdDoc As Word.Document
Dim wdPage As Word.Page, wdRectangle As Word.Rectangle
Dim wdLine As Word.Line
Dim LineCounter As Long, TextOfLine As String

Set wdApp = GetObject(, "Word.Application")
Set wdDoc = wdApp.ActiveDocument
LineCounter = 0: TextOfLine = ""
For Each wdPage In wdDoc.ActiveWindow.Panes(1).Pages
    For Each wdRectangle In wdPage.Rectangles
        For Each wdLine In wdRectangle.Lines
            With wdLine.Range
                If .InRange(wdApp.Selection.Range) Then
                    TextOfLine = wdLine.Range.Text
                    ActiveCell.Offset(LineCounter, 0) = TextOfLine
                    LineCounter = LineCounter + 1
                End If
            End With
        Next wdLine
    Next wdRectangle
Next wdPage
End Sub

combo

 

[renigar]

Thanks Combo, it works perfectly!