当用户更改工作表时，SetValue（）函数抛出异常 [英] SetValue() function throws exception when user changes sheets
问题描述
在Windows XP和自动化上使用Visual C ++ 6将数据发送到Excel 2007. 在我的软件中，我偶尔会重复打开一个Excel文件，写入第1张，然后关闭而不保存。 但是，如果用户单击另一个
工作表，当我的代码到达SetValue（）时，它会抛出COleDispatchException。 错误的描述是一个空白字符串。 为什么Excel不继续写入原始工作表？ 我怎样才能实现这一目标？ 一些代码：
//工作表包装类
CXLWorksheet工作表;
//范围包装类
CXLRange范围;
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是Excel应用程序类
m_XLApp.AttachDispatch（pXLAppDispatch）;
lpUnk-> Release（）;
}
}
V_BOOL（& vBOOL）= TRUE;
m_XLApp.SetVisible（vBOOL）;
VARIANT vFilename;
V_VT（& vFilename）= VT_BSTR;
// strFileName是Excel文件名的完整路径
int Len = strFileName.GetLength（）;
V_BSTR（& vFilename）= SysAllocString（
T2COLE（strFileName.GetBuffer（Len + 1）））;
// m_workbooks是工作簿包装类
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）
{
// *创建一个新的
vRet = m_workbooks.Add（vNotPassed）;
}
END_CATCH_ALL
// m_workbook是工作簿包装类
如果（V_VT（& vRet）== VT_DISPATCH）
m_workbook.AttachDispatch（V_DISPATCH（& vRet））;
}
LPDISPATCH lpDisp;
//对于此示例，nXLWorksheetNo == 1
lpDisp = m_workbook.GetWorksheets（nXLWorksheetNo）;
worksheet.AttachDispatch（lpDisp）;
//在这个例子中，nCols == 10
for （ int nCol = 0; nCol< nCols; nCol ++）
{
// *一次处理整个列
// *添加列（字段）标题
// strRange ==" A1"
strRange = pTable-> m_strXLTopLeftCell;
XLAdjustCol（strRange，nCol）;
V_VT（& v）= VT_BSTR;
V_BSTR（& v）= SysAllocString（
T2COLE（strRange.GetBuffer（0）））;
LPDISPATCH pDisp = worksheet.Range1（v）;
VariantClear（& v）;
range.AttachDispatch（pDisp）;
VariantInit（& v）;
V_VT（& v）= VT_BSTR;
//在这个例子中，strHeader是"nVar_VSL"。 （对bug无意义）
V_BSTR（& v）= strHeader.AllocSysString（）;
if （V_VT（& v）！= VT_EMPTY）
{
尝试
{
//这里是错误的地方
//如果用户更改为另一个工作表，则范围抛出异常
//为什么不继续写入当前工作表？
range.SetValue（v）;
}
catch （COleDispatchException * pEx）
{
//当nCol == 0或8
TRACE时，大部分时间崩溃（ nCol =％d \ n" ，NCOL）;
pEx->删除（）;
}
VariantClear（& v）;
}
当您打开工作簿时，将工作表对象设置为感兴趣的工作表，然后在引用范围时确保使用工作表对象指定范围。 在VBA中，代码将是
设置objXLSheet = objWorkbook.WorkSheets（shtIndex）
然后范围是工作表对象的属性： ; objXLSheet.Range（.....）
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 sheet 1, 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? Some code:
//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 == 10 for( int nCol=0; nCol<nCols; nCol++ ) { //* Work on an entire column at a time //* Add Column (field) title //strRange == "A1" strRange = pTable->m_strXLTopLeftCell; XLAdjustCol(strRange, nCol); V_VT(&v) = VT_BSTR; V_BSTR(&v) = SysAllocString( T2COLE(strRange.GetBuffer(0)) ); LPDISPATCH pDisp = worksheet.Range1(v); VariantClear(&v); range.AttachDispatch(pDisp); VariantInit(&v); V_VT(&v) = VT_BSTR; //in this example, strHeader is "nVar_VSL" (no significance to bug) V_BSTR(&v) = strHeader.AllocSysString(); if( V_VT(&v) != VT_EMPTY ) { try { //here is where the bug is //range throws exception if user changes to another sheet //why doesn't it just keep writing to current sheet? range.SetValue(v); } catch(COleDispatchException *pEx) { //most of the time crashes when nCol==0 or 8 TRACE("nCol = %d\n",nCol); pEx->Delete(); } VariantClear(&v); }
When you open the workbook, set a worksheet object to the sheet of interest, and then when you refer to the range make sure that you use the worksheet object to specify the range. In VBA, the code would be
Set objXLSheet = objWorkbook.WorkSheets(shtIndex)
Then the range is a property fo the sheet object: objXLSheet.Range(.....)
这篇关于当用户更改工作表时，SetValue（）函数抛出异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
