Copy from Word To Excel

[kerenlu]

I succeeded to copy some paragraphs which was marked with bookmarks in the word to excel to a specific cells according to the bookmarks.
but the paragraphs display in the excel with spaces and carriage return. I tried to use send-keys but it causes too many problems.
Do you have any idea?

[Teeroy]

I'm not too familiar with Word specific VBA but the following takes clipboard data and cleans it from inside Excel. You might get something you can use from it.


Sub PasteWithoutSpecialCharacters()
'need reference to Microsoft Forms 2.0 Object Library

Dim DataObj As DataObject
Dim temp As String

Set oData = New DataObject

oData.GetFromClipboard
temp = oData.GetText
'remove special characters
temp = CleanString(temp)


End Sub


Function CleanString(StrIn As String) As String
' "Cleans" a string by removing embedded control (non-printable)
' characters, including carriage returns and linefeeds.
' Does not remove special characters like symbols, international
' characters, etc. This function runs recursively, each call
' removing one embedded character
Dim iCh As Integer
CleanString = StrIn
For iCh = 1 To Len(StrIn)
If Asc(Mid(StrIn, iCh, 1)) < 32 Then
'remove special character
CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
Exit Function
End If
Next iCh

End Function

[Frosty]

As an alternative... you can look into using Replace... something like

sMyStringVariable = Replace(sMyStringVariable, vbcr, "")

And that will remove all paragraph marks from the string. Since you probably don't want any paragraph marks in the excel cell, that might be enough to do "clean up"... but otherwise, from your description of seeing "spaces", it almost sounds like you've pasted in an image, which of course there wouldn't be anything you could do with, textually.

[kerenlu]

Thank you both for your replays.
I used the last post and It simply solved my problem