How to add a text box to an Excel chart

[HVchukes]

I'm generating a number of charts in Excel 2003 from data in LabWindows 7.1

I now need to add a text box to each chart to show information about the waveform (waveform name, test conditions, etc). How do you do this?

Or perhaps there is an easier or better way than using a text box?

Thanks in advance

[Perry_S]

Hi HVchukes,

Could you clarify what you mean by "Text Box" If you could make one in excell and attach a screen shot or the file itself that would be great. Thanks

Perry S.

[HVchukes]

Hi Perry,

Please see the screenshot attached.

I have tried using two different methods to create a textbox as shown both so far I can't even get it to appear on the chart

Excel_ChartTextBoxes (ExcelChartHandle, NULL, CA_DEFAULT_VAL, &ExcelTextBoxHandle);

 and also

Excel_ShapesAddTextbox (ExcelChartHandle, &errinfo, ExcelConst_msoTextOrientationHorizontal, 98, 150, 200, 90, &ExcelTextBoxHandle);

The lack of documentation for these functions makes it very hard for me to figure out what some of the parameters are meant to be 

Ultimately, I want to automatically write certain parameters about the plotted waveform (which are calculated in LabWindows) in the textbox. I think I would also need to modify some properties of the textbox such as the colour, border, font, etc

[HVchukes]

Also, I should add that I don't want to use a macro to do this.

Using a macro would mean that my program has to open a specific *.xls file containing the macro - this would lead to problems such as if the *.xls file is changed, missing, etc

I want the LabWindows program to be as self-sufficient as possible in this respect

[Perry_S]

Hi HVchukes,

These functions are actually wrappers around ActiveX controls that Microsoft Excel made available. As such the documentation on how to use these functions will be on Microsoft's website :

http://msdn.microsoft.com/en-us/ms348103

I will be looking here myself to figure this out. Thanks for being so patient.

Regards,

Perry S.

[dcl9000]

I had a similar issue, too.  Creating a text box in Excel worksheet with Excel_ShapesAddTextbox(...) function is not a problem.  But I have no clue on how to inserting text and modifying the text box. 

