Rather than indexes into the Sheets collection, you need to supply the function valid worksheet objects.
Firing up a new instance of excel and recording 2 macros, we get the following code:
1. Sheet added between Sheet2 & Sheet3 (right-click sheet 3, add new worksheet)
Sheets("Sheet3").Select
Sheets.Add
2. Sheet added at the end, after all sheets
Sheets.Add After:=Sheets(Sheets.Count)
A c++ snippet I just bashed together that achieves #2:
(remembering that we have to pass the args in reverse order for COM calls)
IDispatch *pXlBooks;
pXlBooks = getIDispatchVal(pXlApp, L"WorkBooks");
IDispatch *pXlBook;
pXlBook = getIDispatchVal(pXlBooks, L"Add");
IDispatch *pXlSheets;
pXlSheets = getIDispatchVal(pXlBook, L"Worksheets");
int curBookSheetCount = getCount(pXlSheets);
IDispatch *aftrSheet = getItem(pXlSheets, curBookSheetCount);
IDispatch *pNewSheet;
{
VARIANT result, var1,var2;
VariantInit(&result);
var1.pdispVal = NULL;
var1.vt = VT_NULL;
var2.pdispVal = aftrSheet;
var2.vt = VT_DISPATCH;
AutoWrap(DISPATCH_METHOD, &result, pXlSheets, L"Add", 2, var2, var1);
pNewSheet = result.pdispVal;
}
A couple of my helper functions:
int getCount(IDispatch *pCollection)
{
VARIANT result;
VariantInit(&result);
result.pdispVal = NULL;
AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Count", 0);
return result.lVal;
}
IDispatch *getItem(IDispatch *pCollection, int index)
{
VARIANT result, param1;
VariantInit(&result);
result.pdispVal = NULL;
param1.vt = VT_I4;
param1.lVal = index;
AutoWrap(DISPATCH_PROPERTYGET, &result, pCollection, L"Item", 1, param1);
return(result.pdispVal);
}
IDispatch *getIDispatchVal(IDispatch *pObject, wchar_t *valName)
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pObject, valName, 0);
return result.pdispVal;
}