Working with Excel - How to switch worksheet?

[chopshardiman]

Hi,

I had this thread a while back in which I got help with various problems I was having controlling Excel through C++:

Sending Data to Excel with C++

With the help I got in that thread I was able to create a program which reads a log file, parses it, sends it to an excel template file in an appropriate format (i.e. it fills in the desired spaces in the template), some charts get auto-generated and the spreadsheet gets auto-saved with a timestamped filename.

I've got a couple of different versions of this working at the moment, parsing the log files for different bits of info and sending them to the appropriate templates, but what I'm looking to do now is a summary type report, i.e. put all the templates together in one workbook with a different template on each worksheet and fill them in one by one.

I have it working to the point where I can do iterations of the parsing and copying the data to excel, but each iteration opens a new workbook entirely.

I can't for the life of me figure out how to just get excel to change worksheet! Anyone got any idea?

I'll include my Excel code in case it's of any use, but it's just slight variations on the standard MSDN autowrap stuff:

void mainExcel(int err, BSTR bTemplate, BSTR range, VARIANT pSA, BSTR bOutput, int switchFlag){		
	IDispatch *pXlApp;
	IDispatch *pXlBooks;
	IDispatch *pXlBook;
	IDispatch *pXlSheet;


	//Opening Excel starts now
	cout << endl;
	cout << "Initialising COM..." << endl;
	CoInitialize ( NULL );


	// Get CLSID for Excel
	CLSID clsid;


	cout << "Obtaining Excel CLSID from application name..." << endl;
	HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);


	if(FAILED(hr))
	{ 
		cout << "CLSIDFromProgID() failed - Error" << endl;
		err = 1;
		return;
	}


	// Start server and get IDispatch...
	cout << "Creating instance of Excel using IDispatch..." << endl;


	hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);


	if(FAILED(hr)) {
		cout << "Excel not registered properly - Error" << endl;
		err = 1;
		return;
	}


	// Make it visible (i.e. app.visible = 1)
	cout << "Making instance of Excel visible..." << endl;
	{
		VARIANT x;
		x.vt = VT_I4;
		x.lVal = 1;
		AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
	}


	// Get Workbooks collection
	{
		VARIANT result;
		VariantInit(&result);
		AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
		pXlBooks = result.pdispVal;
	}


	// Call Workbooks.Open() to open an existing workbook...
	cout << "Opening workbook..." << endl;
	{
		VARIANT result;
		VariantInit(&result);
		VARIANT fname;
		fname.vt = VT_BSTR;
		if(switchFlag == 1)
		{
			fname.bstrVal = bOutput;
		} 
		else
		{
			fname.bstrVal = bTemplate;
		}


		AutoWrap(DISPATCH_METHOD, &result, pXlBooks, L"Open", 1, fname);
		pXlBook = result.pdispVal;
	}


	// Get ActiveSheet object
	cout << "Getting Active Sheet..." << endl;
	{
		VARIANT result;
		VariantInit(&result);
		AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
		pXlSheet = result.pdispVal;
	}


	//Changing the ActiveSheet
	if(switchFlag == 1)
	{
		{
			VARIANT result;
			VariantInit(&result);
			VARIANT itemn;
			itemn.vt = VT_I4;
			itemn.lVal = 1;
			AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Item", 1, itemn);
			pXlSheet = result.pdispVal;
		}


		{
			VARIANT result;
			VariantInit(&result);
			AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"Activate", 0);
		}
	}


	// Get Range object...
	cout << "Getting Range Object..." << endl;
	IDispatch *pXlRange;
	{
		VARIANT parm;
		parm.vt = VT_BSTR;
		parm.bstrVal = range;


		VARIANT result;
		VariantInit(&result);
		AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
		VariantClear(&parm);


		pXlRange = result.pdispVal;
	}


	// Set range with our safearray...
	cout << "Setting Range..." << endl;
	AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, pSA);


	// Save workbook
	wcout << "Saving " << bOutput << "..." << endl;
	{
		VARIANT result;
		VariantInit(&result);
		VARIANT fname;
		fname.vt = VT_BSTR;
		fname.bstrVal = bOutput;


		AutoWrap(DISPATCH_METHOD, &result, pXlBook, L"SaveAs", 1, fname);
	}


	// 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;
		AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
	}


	//Tell Excel to quit (i.e. App.Quit)
	//AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0);


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


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

[rogster001]

I think I would just be using excel VBA to accomplish this whole thing, unless it does so much work that it takes an age to complete. though if this feature is just a small part of a bigger app that has nothing to do with excel except as one of the export options say then i suppose that makes sense

To change sheets in VBA you can get them by their index or name in the active workbook, then activate, then select
so if you had named a sheet (its real name, not label i mean) dataOutput then you could say dataOutput.activate then dataOutput.range("A1").select - so maybe this can give you an idea how to use your methods or interface in this wrapper or whatever it is allow you to access the worksheet object members in this way i dunno.