I am need to create an exact duplicate of an excel worksheet in the same workbook.

[IronGolem411]

I need to copy an existing worksheet in the same workbook.
I am trying to use a function provided by NI in excel2000.c:

HRESULT CVIFUNC Excel_WorksheetCopy (CAObjHandle objectHandle,
ERRORINFO *errorInfo, VARIANT before,
VARIANT after)
{
HRESULT __result;
unsigned int __paramTypes[] = {CAVT_VARIANT, CAVT_VARIANT};

__result = CA_MethodInvoke (objectHandle, errorInfo, 0x227, CAVT_EMPTY,
NULL, 2, __paramTypes, before, after);

return __result;
}

...but it is copying the sheet to a new workbook. So i am either using the wrong function , or I am using this fu
nction incorrectly. SO if someone could point out the right function, or how to use this function proeperly, it would be apprecited.

[bilalD]

hello

This will happen if you leave both the "before" and after" parameters as CA_DEFAULT_VAL. You need to pass the handle to one of the sheets as follows, convert the objHandle to variant, and pass that to the "before" or "after" parameters, but not both


error1 = CA_VariantSetObjHandle (&sheet1, ExcelWorksheetHandle, CAVT_DISPATCH);

error1 = Excel_WorksheetCopy (ExcelWorksheetHandle, NULL,CA_DEFAULT_VAL, sheet1);

This should do the trick

Bilal Durrani
NI

[IronGolem411]

Thank you for your response. I think that will do the job. However, i can't seem to locate the CA_VariantSetObjHandle function. There is a CA_VariantGetObjHandle and a CA_VariantHasObjHandle though. I am running CVI v5.5. Your continued assistance would be appreciated.

Thanks

[bilalD]

Open up a C file,press CTRL+Shift+P and search for that function, it should be there in the activeX library under Variant related Functions >> Assigning values to variants

Bilal

[IronGolem411]

I have searched for CA_VariantSetObjHandle using the method you prescribed, and have come up empty. CA_VariantGetObjHandle & CA_VariantHasObjHandle both turn up in the search.

[bilalD]

Its definetly not there in CVI 5.5, I think this was added when some more activex features were introduced in CVI 6. But try the following:

//convert handle to dispatch
error1 = CA_GetDispatchFromObjHandle(ExcelWorksheetHandle, &testing1);

//Convert disptach to variant
error1 = CA_VariantSetDispatch (&sheet1, testing1);

It should have the same effect, just not as straightforward.

[IronGolem411]

That did the trick.
Thank you for your assistance.
Its greatly appreciated

[acni]

I need to make a copy of the worksheet so I can use for pasting to another workbook (or to the same worksheet) in the same Excel application.  

But, I am stuck with the following code.  I can't seem to get a copy of the worksheet.  Could you please show me how you did it or

check what I did wrong.  Below are my code. 

if (FileSelectPopup ("", "Sheet1.xls", "Sheet1.xls", "", VAL_LOAD_BUTTON, 0, 0, 1, 0, excelFileName))

{ 

  SetCtrlAttribute (panelHandle, PANEL_CLOSEDATABUTTON, ATTR_DIMMED, 0); 

  SetCtrlAttribute (panelHandle, PANEL_OPENDATABUTTON, ATTR_DIMMED, 1);
  //Create Excel.Application instance  

  errStatus = Excel_NewApp (NULL, 1, LOCALE_NEUTRAL, 0, &excelAppHandle);
  //Make Excel visible 

  errStatus = Excel_SetProperty (excelAppHandle, NULL, Excel_AppVisible, CAVT_BOOL,   VTRUE);
  //Do not display alert when document is closed without saving 

  errStatus = Excel_SetProperty (excelAppHandle, NULL, Excel_AppDisplayAlerts,   CAVT_BOOL, VFALSE);
  //Acess Work books handle and open the selected file.  

  errStatus = Excel_GetProperty (excelAppHandle, NULL, Excel_AppWorkbooks, CAVT_OBJHANDLE,  &excelWorkbooksHandle);                   
  errStatus = Excel_WorkbooksOpen (excelWorkbooksHandle, NULL, excelFileName,

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,&excelWorkSheetHandle); 

  //Copy WorkSheet 

  errStatus = CA_VariantSetObjHandle (&Sheet1, excelWorkSheetHandle, CAVT_DISPATCH); 

  errStatus = Excel_WorksheetCopy (excelWorkSheetHandle, NULL,CA_DEFAULT_VAL, Sheet1);
  //Discard unwanted handle  CA_DiscardObjHandle (excelWorkSheetHandle); 

}

Thanks,

[blakney]

ACNI

You will probably want to start a new thread. The original thread is 7 + years old. Old threads usually don't get as much attention even though they get bumped to the top of the forum. Always make a new post for a different problem (even if related to this topic). My 2 cents.

Greg