how to auto save excel file
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 12 of 12

Thread: how to auto save excel file

  1. #1
    Join Date
    Sep 2012
    Posts
    7

    how to auto save excel file

    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.

  2. #2
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,352

    Re: how to auto save excel file

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

  3. #3
    ovidiucucu's Avatar
    ovidiucucu is offline Moderator/Reviewer Power Poster
    Join Date
    Feb 2003
    Location
    Iasi - Romania
    Posts
    8,021

    Re: how to auto save excel file

    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".
    Ovidiu Cucu
    "When in Rome, do as Romans do."
    Visit: Microsoft Virtual Academy
    Follow: https://twitter.com/#!/ovidiucucu
    My blog: http://codexpert.ro/blog/author/ovidiu-cucu/

  4. #4
    Join Date
    Sep 2012
    Posts
    7

    Re: how to auto save excel file

    //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.

  5. #5
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,352

    Re: how to auto save excel file

    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
      Code:
      "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx"
    Victor Nijegorodov

  6. #6
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: how to auto save excel file

    Code:
    //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

    Code:
    AutoWrap(DISPATCH_METHOD, NULL, pXlSheet, L"SaveAS", 1, fname);
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

  7. #7
    Join Date
    Sep 2012
    Posts
    7

    Re: how to auto save excel file

    Code:
    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

  8. #8
    Join Date
    Sep 2012
    Posts
    7

    Re: how to auto save excel file

    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.

  9. #9
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,352

    Re: how to auto save excel file

    Quote Originally Posted by modirizwanmodi View Post
    Dear Victor,

    i am unable to identify where exactly error is coming.
    Didn't you debug your code?
    Victor Nijegorodov

  10. #10
    Join Date
    Sep 2012
    Posts
    7

    Re: how to auto save excel file

    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.

  11. #11
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,352

    Re: how to auto save excel file

    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...
    Victor Nijegorodov

  12. #12
    Join Date
    Apr 2002
    Location
    Michigan, USA
    Posts
    862

    Re: how to auto save excel file

    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


    Code:
    	 
    {
    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:
    Code:
    VARIANT x;
    x.vt=VT_BOOL;
    x.boolVal=TRUE;
    AutoWrap(DISPATCH_PROPERTYPUT, &result, pXlBook, L"Saved", 1,x);
    Last edited by Tom Frohman; September 20th, 2012 at 07:17 AM.
    Verere testudinem! (Fear the turtle)

    Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy. -Albert Einstein

    Robots are trying to steal my luggage.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center