Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2014, 11:36 AM
epid011 epid011 is offline How to extract text from a cell and preserve special formatting? Windows 7 64bit How to extract text from a cell and preserve special formatting? Office 2010 32bit
Novice
How to extract text from a cell and preserve special formatting?
 
Join Date: Dec 2013
Posts: 12
epid011 is on a distinguished road
Default 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()

Attached Files
File Type: docx Table test with superscripts.docx (13.6 KB, 13 views)
Reply With Quote
  #2  
Old 04-04-2014, 01:43 PM
Larry Sulky Larry Sulky is offline How to extract text from a cell and preserve special formatting? Windows 7 64bit How to extract text from a cell and preserve special formatting? Office 2010 64bit
Novice
 
Join Date: Mar 2014
Posts: 14
Larry Sulky is on a distinguished road
Default 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
Reply With Quote
  #3  
Old 04-04-2014, 04:54 PM
macropod's Avatar
macropod macropod is online now How to extract text from a cell and preserve special formatting? Windows 7 32bit How to extract text from a cell and preserve special formatting? Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by epid011 View Post
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to extract text from a cell and preserve special formatting? Need macro to merge files in Word and preserve formatting Carolin Word VBA 3 12-14-2014 04:01 AM
How to extract text from a cell and preserve special formatting? 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
How to extract text from a cell and preserve special formatting? Cut and paste a range of cells and preserve formatting StarWeaver Excel 1 03-02-2010 01:41 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:31 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft