search for item in excel file, read the row

[RandySmith]

Hello,

I need to open a spreadsheet file, search for a particular value( part number ), return the line number (column number?), then load that entire row into CVI where I will pick off pertinent items from the row.

I’ve considered reading the entire spreadsheet into a table or some other element, then do the search, however, I’m reluctant due to the spreadsheet size.

Would I load the spreadsheet into a table?  Load it into a structure?

I’m quite new at using C and of course CVI.  Can anyone give me some tips or help on doing this?

Thank you,

Randy

[ebalci]

Hi Randy,

Add the ExcelRpt library to your project. This is for controlling Excel over ActiveX. Actually it is a "lite" version of the full Excel ActiveX library which is cumbersome.

The files for this library are located at: <CVI installation>\toolslib\activex\excel.

You will need excelreport.c and excelreport.fp. I think when you add these to your project Excel2000 library will automatically be loaded too.

In this library there is a ExcelRpt_Find function which finds a specific value in a worksheet and returns them as a cell range string.

You may then use ExcelRpt_ReadDataToTableControl to transfer the row to CVI environment but you will need to modify the cell range string to include all the cells in the row.

This will help you to start. See the demo projects for more detail.

Do not forget to call CA_DiscardObjHandle for all the handles you acquired using the library function.

You can find many posts about people,  including me , complaining about problems with Excel, handles and CA_DiscardObjHandle.

Hope this helps.

Message Edited by ebalci on 06-01-2007 11:22 AM

[Randy]

On Jun 1, 4:40 am, ebalci <x...@no.email> wrote:

Thank you very much for your answer. It is what I want. But, I
cannot make it work properly and I don't know what I'm doing wrong.

Here's my code:
----------------------------------------------------------------------------------------------------------------
Xerror = ExcelRpt_Find (ExcelWorksheetHandle, ExRConst_dataEmpty,
&FindThis, "J2", ExRConst_Values,
ExRConst_Whole, ExRConst_ByColumns,
ExRConst_Next, 0, 1, Csetrange_found);

CA_GetAutomationErrorString (Xerror, ErrorString,sizeof
(ErrorString));
---------------------------------------------------------------------------------------------------------------
I'm getting an error "The parameter is incorrect" in ErrorString. I
don't know how to fix it.
Perhaps my "ExcelWorksheetHandle" is incorrect. If so, I'm not sure
how to correct it.

I have a worksheet in the root of the project called "3A-000000.xls"
that I'm trying to open and find a particular row.
char *path = "D:\\328 Project TE2712\\Get CSET num from excel\
\3A-000000.xls"; is the path I have set up.
The worksheet is called "Sheet2" but am not sure if the sheet name
matters.

How would I get a handle to "Sheet2" in the "3A-000000.xls" workbook?

Again, thank you!!

Randy

[ebalci]

Hi Randy,

The sheet name matters of course.
If you did not use GetWorksheetFromIndex or GetWorksheetFromName to get a handle to your worksheet the find function (or any other function requiring a worksheet handle) won't work.
You can use "Sheet2" in the second function above to get a valid handle.

Besides, I see that you entered ExRConst_dataEmpty in the second parameter and &FindThis in the third.
If you want to find empty cells the search data probably will be ignored and you may get a correct answer, but I really wonder how you defined FindThis in your code.
If it is something like char FindThis[10]; then you should not put an '&' before its name because it would already be a pointer.

Hope this helps.