开发者

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.

  1. 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.
  2. 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
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