copy from XL2007, paste into Word 2007 table

[stuck]

In XL 2007 select two or more cells in a column, right click and choose copy.
Switch to Word 2007 and select equiv. number of empty table cells, right click and choose paste.
Painless!

But do the same thing again having only copied a single XL cell and clicked into a single Word table cell and the right click has no paste option.
Irritation!

Paste as nested table is there but using that adds a paragraph mark.
Extra irritation!!

Ctrl V is no use either, the unasked for paragraph mark materialises once again.

Err why?

Ken

[HansV]

A rather stupid bug!

As a workaround, activate the home tab of the ribbon and click the lower half of the Paste button.
Select Paste Special...
Select Formatted Text (or Unformatted Text if you're not interested in the Excel formatting), then click OK.

(I have a button for Paste Unformatted Text on my Quick Access Toolbar so it's only a single click)

[StuartR]

...(I have a button for Paste Unformatted Text on my Quick Access Toolbar so it's only a single click)

Word 2007 and 2010 provide right click access to Paste plain text, which is even easier still (assuming you have used the mouse to position the insertion point).

[HansV]

x549.png

Er, my Word 2007 does not have Paste Plain Text in its context menu...

x549.png

[StuartR]

Bother, you are right again.

My Word 2007 has Paste Plain Text on the right click menu because it was an upgrade from Word 2003 and I had added it to the right click menu myself!

PastePlainText.png

Word 2010 does have a wide range of Paste Special options on the right click menu.

[stuck]

A rather stupid bug!

Ah! I wondered if if it was just me missing the obvious - again

A Paste Unformatted Text button on the QAT sounds like the simplest workaround

Ken
edited to add:
PS I can't find Paste Unformatted Text as an option to add to the QAT, even if I look through the list of 'All commands' so how did you achieve it on your PC?

[DaveA]

Just add "Paste Spacial" and when you do a "Paste" then the selection pane will appear.

[stuck]

True, perhaps I'm reading too much into Hans' comment about him having a Paste Unformatted button on his QAT.

Ken

[HansV]

how did you achieve it on your PC?

I created a macro

Code: Select all

Sub PasteUnformatted()
  Selection.PasteSpecial DataType:=wdPasteText
End Sub

in my Normal.dotm default template, and assigned this to a QAT button.

[stuck]

I created a macro

Thought so!

Ken

[PJ_in_FL]

I created a macro

Code: Select all

Sub PasteUnformatted()
  Selection.PasteSpecial DataType:=wdPasteText
End Sub

Great idea (i.e. immediately plagiarized by myself), but the "Selection." part puzzled me. Being a very neophyte to OOP, I try to get my head around the objects, methods, properties (but please don't ask me to understand classes, ARRRGGGGHHHH - please give me computed GOTO's again!!!), but the idea that the Selection thing exists in Word when what I've selected is in Excel bothers me on a very low level.

I try to understand what I see in the Watch window in the VBA IDE, when I set a watch on Selection, I just don't understand how Selection isn't "Null" or "Nothing" when I haven't selected anything in Word. A reference (to something other than a psychology text) may be helpful.

[HansV]

The macro will paste whatever is on the clipboard to the current selection in Word. This selection can be some text, or it can be the insertion point. If it is the insertion point, it is a range of length 0, but it is not Null or Nothing - even a range of length 0 has many properties: its location within the document, the font settings etc.

[StuartR]

My version of that Macro is a bit longer, to prevent an error message being displayed if the clipboard cannot be pasted as plain text (for example if you have copied a picture).

Code: Select all

Sub PastePlainText
    On Error GoTo Done
    Selection.PasteSpecial DataType:=wdPasteText
Done:
    Exit Sub
End Sub

[HansV]

You could shorten that to

Code: Select all

Sub PastePlainText
    On Error Resume Next
    Selection.PasteSpecial DataType:=wdPasteText
End Sub

[PJ_in_FL]

Hans,

Thank you for taking the time to help explain to someone who is OOP-challenged!

PJ