Macro Help: Keep formatting

[ElHonigmann]

Hello,

with the help of some Youtube videos i was able to write a small excel macro to help me copying content from my excel table into a word document (by using bookmarks for example).
The macro works fine: it is able to copy the content from the excel sheet to the word document. The problem is: subscript- and superscript-numbers and -letters are turned into normal letters. IF possible i would like to change that.

This is the code is used:

Sub nachwordkopieren()

Dim Probenliste As Object
Dim appWord As Object

Set appWord = CreateObject("Word.Application")
Set Probenliste = appWord.Documents.Add("filepath")

appWord.Visible = True

Probenliste.Activate
Probenliste.Bookmarks("Probenname1").Range.Text = Range("Probenname1")
Probenliste.Bookmarks("Verbindung1").Range.Text = Range("Verbindung1")



Set Probenliste = Nothing
Set appWord = Nothing

End Sub

Keep in mind i am no expert^^
Any ideas are appreciated

Greetings!

[BobBridges]

So, ElHonigmann, it sounds like you're saying you have some text in a cell in your workbook that contains super- and subscript characters...hm. Let me experiment....

I just put "normal sup sub" into a cell in a scratch worksheet, changed it to 20pt so I could see what's going on, and then recorded a macro changing three of the characters to superscript and three to subscript. The resulting code reads like this:

    ActiveCell.FormulaR1C1 = "normal sup sub"
    With ActiveCell.Characters(Start:=1, Length:=7).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With ActiveCell.Characters(Start:=8, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 20
        .Strikethrough = False
        .Superscript = True
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With ActiveCell.Characters(Start:=11, Length:=1).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With ActiveCell.Characters(Start:=12, Length:=3).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 20
        .Strikethrough = False
        .Superscript = False
        .Subscript = True
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With

Notice that the first statement copied the text from the .Value property to the .FormulaR1C1 property; maybe that's important. Then it set the Font properties not only for the two substrings that I made superscript and subscript, but for the two remaining "normal" substrings too. Again; maybe that's important. Now let's step into a macro and look at the .Characters property of that cell...

No, I can't look at it: "Object doesn't support this property or method". So the reference to ActiveCell.Characters(Start,Length) in the code above is neither a property nor a method. It must be a write-only Sub, returning no value and subject only to change, not to inspection. I'm still "hm"ing to myself. Not sure where to go from here.

[BobBridges]

No, I was wrong; it must have been some other property or method that wasn't there, because I'm now able to say "Set och = ActiveCell.Characters(Start,Length)". And when I examine och in the Watches window, I see that the various Font properties are as I set them originally, with Superscript and Subscript etc.

So it seems to me that you can, if you're willing to go to the necessary work, identify which characters in the cell's Text property are to have special handling, and to duplicate that handling in Word. But I'm guessing there's no way to do it in one easy step; you'd have to examine the text character by character as part of copying it over.

Unless, of course, you already know which characters should be suped and subed and just hard-code that.

I haven't forgotten that you claim to be "no expert"; feel free to ask about the parts of this that you don't understand.

[p45cal]

I know nothing of Word VBA but I tried this and it seems to go in the right direction:

Sub nachwordkopieren()
Dim Probenliste As Object
Dim appWord As Object

Set appWord = CreateObject("Word.Application")
Set Probenliste = appWord.Documents.Add("filepath")
appWord.Visible = True
Probenliste.Activate

Range("Probenname1").Copy
Probenliste.Bookmarks("Probenname1").Range.Paste
Range("Verbindung1").Copy
Probenliste.Bookmarks("Verbindung1").Range.Paste

Set Probenliste = Nothing
Set appWord = Nothing
End Sub