How to extract text from a cell and preserve special formatting?

epid011 · #1 04-04-2014, 11:36 AM

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()

Larry Sulky · #2 04-04-2014, 01:43 PM

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

macropod · #3 04-04-2014, 04:54 PM

Quote:

Originally Posted by epid011

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’.

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.

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