Excel 97 - have some questions

[MarekTheStudent]

Hello everyone,

I have some questions on changing some attributes or properties in an excel-file controlled from my CVI program. I already can export data to a worksheet and can make a chart and other things, but some things i cannot find or I do not understand. I'm sorry for some of my questions because I am sure some of them are silly but actually I must hury with my project and I already searched long time for solutions so a little help of you would be very nice.

1) I do not understand exactly this VARIANT variables. I want to move a sheet forward or backward with the function 'Excel_SheetsMove (Range_Handle, NULL, CA_DEFAULT_VAL, CA_DEFAULT_VAL);' but what should I now put in instead of 'CA_DEFAULT_VAL'?

2) Another problem with this VARIANT variable: I want to change the background color of my chart. I think I need the attribute 'SchemeColor' but what should I now put in as a color? How do I define a VARIANT variable as a color?

3) Which function is needed for changing the properties of a cell? I want to make less places behind my dot/comma. My numbers are looking like that '1034.2376' but I want it to look like that '1034.2'.

4) A yes-/no-question: Do I every time need a range in a sheet to put data/numbers in a cell that is in the range or is there another possibility?

Okay, I think that's all for now and sorry for my bad english but the more I am reading and posting here, the better it gets. 😉

Greetings
Marek S.

[MarekTheStudent]

Nobody has an answer? 😕

[DaveC]

Hi Marek,

A couple answers for you:

VARIANT is acutally a struct of unions that allows you basically to set any data type into this one struct. If you wish to set a value in a variant, you need to use one of the VariantSet functions in the CVI 'ActiveX Library' function library.

For question #1. The exact values that you need to put in instead of CA_DEFAULT_VAL aren't determined by labwindows. Labwindows simply leverages the existing ActiveX libraries from microsoft. YOu will need to go to MSDN and find out the Excel 97 ActiveX API to get these values. Here is a link that might help you get started. If you can't find the API I would suggest contacting MS.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/devref/HTML/DVS_01_Introduction__557.asp

For question #2. I would try getting the hex value of the color you wish to set and converting it to an integer. Then use the CA_VariantSetInt function. If that doesn't work, you will have to go through the same procedure as you did to answer question #1.

For question #3, I would try using the Excel_CellFormatSetNumberFormat, though again you will need the API to know which values will give you the correct number format.

[Wendy L]

Hello Marek,

Regarding your second question, it seems like you need to use the SetChartAttribute function with the Chart Area Color attribute. This function is located in the ExcelReport instrument. The documentation for this function mentions that the color value you insert are going to be RGB values and you need to use the MakeColor function. For instance, a sample call would look like:

ExcelRpt_SetChartAttribute (chartHandle, ER_CH_ATTR_CHARTAREA_COLOR, MakeColor(R, G, B));

Regarding your fourth question, you do need to specify a range when changing the attribute of a cell, but the range parameter can vary from once cell ("A1") or an actual range ("A1:A10"). For instance, to set a specific cell attribute, you could so something like:

ExcelRpt_SetCellRangeAttribute (worksheetHandle, "A1", , );

Hope that helps.

[MarekTheStudent]

Hello Wendy L, hello DaveC,

great thanx for this help. Now I know my fault. Before I included the 'excel97.fp', 'excel97.c' and 'excel97.h' files. This was some kind of a mistake or the reason for why I couldn't find the options and attributes I searched. In the Library as an instrument there was only the "Microsoft Excel Object Library" but not the menu "Excel Report". As you both answered my questions I saw my fault. Now I included instead of the 'excel97.fp' the 'excelreport.fp', 'excel2000.fp' and the rest of it and not it works and I find all my attributes I searched before.

Hope this answer can also help other people for not making the same mistake.

Sincerely yours
Marek S.

[davido]

Answer to Question nr.1 :

This is quite a general use of VARIANT variables.

* Step1: Declare a VARIANT variable:

VARIANT MyVariant;

* Step2: Use the function CA_VariantSetObjHandle to transform, a handle to the sheet you want to reffer to as "after" or "before", into a VARIANT variable:

CA_VariantSetObjHandle (MyVariant, SheetHandlePassive, CAVT_DISPATCH);

* Step3: Now use the move command:

Excel_SheetsAdd (SheetsHandle, NULL, CA_DEFAULT_VAL, MyVariant, CA_DEFAULT_VAL, CA_DEFAULT_VAL, &SheetHandleActive);

In this case the sheet are moved to a position "after" another sheet.

[davido]

Note to my answer above:

The command should be the sheet move command:

Excel_WorksheetMove (SheetHandleActive, NULL, CA_DEFAULT_VAL, MyVariant);

This ofcourse works with the Add command also, that was described in the answer above.