Problem: How to copy excel sheet in C++

[sgwong]

Hi,

Anyone here know how to use the sheet->copy() function to copy the
sheet in the same workbook using c++. I am writing a software in C++ to
duplicate an active sheet in the same workbook.

I know that in VBA script, the copy function like below:
Worksheets("Sheet1").Copy After:=Worksheets("Sheet3")

Anyone know what is the argument to fill in for the function copy? If I
left the function copy with no argument below, it will copy the sheet
and put it to a new workbook. My intent is to copy the sheet into new
sheet within the same work book. I know how to get the sheet pointer
for the last sheet, but this function cannot take the sheet pointer as
the argument.

The coding below is working fine if I remove the copy function. Anyone
know how to do this?

Below is some of my coding:


Code:
--------------------
#import "progid:Excel.Sheet" auto_search auto_rename rename_search_namespace("Office10")

#include <direct.h> //just to get the current path/directory

int main()
{
using namespace Excel;
_ApplicationPtr pXL;
pXL.CreateInstance(L"Excel.Application");

pXL->Visible[0] = VARIANT_TRUE; // show the excel program

WorkbooksPtr pBooks = pXL->Workbooks;
_WorkbookPtr pBook = pBooks->Open("c:\\myfile2.xls");
_WorksheetPtr pSheet = pXL->ActiveSheet;
SheetsPtr worksheets = pBook->Worksheets;
_WorksheetPtr lastsheet = worksheets->Item[worksheets->Count]; // get the last sheets

pSheet->Name = "sheetA";
RangePtr cell = pSheet->Cells;
cell->Item[3][3] = "test"; //write "test" to cell C3

pSheet->Copy(); // how to fill in the argument?

pBook->Saved[0] = VARIANT_TRUE;

char buff[_MAX_PATH];
if (_getcwd(buff, _MAX_PATH) == NULL) {
printf("Can't Get Current Directory - errno=%d\n", errno);
Exit(-1);
}

sprintf(buff,"%s\\newfile.xls",buff);
pBook->SaveCopyAs(buff,TRUE);
pXL->Quit();

return 0;
}