0

I am working on copy and past the word tables to excel. but there are a lot of 'enter' key in word tables. could I know how to replace the the enter key in whole word tables. I am encountering issue" wrong number of argument or invalid property assignment"

Zhao Yang
  • 3
  • 3
  • Try replace `With Selection.Find` with `ActiveDocument.Tables(1).Range.Find`, there's no need to select range 99% of the time (so you can probably delete `ActiveDocument.Tables(1).Range.Select` too). – Raymond Wu Sep 23 '21 at 09:05
  • What are you trying to replace carriage return ("enter" key) with? `.Text = ">P"` should be `.Text = "^p"` and `^p` in `.Replacement.Text = "^p"` should be replaced with your replacement character. – Raymond Wu Sep 23 '21 at 09:10
  • Please also read on [how to avoid using Select/Activate](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1), you do not need to activate/select your worksheet/cell 99% of the time (In fact, it's bad practice) – Raymond Wu Sep 23 '21 at 09:12
  • See, for example: https://www.excelguru.ca/forums/showthread.php?8900-Help-with-VBA-to-extract-data-from-Word-to-Excel&p=36586&viewfull=1#post36586 – macropod Sep 23 '21 at 23:24
  • You likely also need to eliminate the end-of-cell marks, not just paragraph marks. – Charles Kenyon Sep 24 '21 at 02:09
  • @CharlesKenyon Bro, how to do it? – Zhao Yang Sep 26 '21 at 14:29
  • Sorry, I do not remember. But I do know it is a separate character. You should be able to find it with an Internet search. – Charles Kenyon Sep 26 '21 at 17:03
  • You really should read code in the link I posted... – macropod Sep 27 '21 at 00:16
  • @macropod bro, I read it, but the Execute Replace:=wdReplaceAll is not working – Zhao Yang Sep 28 '21 at 06:38
  • The code *as posted* in the link works just fine... – macropod Sep 28 '21 at 22:53

2 Answers2

0

With Selection.find references the Excel selection object. But you want to work with the Word selection object.

Do you have a variable for the word application, e.g. appWord? Use this: With appWord.Selection.find

If not With oLookWordDoc.parent.selection.find should work

Ike
  • 9,580
  • 4
  • 13
  • 29
0

You have more than one problem with this code.

The first is that you are not setting oLookWordDoc to point to a document, so none of the Word code will work.

Second, you have two variables pointing to the same table, oLookwordTbl and r. You only need one of these.

Third, you are selecting the table to run Find instead of simply using the Find method of Table.Range.

Fourth, your find and replacement texts are incorrect.

The tidied code below will replace the paragraph marks in the table with a space.

Dim oLookWordDoc As Word.document
Dim oLookwordTbl As Word.Table
Dim iRow As Long 'row index

'you need to set oLookWordDoc to point to a document here

'Grab the word table
Set oLookwordTbl = oLookWordDoc.Tables(1)
With oLookwordTbl.Range.Find
    .Text = "^p"
    .Replacement.Text = " "
    .Forward = True
    .Wrap = wdFindStop
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
End With
'rows 2 - end
For iRow = 2 To oLookwordTbl.Rows.Count
    oLookwordTbl.Rows(iRow).Range.Copy
    'Paste
    xWs.Paste
    xWs.Cells(xWs.Rows.Count, 1).End(3).Offset(1).Select
Next
Timothy Rylatt
  • 7,221
  • 2
  • 10
  • 14