Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
Hello Community,
I've got a problem with a large Excel-Sheet filled mainly with text. Each cell contains a formated text (bold, underline, color, ...) with manual line-wraps (made with ALT-Enter). So far everything works fine, but now I'm trying to add text to this cell. My first try was cells(a,b).value =cells(a,b).value + NewText This set all formats to the format of the first line of cell. For the next approach I tried using the CHARACTERS-Property. DLen = Worksheets("LOP").Range("F37").Characters.Count Set DCh = Worksheets("LOP").Range("F37").Characters(DLen-1, 0) DCh.Insert NewText This worked perfectly, except that it only worked for cells with less than 255 characters. Unfortunately most of the cells have more than 500 characters, even up to 2000 characters. With cells larger than 255 characters simply nothing happened. Has anyone an idea how to add text to a large cell without destroying the existing formats? Thanks in advance. MichaB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
On 15 Mar 2006 05:04:35 -0800, "MichaB" wrote:
Hello Community, I've got a problem with a large Excel-Sheet filled mainly with text. Each cell contains a formated text (bold, underline, color, ...) with manual line-wraps (made with ALT-Enter). So far everything works fine, but now I'm trying to add text to this cell. My first try was cells(a,b).value =cells(a,b).value + NewText This set all formats to the format of the first line of cell. For the next approach I tried using the CHARACTERS-Property. DLen = Worksheets("LOP").Range("F37").Characters.Count Set DCh = Worksheets("LOP").Range("F37").Characters(DLen-1, 0) DCh.Insert NewText This worked perfectly, except that it only worked for cells with less than 255 characters. Unfortunately most of the cells have more than 500 characters, even up to 2000 characters. With cells larger than 255 characters simply nothing happened. Has anyone an idea how to add text to a large cell without destroying the existing formats? Thanks in advance. MichaB Not sure what you are asking. c.value = c.value & NewText or c.value = c.text & NewText does not change the cell format on my system. If you mean that you have different formats for different parts of the string, in the same cell, then I believe you will have to reset them after writing the new string to the cell. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
Hi, try this:
current_text = Cells(1, 1).Text my_text = current_text & "something" Cells(1, 1).FormulaR1C1 = my_text Henrich €žMichaB" napĂ*sal (napĂ*sala): Hello Community, I've got a problem with a large Excel-Sheet filled mainly with text. Each cell contains a formated text (bold, underline, color, ...) with manual line-wraps (made with ALT-Enter). So far everything works fine, but now I'm trying to add text to this cell. My first try was cells(a,b).value =cells(a,b).value + NewText This set all formats to the format of the first line of cell. For the next approach I tried using the CHARACTERS-Property. DLen = Worksheets("LOP").Range("F37").Characters.Count Set DCh = Worksheets("LOP").Range("F37").Characters(DLen-1, 0) DCh.Insert NewText This worked perfectly, except that it only worked for cells with less than 255 characters. Unfortunately most of the cells have more than 500 characters, even up to 2000 characters. With cells larger than 255 characters simply nothing happened. Has anyone an idea how to add text to a large cell without destroying the existing formats? Thanks in advance. MichaB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
If you mean that you have different formats for different parts of the string,
in the same cell, then I believe you will have to reset them after writing the new string to the cell. That's exactly my problem, that the text in the cell has different formats for different parts of the string. I want to avoid that these "local formats" get lost, because I don't think I can recover them. MichaB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
Hello Henrich,
unfortunately it didn't work. The result is that the complete cell has now the format "BOLD, UNDERLINE" like the first line. But before, only the first line had this format, in the second line, part of the text was UNDERLINED and the rest of the text was just plain text. Is there any opportunity to add a picture to this message? If yes I can show you both, before and after. By the way, what does "napísal/napisala" mean? Is this Spanish? Or Portoguise? MichaB |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
I think you'll have to keep track of the characteristics you want to
keep--character by character. Then append the text, then reapply those characteristics. And what should happen to that text that is appended? Should it inherit the same formatting as the last character in the cell (like characters().insert works) or do you want to set it to a default format? Anyway, this (very slow working macro when the text is quite large) may help you: Option Explicit Type myCharacter myChar As String myName As String myFontStyle As String mySize As Double myStrikethrough As Boolean mySuperscript As Boolean mySubscript As Boolean myOutlineFont As Boolean myShadow As Boolean myUnderline As Long myColorIndex As Long End Type Sub testme01() Dim myCell As Range Dim myText As String Set myCell = ActiveSheet.Range("a2") myText = "QWER" If (Len(myCell.Value) + Len(myText)) < 256 Then myCell.Characters(Len(myCell.Value) + 1, 1).Insert myText Else Call AppendKeepFormatting(myCell, myText) End If End Sub Sub AppendKeepFormatting(myCell As Range, myString As String) Dim cCtr As Long 'character counter Dim LastChar As Long LastChar = Len(myCell.Value) Dim myCharacters() As myCharacter ReDim myCharacters(1 To LastChar) For cCtr = 1 To LastChar With myCell.Characters(cCtr, 1) myCharacters(cCtr).myName = .Font.Name myCharacters(cCtr).myFontStyle = .Font.FontStyle myCharacters(cCtr).mySize = .Font.Size myCharacters(cCtr).myStrikethrough = .Font.Strikethrough myCharacters(cCtr).mySuperscript = .Font.Superscript myCharacters(cCtr).mySubscript = .Font.Subscript myCharacters(cCtr).myOutlineFont = .Font.OutlineFont myCharacters(cCtr).myShadow = .Font.Shadow myCharacters(cCtr).myUnderline = .Font.Underline myCharacters(cCtr).myColorIndex = .Font.ColorIndex End With Next cCtr myCell.Value = myCell.Value & myString For cCtr = 1 To LastChar With myCell.Characters(cCtr, 1) .Font.Name = myCharacters(cCtr).myName .Font.FontStyle = myCharacters(cCtr).myFontStyle .Font.Size = myCharacters(cCtr).mySize .Font.Strikethrough = myCharacters(cCtr).myStrikethrough .Font.Superscript = myCharacters(cCtr).mySuperscript .Font.Subscript = myCharacters(cCtr).mySubscript .Font.OutlineFont = myCharacters(cCtr).myOutlineFont .Font.Shadow = myCharacters(cCtr).myShadow .Font.Underline = myCharacters(cCtr).myUnderline .Font.ColorIndex = myCharacters(cCtr).myColorIndex End With Next cCtr For cCtr = LastChar + 1 To Len(myCell.Value) With myCell.Characters(LastChar + 1, Len(myCell.Value) - LastChar) .Font.Name = myCharacters(1).myName .Font.FontStyle = myCharacters(1).myFontStyle .Font.Size = myCharacters(1).mySize .Font.Strikethrough = myCharacters(1).myStrikethrough .Font.Superscript = myCharacters(1).mySuperscript .Font.Subscript = myCharacters(1).mySubscript .Font.OutlineFont = myCharacters(1).myOutlineFont .Font.Shadow = myCharacters(1).myShadow .Font.Underline = myCharacters(1).myUnderline .Font.ColorIndex = myCharacters(1).myColorIndex End With Next cCtr End Sub MichaB wrote: Hello Community, I've got a problem with a large Excel-Sheet filled mainly with text. Each cell contains a formated text (bold, underline, color, ...) with manual line-wraps (made with ALT-Enter). So far everything works fine, but now I'm trying to add text to this cell. My first try was cells(a,b).value =cells(a,b).value + NewText This set all formats to the format of the first line of cell. For the next approach I tried using the CHARACTERS-Property. DLen = Worksheets("LOP").Range("F37").Characters.Count Set DCh = Worksheets("LOP").Range("F37").Characters(DLen-1, 0) DCh.Insert NewText This worked perfectly, except that it only worked for cells with less than 255 characters. Unfortunately most of the cells have more than 500 characters, even up to 2000 characters. With cells larger than 255 characters simply nothing happened. Has anyone an idea how to add text to a large cell without destroying the existing formats? Thanks in advance. MichaB -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
On 15 Mar 2006 07:19:11 -0800, "MichaB" wrote:
If you mean that you have different formats for different parts of the string, in the same cell, then I believe you will have to reset them after writing the new string to the cell. That's exactly my problem, that the text in the cell has different formats for different parts of the string. I want to avoid that these "local formats" get lost, because I don't think I can recover them. MichaB As I wrote, you will have to reset the formats each time you expand the string. Somehow you will need to keep track of the formats for the differing segments of this string; or detect them. Dave posted some code that may be of use to you in this regard. --ron |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
Hello Dave,
thank you very much, this way it works, but you're right, it's really slow, even if I reduce the properties down to 3 instead of 10 (~20 seconds for one 1000 character cell). Is there no way to just copy the complete FONT-Information in one object? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert Characters in cell with formatted text
Not that I know.
MichaB wrote: Hello Dave, thank you very much, this way it works, but you're right, it's really slow, even if I reduce the properties down to 3 instead of 10 (~20 seconds for one 1000 character cell). Is there no way to just copy the complete FONT-Information in one object? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A lot of #'s in my text-formatted cell... | Excel Discussion (Misc queries) | |||
How to insert special characters in a cell | Excel Discussion (Misc queries) | |||
Insert characters in a text string | Excel Worksheet Functions | |||
Converting 'General' formatted cells to Text formatted cell using. | Excel Worksheet Functions | |||
How to insert Asian characters into a cell? | Excel Programming |