none
Automation in excel

    Question

  • Hello !
    I work on VS 2005 and I use this sample to my application http://support.microsoft.com/kb/q216686/ . I use automation to control excel WITHOUT mfc and #import.
    It's works perfectly but I have a big problem : The worksheet is protect in excel

    code VBA
    Code Snippet

    Sheets(1).Protect Password:="XXXXXX", _
    UserInterFaceOnly:=True



    Someone know params to invoke to unprotect the sheet or to execute a macro by automation ?
    I try this unsuccessfully.

    Code Snippet

    bool MyExcel::SetProtect(bool b) {
        VARIANT x;
        x.vt = VT_BSTR;
        x.bstrVal=SysAllocString(L"XXXXXXXX");
        //AutoWrap(DISPATCH_METHOD, NULL, pdispWorksheet, L"Protect", 0);
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pdispWorksheet, L"Protect", 1, x);
      return true;
    }



    Thx.
    Tuesday, September 18, 2007 4:33 PM

Answers

  • This is an extremely tedious way of automating Excel.  Why do you want to do it this way ? Anyway, here is your solution using calls to the AutoWrap function in the KB article.

     

    Code Snippet

     

    int _tmain(int argc, _TCHAR* argv[]){

    VARIANT xlApp = {0};

    VARIANT xlBooks = {0}; // collection of workbooks

    VARIANT xlBook = {0}; // a workbook

    VARIANT xlSheet = {0};

    VARIANT strVariant = {0};

    VARIANT lgVariant = {0};

    VARIANT cellValue = {0};

    OleInitialize(NULL);

    CLSID clsid;

    CLSIDFromProgID(L"Excel.Application", &clsid);

    HRESULT hr = CoCreateInstance(clsid,

                                  NULL,

                                  CLSCTX_LOCAL_SERVER|CLSCTX_INPROC_SERVER,

                                  IID_IDispatch,

                                 (void **)&xlApp.pdispVal);

    if( hr != S_OK) {

      cout<<"cocreateinstance failed."<<endl;

      return 0;

    }

    // get the workbooks object

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlBooks, xlApp.pdispVal, L"Workbooks", 0);

    // open a workbook

    VariantClear(&strVariant);

    strVariant.vt = VT_BSTR;

    strVariant.bstrVal = ::SysAllocString(L"c:\\Test\\Book1.xls");

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlBook, xlBooks.pdispVal, L"Open", 1, strVariant);

    SysFreeString(strVariant.bstrVal);

    VariantClear(&lgVariant);

    // set visible to true

    lgVariant.vt = VT_I4;

    lgVariant.lVal = 1;

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, xlApp.pdispVal, L"Visible", 1, lgVariant);

    // lgVariant can be reused as the required value has not changed

    // get the worksheet object

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlSheet, xlBook.pdispVal, L"Worksheets", 1, lgVariant);

    strVariant.bstrVal = L"password1";

    AutoWrap(DISPATCH_METHOD, NULL, xlSheet.pdispVal, L"Unprotect", 1, strVariant);

    // write some value to a cell to check if unprotect worked

    VariantClear(&cellValue);

    cellValue.vt = VT_BSTR;

    cellValue.bstrVal = L"test";

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, xlSheet.pdispVal, L"Cells", 3, cellValue, lgVariant, lgVariant);

    // protect sheet again with a new password

    strVariant.bstrVal = ::SysAllocString(L"password2");

    AutoWrap(DISPATCH_METHOD, NULL, xlSheet.pdispVal, L"Protect", 1, strVariant);

    SysFreeString(strVariant.bstrVal);

    VariantClear(&xlApp);

    VariantClear(&xlBooks);

    VariantClear(&xlBook);

    VariantClear(&xlSheet);

    OleUninitialize();

    cout<<"ok done"<<endl;

    getch();

    return 0;

    }

     

     

     

    Regards

     

     Sahir Shah

    Sunday, September 23, 2007 9:42 AM

