Is Excel Shape.TextFrame.Characters.Insert broken on Excel 2007?
I've got this piece of code I've been using Excel 2003 which mimics a solution given on the Microsoft website from a while back. How to add more than 255 characters to a shape's text frame:
For i = 0 To Int(Len(myTxt) / 255)
.Characters(.Characters.Count + 1).Insert Mid(myTxt, (i * 255) + 1, 255)
Next i
This just doesn't work under Excel 2007. There are two problems.
- Insert can't insert 开发者_开发知识库at the end of the field, it has insert at an existing character. So
.Characters(.Character.Count).Insert
will work, even though it's not what I want to do. - It's operating as an overwrite, not an insert. Wherever I apply Insert it overwrites the existing characters. So an Insert at
(.Character.Count)
will delete the final character.
Now I've built in logic to operate differently depending on the version of Excel you're using. But I couldn't find anything relating to this issue. Is it a known bug? Is there a way to fix this behaviour?
(On a related note, I cannot set .Characters(x,y).Font.Underline
= True either.)
EDIT In my particular example, the code above is contained with something like With myWorksheet.Shapes(1)
where the shape is a text box. It already has text inside it and I need to append myTxt
(a string
longer than 255 characters) to it. This code worked without problem on Excel 2003. I've seen this problem referred to elsewhere, but looking for a formal statement on it from Stack Overflow...
I agree, your code works in 2003 but fails in 2007. What's surprising to me, though, is that it works at all. Trying to reference "one more" character in the .Characters
collection should throw an error--and if you break up your line above, as I think you're pointing out in point 1, you'll see that it's specifically .Characters(.Characters.Count + 1)
that does. It's not the method that fails, it's the non-existent member, which seems right to me.
Here's code that does what you want in both 2003 and 2007.
Public Sub Loop_InsertTest()
Dim MyWks As Excel.Worksheet
Dim MyTxt As Shape
Dim MyFrme As TextFrame
Dim i As Long
Const StartText As String = "This is a very, very, very, very, very, very, very, very, very, very, very, " _
& "very, very, very, very, very, very, very, very, very, very, very, very, very, very, very, " _
& "very, very, very, very, very, very, very, very, very, very, long piece of text."
Const MaxIterations As Long = 1000
Debug.Print Len(StartText)
Set MyWks = ThisWorkbook.Worksheets(1)
Set MyTxt = MyWks.Shapes.AddTextbox(msoTextOrientationHorizontal, 10, 10, 1000, 1000)
Set MyFrme = MyTxt.TextFrame
'Debug.Print TypeName(MyTxt), MyTxt.Name'
MyFrme.Characters.Text = StartText
MyFrme.AutoSize = True
For i = 1 To MaxIterations
Insert_ThisText MyFrme, " Now it's even longer."
Next i
End Sub
Private Sub Insert_ThisText(pFrme As TextFrame, _
pstrText As String)
Dim strRight As String
Dim i As Long
With pFrme
For i = 0 To Int(Len(pstrText) / 254)
strRight = .Characters(.Characters.Count).Text
.Characters(.Characters.Count).Insert strRight & Mid(pstrText, (i * 254) + 1, 254)
'Debug.Print Len(pstrText), .Characters.Count'
Next i
End With
End Sub
精彩评论