Working with Excel - How to switch worksheet?

This is a discussion on Working with Excel - How to switch worksheet? within the C++ Programming forums, part of the General Programming Boards category; Hi, I had this thread a while back in which I got help with various problems I was having controlling ...

  1. #1
    Registered User
    Join Date
    May 2012
    Posts
    10

    Working with Excel - How to switch worksheet?

    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:

    Code:
    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();
    }

  2. #2
    Registered User rogster001's Avatar
    Join Date
    Aug 2006
    Location
    Liverpool UK
    Posts
    1,406
    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.
    Last edited by rogster001; 08-28-2012 at 10:21 AM.
    Thought for the day:
    "Are you sure your sanity chip is fully screwed in sir?" (Kryten)
    FLTK: "The most fun you can have with your clothes on."

    Stroustrup:
    "If I had thought of it and had some marketing sense every computer and just about any gadget would have had a little 'C++ Inside' sticker on it'"

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Working with multiple excel files
    By SBK in forum Tech Board
    Replies: 5
    Last Post: 05-22-2012, 12:50 AM
  2. switch loop not working
    By dakarn in forum C Programming
    Replies: 11
    Last Post: 10-29-2008, 12:54 PM
  3. case switch not working
    By AmbliKai in forum C Programming
    Replies: 2
    Last Post: 10-09-2008, 06:42 AM
  4. why isnt my switch working
    By Unregistered in forum C Programming
    Replies: 4
    Last Post: 05-21-2002, 12:08 PM
  5. switch not working
    By Unregistered in forum C++ Programming
    Replies: 5
    Last Post: 01-22-2002, 06:25 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21