CODEWITHSUNDEEP
vbaexcel
chefman179
chefman179

Reputation: 17

Copy Word table to Excel without splitting cells

I'm struggling to paste a word table into excel without the deliminator splitting out by paragraph, as I want to grabs those paragraphs and paste into excel later.

Does anyone know how to prevent excel from splitting paragraphed text when it is pasted into excel from a word table?

Sub InputDoc()
  Dim Oo As OLEObject
  Dim wDoc As Object 'Word.Document

  'Search for the embedded Word document
  For Each Oo In Worksheets("Input").OLEObjects
      If InStr(1, Oo.progID, "Word.Document", vbTextCompare) > 0 Then
          'Open the embedded document
          Oo.Verb xlVerbPrimary
          'Get the document inside
          Set wDoc = Oo.Object

          'Copy the contents to cell A1
          wDoc.Content.Copy
          Worksheets("Paste").Range("A1").PasteSpecial Paste:=xlPasteFormats

Upvotes: 1

Views: 1590

Answers (1)

D_Bester
D_Bester

Reputation: 5911

The text you're copying has CRLF in the string and you want to avoid Excel interpreting that as multiple cells.

Just avoid using Paste and use Range.Value instead. Problem solved. 

'Search for the embedded Word document
For Each Oo In Worksheets("Input").OLEObjects
If InStr(1, Oo.progID, "Word.Document", vbTextCompare) > 0 Then
    'Open the embedded document
    Oo.Verb xlVerbPrimary
    'Get the document inside
    Set wDoc = Oo.Object

    'Copy the contents to cell A1
    Worksheets("Paste").Range("A1").value = wDoc.Content

This will put the entire wDoc.content into one cell. That's not what you really want though. You really need to loop through the table in Word and use .Value to insert the data into Excel.

Upvotes: 1

Related Questions