#1
|
|||
|
|||
How to extract text from a cell and preserve special formatting?
The goal of the VBA macro is to extract the text from the first cell and assign it to a variable (see attached document). The problem that I’m having is that I can’t get it to retain the superscripted letter ‘s’ after the word ‘test’.
TIA, Robert Matthews Code:
Sub test() Dim varTextString As Variant varTextString = ActiveDocument.Tables(1).Cell(Row:=1, Column:=1) varTextString = Left(varTextString, Len(varTextString) - 2) MsgBox varTextString End sub() |
#2
|
|||
|
|||
Mark up the formatted characters
The most effective way I've found to do this is to mark the special formatting first with codes (might as well use XML syntax), grab your text, then remove the codes:
Code:
Sub test() With Selection.Find .ClearFormatting .Replacement.ClearFormatting .Text = "" .Forward = True .Wrap = wdFindContinue .Format = True .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.HomeKey Unit:=wdStory With Selection.Find .Font.Superscript = True .Replacement.Text = "<sup>^&</sup>" End With Selection.Find.Execute Replace:=wdReplaceAll Selection.HomeKey Unit:=wdStory With Selection.Find .Font.Bold = True .Replacement.Text = "<b>^&</b>" End With Selection.Find.Execute Replace:=wdReplaceAll Dim varTextString As Variant varTextString = ActiveDocument.Tables(1).Cell(Row:=1, Column:=1) varTextString = Left(varTextString, Len(varTextString) - 2) MsgBox varTextString Selection.HomeKey Unit:=wdStory With Selection.Find .Text = "<sup>" .Replacement.Text = "" End With Selection.Find.Execute Replace:=wdReplaceAll Selection.HomeKey Unit:=wdStory With Selection.Find .Text = "</sup>" .Replacement.Text = "" End With Selection.Find.Execute Replace:=wdReplaceAll Selection.HomeKey Unit:=wdStory With Selection.Find .Text = "<b>" .Replacement.Text = "" End With Selection.Find.Execute Replace:=wdReplaceAll Selection.HomeKey Unit:=wdStory With Selection.Find .Text = "</b>" .Replacement.Text = "" End With Selection.Find.Execute Replace:=wdReplaceAll End Sub |
#3
|
||||
|
||||
The basic issue is that string variables are just that - strings; they cannot contain formatting information. And, even if they could, a message box cannot display such formatting. If you could explain what you're trying to achieve, we could perhaps suggest another way. I wouldn't be inclined to go down Larry's route, though, unless there's no better way, as that route adds what may be a lot of unnecessary overheads if all aspects of formatting are to be preserved.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need macro to merge files in Word and preserve formatting | Carolin | Word VBA | 3 | 12-14-2014 04:01 AM |
How do I output special character to a specific cell (x,y)? | norwood | Word VBA | 2 | 01-31-2014 01:26 PM |
Extract Min and highlight cell from source | LeilaniMerle | Excel | 3 | 01-26-2014 11:05 PM |
Conditional Formatting Expiration Dates Based on Text in Adjacent Cell | Frogggg | Excel | 1 | 10-25-2011 08:44 PM |
Cut and paste a range of cells and preserve formatting | StarWeaver | Excel | 1 | 03-02-2010 01:41 PM |