It is easy to do in Visual Basic such as (found in http://msdn.microsoft.com/en-us/library/aa171543(office.11).aspx😞

-------------------------------------------------------------

Sub newTextbox()
    Dim docNew As Document
    Dim newTextbox As Shape

    'Create a new document and add a text box
    Set docNew = Documents.Add
    Set newTextbox = docNew.Shapes.AddTextbox _
        (Orientation:=msoTextOrientationHorizontal, _
        Left:=100, Top:=100, Width:=300, Height:=200)

    'Add text to the text box
    newTextbox.TextFrame.TextRange = "Test"
End Sub

----------------------------------------------------------

The problem is how to "get" the property of "TextFrame.Text" or "TextFrame.TextRange" and assign the text to insert into the text box.  There's no obvious corresponding definition in the exmaple codes from NI's "excel2000.c" and "excel2000.h". 

Hope someone can figure that out for us. 

By the way, if you don't definitely need to use Excel, DPlot (www.dplot.com) is a much better choice for generating high quality graphs.  The free DLL library it offers can be integrated into your C code easily and seamlessly. 

[HVchukes]

---

@dcl9000 wrote:

Creating a text box in Excel worksheet with Excel_ShapesAddTextbox(...) function is not a problem. 

---

Could you tell me exactly what parameters you sent to get the Textbox? So far I can't even get that to work

And thanks for the tip about DPlot. But I think it'd be better for me to stick with Excel as my program already uses Excel spreadsheets for some calculations and report generation

[dcl9000]

You can modify the example CVI project "excel2000dem.prj" shipped with LabWindows by adding the following lines of code into "excel2000dem.c":

-------------------------------------------------------------------------------------

HRESULT ExcelRpt_InsertTextBox (void)
{
   HRESULT  error=0 ;
   CAObjHandle shapesHandle = 0 ;
   CAObjHandle shapesTextBoxHandle = 0;
   double top=300, left=100, width=100, height=100;    // final dimensions is width x height     
   

  //... I assume you already open and activate an empty Excel worksheet and

  //    a proper worksheet handle "ExcelWorksheetHandle" is generated.

 
   error = Excel_GetProperty (ExcelWorksheetHandle, &ErrorInfo, Excel_WorksheetShapes,
                              CAVT_OBJHANDLE, &shapesHandle) ;
   if (error<0)  goto Error ;
  
   // Create a text box
   error = Excel_ShapesAddTextbox (shapesHandle, &ErrorInfo, ExcelConst_msoTextOrientationHorizontal,
                                   left, top, width, height, &shapesTextBoxHandle) ;
   if (error<0)  goto Error ;

Error:
   
         ClearObjHandle(&shapesHandle);
         ClearObjHandle(&shapesTextBoxHandle); 
         if (error<0)  ReportAppAutomationError(error);
   
   return  error ;
}

------------------------------------------------------------------------------------

This will create an empty text box and that's all it does. 

Let us know if you can insert text in it.

[HVchukes]

Thanks for the help guys, but I've made absolutely no progress in this.

I've decided to use an Excel macro after all to create a text box in my chart and then write data into it. While it's not an ideal solution for me, it's better than no solution whatsoever!

Btw, dcl9000, thanks for putting up the code to create a text box. I played around with it somewhat and, like you, I couldn't figure out how to write text into it. Also, I couldn't seem to modify it to put the textbox inside a chart sheet by using this function call:

error = Excel_GetProperty (ExcelChartsheetHandle, &ErrorInfo, Excel_ChartShapes,CAVT_OBJHANDLE, &shapesHandle)

I can't see why it shouldn't work...

While it probably won't be used in my current program, I would be interested to know for future reference if there actually is a way to create and write to textboxes from LabWindows. Surely it shouldn't be this hard!

Thanks again for your help guys

[dcl9000]

Finally figure out a way to add a text box into an Excel worksheet. 

Here's the major part of the code (modifying the example "excel2000demo.prj") to make that happen:

-------------------------------------------------------------------------------

   CAObjHandle shapesHandle = 0 ;
   CAObjHandle shapesShapeHandle = 0 ;
   CAObjHandle shapesShapeTextFrameHandle = 0 ;
   CAObjHandle shapesShapeTextFrameCharactersHandle = 0 ;
   CAObjHandle textEffectHandle = 0 ;
   double top=300, left=100, width=100, height=100;   

//... assuming a Worksheet has been created and activated ....

error = Excel_GetProperty (ExcelWorksheetHandle, &ErrorInfo, Excel_WorksheetShapes,
                               CAVT_OBJHANDLE, &shapesHandle) ;

if (error<0)  goto Error ;

//   A text can be added in any shape, not necessarily a box.    
       
   error = Excel_ShapesAddShape (shapesHandle, &ErrorInfo,
                                 ExcelConst_msoShapeRectangle, left, top,
                                 width, height, &shapesShapeHandle);
   if (error<0)  goto Error ;
  
  
   error = Excel_GetProperty (shapesShapeHandle, &ErrorInfo, Excel_ShapeTextFrame,
                               CAVT_OBJHANDLE, &shapesShapeTextFrameHandle);
   if (error<0)  goto Error ;
  
  
   //-- Must use this method to get the Characters handle of a TextFrame
   //   "Characters" is a method of a TextFrame, not a property!
   //   Very strange! Microsoft doesn't include "Text" as a property of a
   //   TextFrame. 
   error = Excel_TextFrameCharacters (shapesShapeTextFrameHandle, NULL,
                                      CA_DEFAULT_VAL, CA_DEFAULT_VAL,
                                      &shapesShapeTextFrameCharactersHandle);
   if (error<0)  goto Error ;
   
   error = Excel_CharactersInsert (shapesShapeTextFrameCharactersHandle,
                                   &ErrorInfo, "text message", &myVariant);
   if (error<0)  goto Error ;
  

Error:

         //--- clean up and release memory
         CA_VariantClear(&myVariant);
         ClearObjHandle(&shapesHandle);
         ClearObjHandle(&shapesShapeHandle); 
         ClearObjHandle(&shapesShapeTextFrameHandle);
         ClearObjHandle(&shapesShapeTextFrameCharactersHandle);
        

         if (error<0)  ReportAppAutomationError(error);

--------------------------------------------------------------------------------------------------------------------------------