How can write sheet 2 in xlsx with excel automation?

[vjshankwar]

Hi all,

i m writing a excel sheet with use os excel automation and i m using this code its forking fine.

but the data alwayz write in sheet one ,i want to write in sheet 2 and also want to rename the sheet.

please tell me how can i do this.

thanks in advance.

HRESULT AutoWrap_Exp(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) 
{
    // Begin variable-argument list...
    va_list marker;
    va_start(marker, cArgs);

    if(!pDisp) 
	{
		//MessageBox(NULL, "NULL IDispatch passed to AutoWrap_Exp()", "Error", 0x10010);
        //_exit(0);
		return 0;
    }

    // Variables used...
    DISPPARAMS dp = { NULL, NULL, 0, 0 };
    DISPID dispidNamed = DISPID_PROPERTYPUT;
    DISPID dispID;
    HRESULT hr;
    char buf[200];
    char szName[200];
    
    // Convert down to ANSI
    WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
    
    // Get DISPID for name passed...
    hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
    if(FAILED(hr)) 
	{
        sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
		OutputDebugStringA(buf);
		//MessageBox(NULL, buf, "AutoWrap_Exp()", 0x10010);
        //_exit(0);
        return hr;
    }
    
    // Allocate memory for arguments...
    VARIANT *pArgs = new VARIANT[cArgs+1];
    // Extract arguments...
    for(int i=0; i<cArgs; i++) 
	{
        pArgs[i] = va_arg(marker, VARIANT);
    }
    
    // Build DISPPARAMS
    dp.cArgs = cArgs;
    dp.rgvarg = pArgs;
    
    // Handle special-case for property-puts!
    if(autoType & DISPATCH_PROPERTYPUT) 
	{
        dp.cNamedArgs = 1;
        dp.rgdispidNamedArgs = &dispidNamed;
    }
    
    // Make the call!
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
    if(FAILED(hr)) 
	{
        sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
		OutputDebugStringA(buf);
		//MessageBox(NULL, buf, "AutoWrap_Exp()", 0x10010);
        //_exit(0);
        return hr;
    }
    // End variable-argument section...
    va_end(marker);
    
    delete [] pArgs;
    
    return hr;
}

