asked on

Failure to preserve rich text formatting when original text is copied from Visio shape and pasted into Excel. I seek an automated process & can provide HTML for the original rich text.

I have rich text in MS Visio shapes, that I wish to display, fully formatted, in Excel cells, through vba automation.
When I copy-paste the visio shape.text directly into an Excel cell, all formatting is lost, even bold, underline and superscript etc.
However, if I first copy the visio.shp.text into WORD, then, when I copy the text from WORD to Excel, the original 'visio' formating is preserved in both WORD and in EXCEL.
In this additional step, WordPAD is ineffective as a substitute for WORD (ie formatted text copied from WordPAD does not preserve formatting when the text is copied to Excel.)

I seek an automated solution (vba).
But an automated solution that takes Text from visio via WORD and through to Excel seems cumbersome. Is there an automated solution, and can I avoid the WORD step?

LATERALLY - I can convert visio.shape.text into an HTML string...
Can you tell me how I could display that HTML string (derived from Visio) as RICH TEXT in Excel?

Thanks!
Kelvin

ASKER CERTIFIED SOLUTION

Scott Helmers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Roger

ASKER

Many thanks!

Scott Helmers

For the benefit of others reading this question later: did the paste special with HTML format work as shown above or was something else required? Thanks

Roger

ASKER

Scott:
Thanks. I did a simple text within EXCEL, asking if I could copy raw HTML to clipboard, and paste same to another cell, using PasteSpecial Format:="Text" ', Link:=False, DisplayAsIcon:=False (as you suggested)
I entered his short HTML string: Abcd 1<sup>2</sup>345 into excel.activesheet cell "B2", and proceeded as below
The following uses .DataObject method to copy to clipboard. This pasted ok provided I did not specify Format:="HTML" or Format:="TEXT".

The clipboard was cleared:
Sub clearClipbrd()
    Application.CutCopyMode = False
End Sub

B2 was copied to clipboard:
Sub copyToClipboard()
    Dim clipboard As MSForms.DataObject
    Set clipboard = New MSForms.DataObject
    
    clipboard.SetText ActiveSheet.Range("B2")
    clipboard.PutInClipboard

End Sub

Open in new window

-- Results for the pasteSpecial step:
ActiveSheet.Range("C6").PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False 'bugged App-defined or Obj-defined error 1004
ActiveSheet.Range("C6").PasteSpecial Format:="Text" 'bugged App-defined or Obj-defined error 1004
*** ActiveSheet.Range("C6").PasteSpecial pasted exactly what I'd copied (unformatted) into Cell C6.

I'm not sure if that was a fair test?

There is an API method for copying to clipboard (eg: https://wellsr.com/vba/2015/tutorials/vba-copy-to-clipboard-paste-clear/ )
I shall try that as soon as I can.

Scott Helmers

Interesting, thanks.

Failure to preserve rich text formatting when original text is copied from Visio shape and pasted into Excel. I seek an automated process &amp; can provide HTML for the original rich text.

Failure to preserve rich text formatting when original text is copied from Visio shape and pasted into Excel. I seek an automated process & can provide HTML for the original rich text.