Excel shapes - editing / creating mixed format

Jan Kučera

Hi,

I have a shape in Excel 2007, which looks like this:

Code Snippet

My Project

cool sheet

Project Number

Now, I would like by C# (Orcas), eventually VBA modify the individual paragraphs, like Project Number without loosing the formating.

I also need to create such formatted shapes. How

The Excel macro recording does not capture operations with shapes.

I have also found the Sheet.Shape.TextFrame2 object, but I don't know how to get/modify individual paragraphs, since I see only Text property of "My Project cool sheet Project Number" in the VBA watch window.

If this can't be done, what workaround do you suggest Three text boxes in layers

Thank you,

Jan Kucera

Jan Ku?era

Okay, I found Lines, Paragraphs, Runs and Senteces. What is the difference between these

This allows me to edit the such shape. How to create one eg. there is no Lines.Add and so on...

Dennis Wallentin

Hi Jan,

Excel is not good with shapes (which is also the case with me ).

I assume that You work with a workbook level solution in Orca. Please try the following:

• Open the workbook in Excel 2007.
• Add a native worksheet to the workbook.
• Format and "configure a range in the added worksheet.
• Copy the range.
• Swith to the worksheet that will be showing it.
• Select the command Paste | As Picture... | Past Link Picture
• Hide the added worksheet.
• Save the workbook.

When You retrieve projectnumber etc from the users You write the information to the range in the hidden worksheet. It will be automatically updated in the visible targeting sheet.

Jan Ku?era

Hi Dennis! Thank you for your reply. However I am afraid I did not explain my problem correctly enough. It doesn't matter if I use VBA or VSTO because I believe they have both the same power (actually I make an add-in). I realize that Excel might not seem to be very good at shapes (as I thought too) but by inspecting its object model, it seems that shape handling is full featured. If I'd like to store the "project number" as such, I think this is what custom properties are for, without need for hidden objects - actually, being an add-in, I do not have any either. Now I just wonder how the properties I mentioned above differ and what is the best practice for creating such formated ranges in one shape. Jan

Cindy Meister

Hi Jan

From the looks of things, you want to work with the new Office 2007 graphics. These don't have a very good object model - no idea whether they may acquire one in a future version of Office. But for the moment, you need to use the Office Open XML file format to work with the full spectrum of functionality offered by the new graphics engine. You might try looking around and asking at the OpenXMLDeveloper.org website.

Jan Ku era wrote:

Hi, I have a shape in Excel 2007, which looks like this: Code Snippet My Project cool sheet Project Number Now, I would like by C# (Orcas), eventually VBA modify the individual paragraphs, like Project Number without loosing the formating. I also need to create such formatted shapes. How The Excel macro recording does not capture operations with shapes. I have also found the Sheet.Shape.TextFrame2 object, but I don't know how to get/modify individual paragraphs, since I see only Text property of "My Project cool sheet Project Number" in the VBA watch window. If this can't be done, what workaround do you suggest Three text boxes in layers Thank you, Jan Kucera

Jan Ku?era

Cindy! Hello! If you're thinking about the new things called SmartArt I really don't nothing about its support in the object model and although I'd be interested whether you can change the xml on the fly within the document itself, I don't want to mix this stuff in this thread. I use standard shapes which are in office applications quite a while I think, particularly my one is the msoShapeSnipSoundRectangle. In the model I could (although with scepticism) find whatever came to my mind yet, points adjustments, gradient stops, transparency. So the only things I mentioned remain a little bit unclear to me: Difference between the Lines, Sentences, Paragraphs and Runs properties of TextRange2 object and if there is a way how to add items to these collections. Have a nice day! Jan

Cindy Meister

Hi Jan

ah, but you see, TextRange2 is a new object that is definitely linked to the new graphics engine. It may appear to you that you're working with the same "Shape" as in previous versions, but in fact you are not. The "AutoShapes" accessed via the user interface are part of the new graphics engine...

Have you tried reading the Help for TextRange2 via Excel's VBA Editor That explains what these things are, and from the look of it, it builds on the WordProcessingXML and uses the basic approach of working with formatted text in Word.

The following takes an existing AutoShape on the ActiveSheet and adds some text. This creates one "Run" in the TextRange. It makes the second word red, which creates three runs. Inserting a paragraph mark before the third word (which is coincidently the third Run) creates a second paragraph, but you still have only three runs at this point.

Dim shp As Excel.Shape

Set shp = ActiveSheet.Shapes(1)
With shp.TextFrame2.TextRange
.Text = "Testing things out"
.Words(2).Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Runs(3).InsertBefore vbCr
Debug.Print .Runs.Count
.Paragraphs(2).ParagraphFormat.Alignment = msoAlignRight
End With

That's the simple way of looking at it. Just as when working in Word, you get more control if you work more closely with the object model, as the following bit of sample code. Note carefully the use of the TextRange2 object (corresponding to the Range object in Word). But you really should be exploring this in the excel.programming newsgroup, where the people are more familiar with the object model. This isn't really a VSTO question :-)

Sub TestShapeAttributes()
Dim shp As Excel.Shape
Dim rng As Office.TextRange2
Dim rngWord2 As Office.TextRange2
Dim rngRun3 As Office.TextRange2
Dim rngPara As Office.TextRange2
Dim fnt As Office.Font2

Set shp = ActiveSheet.Shapes(1)
Set rng = shp.TextFrame2.TextRange
rng.Text = "TEsting things out"

Set rngWord2 = rng.Words(2)
Set fnt = rngWord2.Font
With fnt
.Fill.ForeColor.RGB = RGB(255, 0, 0)
.Bold = msoTrue
End With

Set rngRun3 = rng.Runs(3)
rngRun3.InsertBefore vbCr

Set rngPara = rng.Paragraphs(2)
rngPara.ParagraphFormat.Alignment = msoAlignRight

Debug.Print rng.Runs.Count
End Sub

Jan Ku?era

Thank you for your answer and explanation. Three things caused me to post here: I'm working in VSTO using the VBA just for reference because the documentation in VSTO is pure and "proxy object cannot be evaluated" is all around , I did not find any excel.vba group as in Word case, and discussions are moving from nntp to forums, if there is any. Well and actually no answer was coming to my post to such news group. But you're right, I apologize and thank you. Jan