Removing Autofilter and Unfreezing Panes in Excel sheet with the help of CVI function

[Rajpriya]

Hi,

  I am using Lab windows CVI. Which function should be used for removing Autofilter and Unfreezing Panes in Excel sheet.

Thanks & Regards,

Rajesh

[Doiron]

Hi Rajesh,

The function that does this is most likely going to be "Set Property" in the Excel Report Instrument, see here. Add the .fp files at that location and you can browse the different functions and attributes that those functions control.

[Rajpriya]

Dear David,

         Thanks for your warm response. I already tried to find with following functions: 

ExcelRpt_SetApplicationAttribute ();

ExcelRpt_SetWorksheetAttribute ();

ExcelRpt_SetCellRangeAttribute ();

But I didnot find the attribute related to removing filter and unfreezing panes. I checked with the other functions also. Could you please help me in finding the functions.

Thanks & Regards,

Rajesh

[Doiron]

Hello Rajesh,

Yes it looks like "Microsoft Excel 9.0 Object Library" is the instrument to use rather than "Excel Report". It's important to note that when I loaded the "Excel Report.fp" the "Excel2000.fp" loaded as well which is what loaded the "Microsoft Excel 9.0 Object Library" instrument.

Could you tell me exactly how you perform this functionality in Excel (ie which menu you go to, etc) in detail so that I can try and assist you further. Also, which version of excel are you using? Which version of CVI?

The "Microsoft Excel 9.0 Object Library" instrument has MANY more functions than "Excel Report" so I am hopeful that we can find the functionality you desire here.

[Rajpriya]

Hi David,

      I am using Excel 2000 and CVI 8.5.

  Menu for unfreeze / freeze-------> Window ==> Freeze Panes

   Menu for removing filter / Auto filter-------> Data==> Auto filter

At the same time, I will try with Excel2000.fp

Thanks & Regards,

Rajesh

[Doiron]

Hello Rajesh,

I am not sure about Excel 2000 but I found that the following snippet is the proper procedure for 2003 (and should be 2007 as well):

ExcelRpt_ApplicationNew (VTRUE, &ExcelAppHandle);
ExcelRpt_WorkbookNew(ExcelAppHandle,&WorkBookHandle);
ExcelRpt_GetWorksheetFromIndex (WorkBookHandle, 1, &WorkSheetHandle);                
Excel_GetProperty (ExcelAppHandle, NULL,Excel_AppActiveWindow, CAVT_OBJHANDLE, &WindowHandle);
Excel_SetProperty (WindowHandle, NULL, Excel_WindowSplitRow,CAVT_LONG, 5);
Excel_SetProperty (WindowHandle, NULL, Excel_WindowFreezePanes, CAVT_BOOL, VTRUE);

This code is thanks to  a customer (you are awesome Aric!). I believe that the resources he used the most were found here:

 http://support.microsoft.com/default.aspx/kb/q222101/

Unfortunately, in the case of ActiveX often we are limited to the help and what functionality is exposed by a 3rd party. In many cases this means going through 3rd party documentation on functions, classes, etc.

In this case Aric did a great job of this and has provided this snippet for us. Thanks again to Aric for all his hard work!