Saving an Excel file from C++

[gxkendall]

Hi

I am trying to develop an application that creates an Excel file and then saves it. The excellent examples that are on-line demonstrate how to develop such an application and this wll works, in that I can populated the Excell sheet.

But the samples simply set the saved property, so that when Excel exits you are not prompted to save the file (which is great when testing the population side of things)

I have found some code (posted by Tom Frohman) that shows how to do the save. In essence it is (and this is what I am using)

//Save the work book.
{
VARIANT result;
VariantInit(&result);

VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"D:\\output.xls");

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

//VARIANT fformat;
//fformat.vt = VT_I4;
//fformat.lVal=-4143;
//AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 2, fname, fformat);
}


There has been some discussion that this sometimes does not work. In my case (whih is also the problem reported by others), the save initially worked, but now I get the error

Idispatch("SaveAs"=00000785) failed w/err 0X800a0aa8


... and the save never works now (even after reboot).

Can anybody tell me what is wrong?

For information, which might be useful

- The discussion of the web question whether you need to pass fformat to Autowrap. I have tried both ways (see the commented out code above), with the same results.
- Sometimes, when I click OK on the message box from Autowrap, it takes ages to return as if it is doing something (tidying up memoy?), but sometimes it returns immediately.

I should also say, I am not that familiar with this type of programming.

Thanks

G

[Ejaz]

[ Moved Thread ]

[Tom Frohman]

First, I would try to use the SaveAs from the workbook class rather than the worksheet class. I don't know what you called it. Maybe xlBook? rather than xlSheet.

If the first suggestion doesn't work, I would like to see the code that leads up to the save. Maybe zip and attach the file or part of the file to the post.

Does Output.xls exist already? Does someone already have it open?
Of course you should check if the path exists too. Can you manually use saveas from the file menu to save as output.xls.

[gxkendall]

Thanks for the suggestions.

I have tried them. Alas, they do not work.

Here is the code that I use. Sorry it's so long.

Graham

int writeExcel(excelData *ed,  dataTypes_ currentData, int data_size_length_, int data_size_width_) {

	int len_, ctr;
	WCHAR wcSize100[100];
	char buffer [500];


   // 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, "CLSIDFromProgID() failed", "Error", 0x10010);
      return -1;
   }

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

   // Make it visible (i.e. app.visible = 1)
   {

      VARIANT x;
      x.vt = VT_I4;
      x.lVal = 1;
      AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
   }

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

   // Call Workbooks.Add() to get a new workbook...
   IDispatch *pXlBook;
   {
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
      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 = data_size_length_+1;
      sab[1].lLbound = 1; sab[1].cElements = data_size_width_;
      arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
   }

   int excelRow,excelCol;

   // Set up the Headings
   excelRow = 1;
   for(excelCol=1; excelCol <= data_size_width_; excelCol++) {
	   // Create entry value for (i,j)
       VARIANT tmp;
	   tmp.vt = VT_BSTR;
	   switch(excelCol) {
		case 1: tmp.bstrVal = ::SysAllocString(L"ma"); break;
		case 2: tmp.bstrVal = ::SysAllocString(L"pcent"); break;
		case 3: tmp.bstrVal = ::SysAllocString(L"Trader_Better"); break;
		case 4: tmp.bstrVal = ::SysAllocString(L"Wisdom_Better"); break;
		case 5: tmp.bstrVal = ::SysAllocString(L"Best_Trader_Type"); break;
		case 6: tmp.bstrVal = ::SysAllocString(L"its_ma_val"); break;
		case 7: tmp.bstrVal = ::SysAllocString(L"its_pcent_val"); break;
	   } // switch

       // Add to safearray...
       long indices[] = {excelRow,excelCol};
       SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
   } // for excelCol

	excelRow=2, excelCol=1;

   // Fill safearray with some values...
   for(int i=0; i < data_size_length_; i++) {
      for(excelCol=1; excelCol <= data_size_width_; excelCol++) {
         // Create entry value for (i,j)
       VARIANT tmp;
	   switch(excelCol) {
		case 1: tmp.vt = VT_I2;   tmp.iVal      = ed[i].ma_;                       break;
		case 2: tmp.vt = VT_R8;   tmp.dblVal    = ed[i].pcent_;                    break;
		case 3: tmp.vt = VT_I2;   tmp.iVal      = ed[i].trader_better;             break;
		case 4: tmp.vt = VT_I2;   tmp.iVal      = ed[i].wisdom_better;             break;
		case 5: tmp.vt = VT_BSTR;
			len_ = ed[i].trader.length()+1; // +1 to get the terminating char
			for(ctr = 0; ctr < len_; ctr++) wcSize100[ctr] =  ed[i].trader[ctr];
			tmp.bstrVal   = ::SysAllocString(wcSize100);
			                                                                       break;
		case 6: tmp.vt = VT_I2;   tmp.iVal      = ed[i].ma_val;                    break;
		case 7: tmp.vt = VT_R8;   tmp.dblVal    = ed[i].pcent_val;                 break;
	   } // switch

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



   // Get ActiveSheet object
   IDispatch *pXlSheet;
   {
      VARIANT result;
      VariantInit(&result);
      AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
      pXlSheet = result.pdispVal;
   }

   // Get Range object for the Range A1:O15...
   IDispatch *pXlRange;
   {
      VARIANT parm;
      parm.vt = VT_BSTR;

	   // Code to set the range
		//n=sprintf (buffer, "%d plus %d is %d", a, b, a+b);
		len_=sprintf(buffer, "A1:G%d", data_size_length_+1); // +1 to allow for header row
		len_++;

		for(ctr = 0; ctr < len_; ctr++) wcSize100[ctr] =  buffer[ctr];
		parm.bstrVal   = ::SysAllocString(wcSize100);
   	   // END: Code to set the range

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

      pXlRange = result.pdispVal;
   }

   // Set range with our safearray...
   AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr);

   // 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;
      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);

	//Save the work book.
	{
	VARIANT result;
	VariantInit(&result);
	VARIANT fname;
	fname.vt = VT_BSTR;
	fname.bstrVal=::SysAllocString(L"D:\\output.xls");
	//VARIANT fformat;
	//fformat.vt = VT_I4;
	//fformat.lVal=-4143;
	AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname);
}



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

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

   return 0;
} // writeExcel

[Tom Frohman]

It looks to me like you are trying to do the SaveAs after Quit.

You need to do the SaveAs before Quit. Excel can't save the file after it has shut down.

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

//Save the work book.
{
VARIANT result;
VariantInit(&result);
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal=::SysAllocString(L"D:\\output.xls");
//VARIANT fformat;
//fformat.vt = VT_I4;
//fformat.lVal=-4143;
AutoWrap(DISPATCH_METHOD, &result, pXlSheet, L"SaveAs", 1, fname);
}

[gxkendall]

Doh! - Thanks a lot - is working now.

G