how to auto save excel file

[modirizwanmodi]

HI, i am developing a program where i have to auto save the excel file. i try putting some integer data's in excel and auto save the file it works perfect. while i try to auto save the file with some string value in it. it shows me error.
AutoWrap()-IDispatch::GetIDsOfNames("Saved")failed w/err 0x80020006 - this is the error what i am getting right now. please help me to auto save the excel file while it have string value in it.

[VictorN]

How do you put "some integer data's in excel"?
How do you put "some string value"?
How did you implement AutoSave?

[ovidiucucu]

Each time when you get such type of error code, you can try getting its description by using Error Lookup tool which is shipped with Visual Studio.
E.g. 0x80020006 means "Unknown name".

[modirizwanmodi]

//this is my full code

#undef _UNICODE
#undef UNICODE
#include <iostream>
#include <windows.h>
#include <conio.h>
#include <string.h>


// AutoWrap() - Automation helper function...
HRESULT AutoWrap(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()", "Error", 0x10010);
_exit(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);
MessageBox(NULL, buf, "AutoWrap()", 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);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// End variable-argument section...
va_end(marker);

delete [] pArgs;

return hr;
}

int main(void)
{
// COM object is being intialized -- Haroon
// Initialize COM for this thread...
CoInitialize(NULL);

//get class ID of Excel -- Haroon
// 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;
}

// get IDispatch -- Haroon
// 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;
}


// Excel visible -- Haroon
// 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);
}

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

// new workbook create -- Haroon
// 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;
}

// creating array -- Haroon
// Create a safearray of variants...
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = 10;
sab[1].lLbound = 1; sab[1].cElements = 1;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
}

for(int j=1; j<=3; j++)
{
BSTR b;
VARIANT parm1;
b = SysAllocString(L"Enter the Command Values:");
parm1.vt = VT_BSTR;
parm1.bstrVal = b;
// Add to safearray...
long indices[] = {1,1};
SafeArrayPutElement(arr.parray, indices, &parm1);
}


int k=2;
int i=0;
WCHAR *hrs[]={L"Native Class",L"Caption",L"Height",L"Width",L"Window ID",L"Window Style"};
for(int j=1; j<=6; j++) {
// Create entry value for (i,j)
BSTR b;
VARIANT parm1;
b = SysAllocString(hrs[i]);
i++;
parm1.vt = VT_BSTR;
parm1.bstrVal = b;
// Add to safearray...
long indices[] = {k,1};
k++;
SafeArrayPutElement(arr.parray, indices, &parm1);
}

// Get current sheet -- Haroon
// 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;
parm.bstrVal = ::SysAllocString(L"A1:A8");
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);

//Save the work book.
{
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal =::SysAllocString(L"D:\\output3.xls");
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlSheet, L"Saved", 1, fname);
}

// Quit Excel
AutoWrap(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();
}

currently i am facing problem in saving the excel file. i can able to write and read but not able to auto-save the excel file. please help me.

[VictorN]

First of all you have to properly format your code using Code tags. Otherwise your code is absolutely unreadable!
Second, the error you got is DISP_E_UNKNOWNNAME. According to MSDN article IDispatch::GetIDsOfNames method:

DISP_E_UNKNOWNNAME

One or more of the specified names were not known. The returned array of DISPIDs contains DISPID_UNKNOWN for each entry that corresponds to an unknown name.

So what you now have to do is debug your code step-by-step to see what parameters you pass in the GetIDsOfNames trying to "save" the excel sheet.

Besides, What a strange design to #undefine UNICODE (and _UNICODE) but use everywhere functions that require UNICODE parameters? And why do you need then convert UNICODE to MBCS instead of just use MessageBoxW API?
And BTW, your buffer length for:

1. szName is only 200 while you pass the ptName array of the size 256
2. bufis only 200 while it must be at least the length of szName i plus the length of the formatted text

   "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx"

[Tom Frohman]

//Save the work book.
{
VARIANT fname;
fname.vt = VT_BSTR;
fname.bstrVal =::SysAllocString(L"D:\\output3.xls");
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlSheet, L"Saved", 1, fname);
}

I think the AutoWrap should be

AutoWrap(DISPATCH_METHOD, NULL, pXlSheet, L"SaveAS", 1, fname);

[modirizwanmodi]

AutoWrap(DISPATCH_METHOD, NULL, pXlSheet, L"SaveAS", 1, fname);

this code work fine but the issue is i am updating the excel file regularly and each time the excel file asking me saveas. i want the excel file is autosaved while i am closing the excel file. plz help

[modirizwanmodi]

Dear Victor,

i am unable to identify where exactly error is coming. could you please help me with little briefly reply. so that i can finish the program.

[VictorN]

Dear Victor,

i am unable to identify where exactly error is coming.

Didn't you debug your code?

[modirizwanmodi]

Actually i am new to this and i have taken code from net. yes i debug the code but i am not clear where the error is .. as u said the szname, buf size is less(200) compare to the given size(256). i try changing the size of the szName and buf still the error coming. i try to find out what is there in the ptName. could you plz help me.

[VictorN]

Set a break point in the begin of the AutoWrap function, press F5 to start debugging and goto the break point.
Then use F10 to execute code step-by-step and look at the debugger windows (Autos or Watch) to see the current values of the variables...

[Tom Frohman]

What I think you are asking is how do I stop excel from prompting during the save operation?
You need to set DisplayAlerts to False with

	 
{
VARIANT x;
x.vt=VT_BOOL;
x.boolVal=FALSE;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"DisplayAlerts", 1, x);
}

Do this just before you do the SaveAs and then right afterwards you might want to set DisplayAlerts back to TRUE
just after the SaveAs Operation.



If that doesn't fully work, you will need to set the Saved property of the pXLBook to TRUE before saving.
I haven't tried it but I think it would be of the form:

VARIANT x;
x.vt=VT_BOOL;
x.boolVal=TRUE;
AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlBook, L"Saved", 1,x);