Excel automation: SetValue() function throws exception when user changes sheets

garyflet

Using Visual C++ 6 on Windows XP and automation to send data to Excel 2007. With my software there are occasions in which I repeatedly open an Excel file, write to a specified sheet, then close without saving. However, if the user clicks on a different sheet, when my code gets to SetValue(), it throws a COleDispatchException. The description of the error is a blank string. Why doesn't Excel just keep writing to the original sheet? How can I make that happen? A less desirable workaround would be to somehow disable Excel while my code is writing to Excel. Making Excel invisible while writing is not acceptable. Incidentally, there was no problem with Excel 2000.

//worksheet wrapper class
CXLWorksheet worksheet;
//range wrapper class
CXLRange range;
CString strRange;

VARIANT v, vRet, vNotPassed, vBOOL;
VariantInit(&v);
VariantInit(&vRet);
VariantInit(&vNotPassed);
VariantInit(&vBOOL);
V_VT(&vNotPassed) = VT_ERROR;
V_ERROR(&vNotPassed) = DISP_E_PARAMNOTFOUND;
V_VT(&vBOOL) = VT_BOOL;

LPDISPATCH pXLAppDispatch = NULL;
LPUNKNOWN lpUnk; CLSID clsid; 
if( S\_OK == ::CLSIDFromProgID(L"Excel.Application", &clsid) )
{
	if( S\_OK == ::GetActiveObject(clsid, NULL, &lpUnk) )
	{
		VERIFY(lpUnk->QueryInterface(IID\_IDispatch, (void\*\*)&pXLAppDispatch) == S\_OK);
		//m\_XLApp is Excel application class
		m\_XLApp.AttachDispatch( pXLAppDispatch );
		lpUnk->Release();
	}
}


V\_BOOL(&vBOOL) = TRUE;
m\_XLApp.SetVisible(vBOOL);


VARIANT vFilename; 
V\_VT(&vFilename) = VT\_BSTR;

//strFileName is full path of Excel file name
int Len = strFileName.GetLength();
V_BSTR(&vFilename) = SysAllocString(
T2COLE(strFileName.GetBuffer(Len+1)) );

//m_workbooks is workbooks wrapper class
if( !m_workbooks.m_lpDispatch )
{
m_workbooks.AttachDispatch( m_XLApp.GetWorkbooks() );

	TRY
	{
	vRet = m\_workbooks.Open(vFilename, 
		vNotPassed,vNotPassed,vNotPassed,vNotPassed,
		vNotPassed,vNotPassed,vNotPassed,vNotPassed,
		vNotPassed,vNotPassed,vNotPassed );
	}
	CATCH\_ALL(e)
	{
		//\* Create a new one instead
		vRet = m\_workbooks.Add(vNotPassed);
	}
	END\_CATCH\_ALL

//m\_workbook is workbook wrapper class
	if( V\_VT(&vRet) == VT\_DISPATCH )
		m\_workbook.AttachDispatch( V\_DISPATCH(&vRet) );
}

LPDISPATCH lpDisp;
//for this example, nXLWorksheetNo == 1
lpDisp = m\_workbook.GetWorksheets(nXLWorksheetNo);
worksheet.AttachDispatch(lpDisp);

//in this example, nCols ==