Creating a new worksheet with Excel addin

[Sankalp]

Hi,

Am trying to create a new worksheet from the excel addin. For some
reason the creation fails. Does the code seem alright or am I missing
something?

Seems like pSheets->Add is failing.

Regards,
Sankalp


///////////Code///////////
const char C_SHEET_NAME[] = "RCO";

long lSheetsCnt = 0;
bool blnNoSheetFound = true;
CComPtr<Excel::Sheets> pSheets;
HRESULT hr = objApp->get_Sheets(&pSheets);

USES_CONVERSION;

if (SUCCEEDED(hr)) {
hr = pSheets->get_Count(&lSheetsCnt);
}

IDispatchPtr pDispSheet;

if (SUCCEEDED(hr)) {
//Check if sheet exists
for (int nIdx = 1; nIdx <= lSheetsCnt; nIdx++) {
hr = pSheets->get_Item(_variant_t(nIdx), &pDispSheet);
BSTR bsTempName;
CComQIPtr<Excel::_Worksheet>pSheet;
if (SUCCEEDED(hr)) {
CComQIPtr<Excel::_Worksheet>pSheet(pDispSheet);
hr = pSheet->get_Name(&bsTempName);
}
if (SUCCEEDED(hr)) {
_bstr_t bsName(bsTempName);
if (bsName == _bstr_t(C_SHEET_NAME)) {
CComQIPtr<Excel::_Worksheet>pSheet(pDispSheet);
(*ppWorkSheet) = pSheet;
blnNoSheetFound = false;
break;
}
}
} //for

if (blnNoSheetFound) {
hr = pSheets->Add(CComVariant(VT_EMPTY), CComVariant(VT_EMPTY),
CComVariant(VT_EMPTY), CComVariant(VT_EMPTY), LOCALE_USER_DEFAULT,
&pDispSheet);
}
if (SUCCEEDED(hr)) {
CComQIPtr<Excel::_Worksheet>pSheet(pDispSheet);
(*ppWorkSheet) = pSheet;

CComBSTR bs(C_SHEET_NAME);
BSTR bsName = bs.Copy();
hr = pSheet->put_Name(bsName);
::SysFreeString(bsName);
}

if (SUCCEEDED(hr)) {
objApp->put_ScreenUpdating(LOCALE_USER_DEFAULT, VARIANT_TRUE);
}
}