PDA

View Full Version : [SOLVED:] Paste and keep source formatting



HTSCF Fareha
12-11-2020, 07:51 AM
Trying to fathom out how to paste from clipboard (contains Excel data) into a textbox in word using VBA, whilst using the word option of "keeping the source formatting".


Dim Clipboard As MSForms.DataObject
Set Clipboard = New MSForms.DataObject
Dim myDataObject As DataObject

Set myDataObject = New DataObject
myDataObject.GetFromClipboard


Clipboard.GetFromClipboard

TextBox3.PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Many thanks!

gmayor
12-11-2020, 11:33 PM
What sort of text box? Some text boxes by definition will only contain text, and you have not indicated whether you are running the code from Word or Excel, though I assume Excel
A simple Paste command will by default keep the Excel formatting, provided the receptacle can contain it. If you need a receptacle then I would suggest using a rich text content control, then if that is titled TextBox3 the following will work subject to provisos
Sub PasteToWord()
Dim wdApp As Object
Dim wdDoc As Object
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err Then
Set wdApp = CreateObject("Word.Application")
End If
On Error GoTo err_Handler
Set wdDoc = wdApp.Activedocument
wdDoc.SelectContentControlsByTitle("TextBox3").Item(1).Range.Paste
lbl_Exit:
Exit Sub
err_Handler:
MsgBox "The content control 'TextBox3' is not present in the current document", vbCritical
Err.Clear
GoTo lbl_Exit
End SubThe Clipboard code from your macro is superfluous, and the example assumes that the document is open and active in Word and that the data has been copied to the clipboard.

HTSCF Fareha
12-12-2020, 01:27 AM
Thanks, gmayor for taking the time out to look at this for me.

I think I need to clarify a few bits of information that were ommitted from my original post.


The data that I need to transfer from Excel to Word is generated in Excel and is put into the clipboard from the same application. I have ensured that everything that I need to copy over is present and correct by opening a blank Word document and then pasting the contents into this by using the default paste button, selecting the "keeping the source formatting" option.

Both applications will be open and both will be using a VBA UserForm to perform their respective tasks.

The TextBox I need the content to arrive in (Word), is a UserForm TextBox which is currently called "TextBox3". It is Word that will be producing the final document.

The best that I have managed to achieve is the content being "copied" into TextBox3 but minus the formatting. Even copying from Excel and then pasting into the TextBox3 loses the required bold formatting.

The most frustrating thing is that the important formatting that I need to keep is certain pieces of text that are bold.

gmayor
12-12-2020, 06:28 AM
What you are trying to achieve is not possible. A userform text box does not support text formatting.

HTSCF Fareha
12-12-2020, 07:18 AM
Thank you for putting me out of my misery. Thought this might be the answer.

I think that I had probably stumbled across this fact myself when I couldn't paste with formatting directly into the TextBox.

This will leave your original suggestion and code. You mention that it "will work subject to provisos". What are these please?

gmayor
12-12-2020, 09:39 PM
The provisos are under the code.

HTSCF Fareha
12-13-2020, 01:26 AM
Ah yes, I'm with you.

This works superbly, thanks!