void CTest_ExcelDlg::OnBnClickedButton2()
{
	try
	{	
		CString OpenFile_Name=_T("E:\\xl.xlsx");
		CWaitCursor cwt;

		DeleteFile(OpenFile_Name);

		BOOL flag=FALSE;

		int nRow=4;
		int nCol=2;

		CString locount;
		locount.Format(_T("%d"),nRow+1);
		CString rowp="";
		
		rowp="A1:B";
		
		rowp+=locount;

		CString column="";
		CString item="";

		// Initialize COM for this thread...
		CoInitialize(NULL);

		// Get CLSID for our server...
		CLSID clsid;
		HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

		if(FAILED(hr)) 
		{
			::MessageBox(NULL, _T("CLSIDFromProgID() failed"), _T("Error"), 0x10010);
			
			//MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;

		}

		// Start server and get IDispatch...
		IDispatch *pXlApp;
		hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
		if(FAILED(hr)) 
		{
			::MessageBox(NULL, _T("Excel not registered properly"), _T("Error"), 0x10010);
			

			//MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;

		}

		// Make it visible (i.e. app.visible = 1)
		{
			VARIANT x;
			x.vt = VT_I4;
			x.lVal = 0;
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
		}

		// Get Workbooks collection
		IDispatch *pXlBooks;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			
			pXlBooks = result.pdispVal;
		}

		// Call Workbooks.Add() to get a new workbook...
		IDispatch *pXlBook;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			
			pXlBook = result.pdispVal;
		}

		// Create a 15x15 safearray of variants...
		VARIANT arr;
		arr.vt = VT_ARRAY | VT_VARIANT ;
		{
			SAFEARRAYBOUND sab[2];
			sab[0].lLbound = 1; sab[0].cElements = nRow+1;
			sab[1].lLbound = 1; sab[1].cElements = nCol;
			arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
		}

		// Fill safearray with some values...
		for(int i=1; i<=nRow+1; i++) 
		{
			for(int j=1; j<=nCol; j++) 
			{
				// Create entry value for (i,j)
				VARIANT tmp;

				if(i==1)
				{	
					if(j==1)
						item=_T("Column 1");
					else
						item=_T("Column 2");
				}
				else
				{
					item.Format("Row :: %d and Column :: %d",i-2,j-1);
				}
						
				tmp.bstrVal = _bstr_t(item);
				tmp.vt = VT_BSTR;

				 //tmp.lVal = i*j;
				 // Add to safearray...
				 long indices[] = {i,j};
				 SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
			}
		}

		// Get ActiveSheet object
		IDispatch *pXlSheet;
		{
			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0)!=S_OK)			
			{
				flag=TRUE;
				goto error;
			}
			
			pXlSheet = result.pdispVal;
		}

		// Get Range object for the Range A1:O15...
		IDispatch *pXlRange;
		{			   
			VARIANT parm;
			parm.vt = VT_BSTR;
			parm.bstrVal = ::SysAllocString(_bstr_t(rowp));

			VARIANT result;
			VariantInit(&result);
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
			VariantClear(&parm);

			pXlRange = result.pdispVal;
		}

		// Set range with our safearray...
		if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr)!=S_OK)
		{
			flag=TRUE;
			goto error;
		}

		// Wait for user...
		//   ::MessageBox(NULL, "All done.", "Notice", 0x10000);

		// Set .Saved property of workbook to TRUE so we aren't prompted
		// to save when we tell Excel to quit...
		{
			VARIANT x;
			x.vt = VT_I4;
			x.lVal = 1;
			
			if(AutoWrap_Exp(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}

			VARIANT result;
			VariantInit(&result);
			VARIANT fname;
			fname.vt = VT_BSTR;
			fname.bstrVal=::SysAllocString(_bstr_t(OpenFile_Name));
			//VARIANT fformat;
			//fformat.vt = VT_I4;
			//fformat.lVal=-4143;
			
			if(AutoWrap_Exp(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname)!=S_OK)
			{
				flag=TRUE;
				goto error;
			}
		}

		// Tell Excel to quit (i.e. App.Quit)
		if(AutoWrap_Exp(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0)!=S_OK)
		{
			flag=TRUE;
			goto error;
		}

error:
		if(flag==TRUE)
		{
			// Tell Excel to quit (i.e. App.Quit)
			AutoWrap_Exp(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);
			{
			}

			// Release references...
			pXlRange->Release();
			pXlSheet->Release();
			pXlBook->Release();
			pXlBooks->Release();
			pXlApp->Release();
			VariantClear(&arr);

			// Uninitialize COM for this thread...
			CoUninitialize();			

			MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
			return ;
		}

		// Release references...
		pXlRange->Release();
		pXlSheet->Release();
		pXlBook->Release();
		pXlBooks->Release();
		pXlApp->Release();
		VariantClear(&arr);

		// Uninitialize COM for this thread...
		CoUninitialize();
	}
	catch(...)
	{
		MessageBox(_T("Error in file."),_T("Message"),MB_ICONINFORMATION);
		return ;
	}

	MessageBox(_T("Done."),_T("Message"),MB_ICONINFORMATION);
}

[Tom Frohman]

You are using the ActiveSheet.
The code below gets the dispatch of sheet item 2.
This code is very old and was done for Excel 2000. I don't know if it will need any changes.

IDispatch *pXlSheets;
{
VARIANT result;
VariantInit(&result);

AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBook, L"Sheets", 0);
pXlSheets = result.pdispVal;
}
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
VARIANT itemn;
itemn.vt = VT_BSTR;
itemn.bstrVal = ::SysAllocString(L"Sheet3");;
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheets, L"Item", 2, itemn);
pXlSheet = result.pdispVal;

}