Read a specific sheet in a excel document...Please help!

[Annees]

First I start Excel then I read a specific excel document which has 5 sheet`s and the 1 sheet is active:

GetProjectDir (fileName);

strcat(fileName, "\\Logiciel_FH.xls");

error = Excel_WorkbooksOpen (ExcelWorkbooksHandle, NULL, fileName, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, &ExcelWorkbookHandle);

// Get Active Workbook Sheets

error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets,

CAVT_OBJHANDLE, &ExcelSheetsHandle);

error = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(1), &ExcelWorksheetHandle);

// Make First Sheet Active - should already be active

error = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);

After I write some data in this sheet and start a macro function I want to read the sheet number 4....so I tried to do this:(but still it reads from the 1 sheet.)

error = CA_VariantSetCString (&MyCellRangeV, EXCEL_ARRAY_OF_CELLS);

error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);

CA_VariantClear(&MyCellRangeV);

error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets,

CAVT_OBJHANDLE, &ExcelSheetsHandle);

error = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(4), &ExcelWorksheetHandle);

// Make First Sheet Active - should already be active

error = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);

// Make range Active

error = Excel_RangeActivate (ExcelRangeHandle, &ErrorInfo, NULL);

// Ask for the ith by jth value of the range which returns a dispatch to a new single cell range

error = Excel_RangeGetItem (ExcelRangeHandle, &ErrorInfo, CA_VariantInt (12), CA_VariantInt (2), &MyVariant);

// Get the DISPATCH pointer

error = CA_VariantGetDispatch (&MyVariant, &MyDispatch);

// Create a new Range Object from DISPATCH pointer

error = CA_CreateObjHandleFromIDispatch (MyDispatch, 0, &ExcelSingleCellRangeHandle);

// Get the value of the Single Cell Range

error = Excel_GetProperty (ExcelSingleCellRangeHandle, &ErrorInfo, Excel_RangeValue2, CAVT_VARIANT, &MyVariant);

error = CA_VariantGetCString (&MyVariant, &d);

printf("%s\n ", d);

CA_VariantClear(&MyVariant);

ClearObjHandle (&ExcelSingleCellRangeHandle);

Can you tell me where I`m doing wrong...?? What should I do to make the program read from the sheet number 4??

Message Edited by Annees on 06-10-2010 03:40 PM

[RobertoBozzolo · Accepted solution]

I have used in the past some code very similar to yours, with one exception which is to close the handle to the active worksheet before acivating a new one. In this case the code should look like the following (note the lines in red):

GetProjectDir (fileName);

strcat(fileName, "\\Logiciel_FH.xls");

error = Excel_WorkbooksOpen (ExcelWorkbooksHandle, NULL, fileName, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, CA_DEFAULT_VAL,

CA_DEFAULT_VAL, &ExcelWorkbookHandle);

// Get Active Workbook Sheets

error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, CAVT_OBJHANDLE, &ExcelSheetsHandle);

// Handle to the first sheet

error = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(1), &ExcelWorksheetHandle);

// Make First Sheet Active - should already be active

error = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);

error = CA_VariantSetCString (&MyCellRangeV, EXCEL_ARRAY_OF_CELLS);

error = Excel_WorksheetRange (ExcelWorksheetHandle, NULL, MyCellRangeV, CA_DEFAULT_VAL, &ExcelRangeHandle);

CA_VariantClear(&MyCellRangeV);

// Clear the handle to the active worksheet

error = ClearObjHandle (&ExcelWorksheetHandle);

//error = Excel_GetProperty (ExcelAppHandle, NULL, Excel_AppSheets, CAVT_OBJHANDLE, &ExcelSheetsHandle);

// Not needed: you have not overwritten ExcelSheetsHandle variable which is still valid

// Handle to the fourth sheet

error = Excel_SheetsItem (ExcelSheetsHandle, NULL, CA_VariantInt(4), &ExcelWorksheetHandle);

// Make Sheet Active

error = Excel_WorksheetActivate (ExcelWorksheetHandle, NULL);

// Make range Active

error = Excel_RangeActivate (ExcelRangeHandle, &ErrorInfo, NULL);

// Ask for the ith by jth value of the range which returns a dispatch to a new single cell range

error = Excel_RangeGetItem (ExcelRangeHandle, &ErrorInfo, CA_VariantInt (12), CA_VariantInt (2), &MyVariant);

// Get the DISPATCH pointer

error = CA_VariantGetDispatch (&MyVariant, &MyDispatch);

// Create a new Range Object from DISPATCH pointer

error = CA_CreateObjHandleFromIDispatch (MyDispatch, 0, &ExcelSingleCellRangeHandle);

// Get the value of the Single Cell Range

error = Excel_GetProperty (ExcelSingleCellRangeHandle, &ErrorInfo, Excel_RangeValue2, CAVT_VARIANT, &MyVariant);

error = CA_VariantGetCString (&MyVariant, &d);

printf("%s\n ", d);

CA_VariantClear(&MyVariant);

ClearObjHandle (&ExcelSingleCellRangeHandle);

As a side note, you are not using error checking and probably have disabled the break on library errors: you should have recevied some error when trying to access the new sheet: consider adding a proper error handling and be sure in this case to clear all active objects before leaving the function.

[JJHGH]

Hello,

Can you give me the project ,it's urgent

I need this method because I added this code in my project  to display in table under cvi

[RobertoBozzolo]

JJHGH,

stop hijacking old threads and read the documentation and examples I link here!