All replies

  • Watch these my posts. There is a better way to automate excel. There you have Properties like in VB. So it will easy to port your code from VB.

     

    #include "stdafx.h"
    #include <iostream>

    #import "progid:Excel.Sheet" auto_search auto_rename no_auto_exclude

    int _tmain(int argc, _TCHAR* argv[])
    {
     ::CoInitialize(NULL);
     {
      using namespace Excel;
      try
      {
       _ApplicationPtr app(__uuidof(Application));
       _WorkbookPtr book = app->Workbooks->Add();
       _WorksheetPtr sheet = book->Sheets->Item[1];
       sheet->Cells->Item[1 ] [ 1] = "Hello";
       for(int i=1; i <= 10; i++)
        sheet->Cells->Item[2 ] [ i] = i;
       
       int val = sheet->Cells->Item[2][3];
       printf("%d\n", val);
       
       app->Save("MyBook.xls");
       app->Quit();
      }
      catch(const _com_error& e)
      {
       wprintf(e.ErrorMessage());
       std::cin.get();
      }
     }
     ::CoUninitialize();
     return 0;
    }


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2045032&SiteID=1

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2146711&SiteID=1

    Tuesday, September 18, 2007 6:00 PM
  • You will get more help in the microsoft.public.excel.programming newsgroup; the online interface is at http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.excel.programming&lang=en&cr=US. There is also an Excel SDK newsgroup and I don't know what it is for.

     

    If there is any way you can use #import then that will make it easier; at least the details will be easier. The KB article you are using links to another article with a lot of help. Even if you continue to not use MFC, #import and not managed code, the other article has some help learning Automation. If you can use managed code solutions then you can use the Visual Studio Tools for Office forum.

     

    Also see How Do I... in Excel and Excel Object Model Reference.

    Tuesday, September 18, 2007 10:31 PM
  • Thanks fot your help but I want't work ONLY with pure api win32 no mfc and no import.The only thing that ican't do is deprotect the sheet.
    I m going to try the microsoft.public.excel.programming newsgroup.
    Wednesday, September 19, 2007 3:20 PM
  • This is an extremely tedious way of automating Excel.  Why do you want to do it this way ? Anyway, here is your solution using calls to the AutoWrap function in the KB article.

     

    Code Snippet

     

    int _tmain(int argc, _TCHAR* argv[]){

    VARIANT xlApp = {0};

    VARIANT xlBooks = {0}; // collection of workbooks

    VARIANT xlBook = {0}; // a workbook

    VARIANT xlSheet = {0};

    VARIANT strVariant = {0};

    VARIANT lgVariant = {0};

    VARIANT cellValue = {0};

    OleInitialize(NULL);

    CLSID clsid;

    CLSIDFromProgID(L"Excel.Application", &clsid);

    HRESULT hr = CoCreateInstance(clsid,

                                  NULL,

                                  CLSCTX_LOCAL_SERVER|CLSCTX_INPROC_SERVER,

                                  IID_IDispatch,

                                 (void **)&xlApp.pdispVal);

    if( hr != S_OK) {

      cout<<"cocreateinstance failed."<<endl;

      return 0;

    }

    // get the workbooks object

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlBooks, xlApp.pdispVal, L"Workbooks", 0);

    // open a workbook

    VariantClear(&strVariant);

    strVariant.vt = VT_BSTR;

    strVariant.bstrVal = ::SysAllocString(L"c:\\Test\\Book1.xls");

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlBook, xlBooks.pdispVal, L"Open", 1, strVariant);

    SysFreeString(strVariant.bstrVal);

    VariantClear(&lgVariant);

    // set visible to true

    lgVariant.vt = VT_I4;

    lgVariant.lVal = 1;

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, xlApp.pdispVal, L"Visible", 1, lgVariant);

    // lgVariant can be reused as the required value has not changed

    // get the worksheet object

    AutoWrap(DISPATCH_PROPERTYGET|DISPATCH_METHOD, &xlSheet, xlBook.pdispVal, L"Worksheets", 1, lgVariant);

    strVariant.bstrVal = L"password1";

    AutoWrap(DISPATCH_METHOD, NULL, xlSheet.pdispVal, L"Unprotect", 1, strVariant);

    // write some value to a cell to check if unprotect worked

    VariantClear(&cellValue);

    cellValue.vt = VT_BSTR;

    cellValue.bstrVal = L"test";

    AutoWrap(DISPATCH_PROPERTYPUT, NULL, xlSheet.pdispVal, L"Cells", 3, cellValue, lgVariant, lgVariant);

    // protect sheet again with a new password

    strVariant.bstrVal = ::SysAllocString(L"password2");

    AutoWrap(DISPATCH_METHOD, NULL, xlSheet.pdispVal, L"Protect", 1, strVariant);

    SysFreeString(strVariant.bstrVal);

    VariantClear(&xlApp);

    VariantClear(&xlBooks);

    VariantClear(&xlBook);

    VariantClear(&xlSheet);

    OleUninitialize();

    cout<<"ok done"<<endl;

    getch();

    return 0;

    }

     

     

     

    Regards

     

     Sahir Shah

    Sunday, September 23, 2007 9:42 AM
  • Yeah !
    Big thx !

    It's works perfectly, where are you find help for this ? Like you say it's an hard way and the documentation is rare.
    I hate mfc for the standard reasons : Big code, a lot of thinks useless, and I like control all my code.
    I don't want to use import because there is cases where it's not work correcly (not same OS, not same Excel, different directory)

    I have the time to make a good code and distribute it for haunted of WIN32 pure code Smile .
    Sunday, September 23, 2007 3:48 PM