Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-23-2020, 09:51 AM
ElHonigmann ElHonigmann is offline Macro Help: Keep formatting Windows 10 Macro Help: Keep formatting Office 2019
Novice
Macro Help: Keep formatting
 
Join Date: Jan 2020
Posts: 1
ElHonigmann is on a distinguished road
Default Macro Help: Keep formatting

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!
Reply With Quote
  #2  
Old 01-25-2020, 11:01 AM
BobBridges's Avatar
BobBridges BobBridges is offline Macro Help: Keep formatting Windows 7 64bit Macro Help: Keep formatting Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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:
Code:
    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.
Reply With Quote
  #3  
Old 01-25-2020, 11:16 AM
BobBridges's Avatar
BobBridges BobBridges is offline Macro Help: Keep formatting Windows 7 64bit Macro Help: Keep formatting Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 01-27-2020, 07:19 PM
p45cal's Avatar
p45cal p45cal is online now Macro Help: Keep formatting Windows 10 Macro Help: Keep formatting Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

I know nothing of Word VBA but I tried this and it seems to go in the right direction:
Code:
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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro Help: Keep formatting Macro to convert formatting? CTHockeyBA Word VBA 1 09-25-2018 06:13 PM
Conditional Formatting in a Macro jennyyoung Excel Programming 15 05-17-2018 09:39 AM
Conditional Formatting Macro grexcelman Excel Programming 1 01-10-2015 04:45 PM
Macro Help: Keep formatting Macro for text formatting georgekung84 Word VBA 15 08-08-2012 06:28 PM
Formatting macro Ulodesk Word Tables 9 04-10-2012 06:37 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:10 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