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:

#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;
}