Consulting

Results 1 to 7 of 7

Thread: Paste and keep source formatting

  1. #1
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location

    Paste and keep source formatting

    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!
    Last edited by HTSCF Fareha; 12-11-2020 at 08:06 AM.

  2. #2
    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 Sub
    The 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.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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.

  4. #4
    What you are trying to achieve is not possible. A userform text box does not support text formatting.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    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?

  6. #6
    The provisos are under the code.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  7. #7
    VBAX Mentor
    Joined
    Aug 2020
    Location
    Hampshire
    Posts
    395
    Location
    Ah yes, I'm with you.

    This works superbly, thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •