wxWidgets Discussion Forum

Posted: **Sun Jan 01, 2012 8:04 am**

Hi All:

Can anyone please tell me how to create chart (x and y data) in Excel 2007 using wxAutomation. I am having VBA code to generate chart. Is there any class or procedure in wxWidgets to trigger VBA code?

Thanks in advance.

Posted: **Sun Jan 01, 2012 8:18 pm**

Hi,

do you have any previous experience with MS Excel VBA and OLE automation? I.e. are you aware of macro security that can seriously hinder your ability to run macros? wxWidget support for OLE Automation is also somewhat rudimentary, for example sometimes it may be hard to find out whether your call succeeded or not.

Anyway, if I understood you correctly, you already have a VBA macro in an Excel file and want to call it from C++ wxWidgets application. Below is a simple compilable example of how to run a (parameter-less) macro stored in a given file, it might serve you as a starting point. IIRC, macros are run in the context of active sheet, so if you use relative references, you have to account for that.

Code: Select all

#ifndef WX_PRECOMP
#include "wx/wx.h"
#endif

#include <wx/msw/ole/automtn.h>
#include <wx/filedlg.h>
#include <wx/textdlg.h>
#include <wx/log.h>


class MyFrame : public wxFrame {
public:
    MyFrame()
        : wxFrame(NULL, wxID_ANY, "Excel Automation Test", wxDefaultPosition, wxSize(600,500))
    {                
        wxTextCtrl* pTextCtrl = new wxTextCtrl(this, wxID_ANY, wxEmptyString, wxDefaultPosition, wxDefaultSize, wxTE_MULTILINE|wxTE_READONLY);
        wxLog::SetActiveTarget(new wxLogTextCtrl(pTextCtrl));
        wxLog::SetTimestamp("");
        wxLog::AddTraceMask(wxTRACE_OleCalls);

        wxBoxSizer* pSizer = new wxBoxSizer(wxVERTICAL);
        pSizer->Add(pTextCtrl, 1,wxEXPAND, 0 );		
        SetSizer(pSizer);
        Layout();
        Centre();
    }	
};



class MyApp: public wxApp
{
public:
    virtual bool OnInit()
    {
        if (!wxApp::OnInit())
            return false;

        MyFrame* frame = new MyFrame();         
        frame ->Show();
        SetTopWindow(frame);
        
        
        wxString fileName("C:\\test.xlsm");
        // uncomment the following line to invoke file open dialog
        // fileName = "";
        if (fileName.empty()) {
            wxFileDialog dlg(NULL, "Open Excel file", "", "",
                "XLS* files (*.xls*)|*.xls*", wxFD_OPEN|wxFD_FILE_MUST_EXIST);
            if (dlg.ShowModal() == wxID_CANCEL)
                return false;
            fileName = dlg.GetPath();
        }
        
        CallMacro(fileName);        
        
        return true;
    }

    void CallMacro(const wxString& fileName)
    {
        wxAutomationObject excel;
        
        wxLogMessage("Attempting to create Excel instance...");
        if (!excel.CreateInstance("Excel.Application")) {
            wxMessageBox("Failed to create Excel instance.");
            return;
        }        

        excel.PutProperty("Visible", true);
        // excel.PutProperty("DisplayAlerts", false);

        wxString macroName("Test");
        // uncomment the following line to invoke GetTextFromUser dialog
        // macroName = "";
        if (macroName.empty()) {
            macroName = wxGetTextFromUser("Type name of macro you want to run");
            if (macroName.empty())
                return;
        }
                
        wxLogMessage("Attempting to open file %s...", fileName);
        wxVariant result = excel.CallMethod("Workbooks.Open", fileName);
        if (result.IsNull()) {            
            wxMessageBox(wxString::Format("Failed to open %s.", fileName));
            return;
        }
        
         wxVariant params[2];
        
        params[0] = macroName;        
        excel.CallMethod("Application.Run", 1, params);                    
    }
};

IMPLEMENT_APP(MyApp)

Posted: **Mon Jan 02, 2012 4:16 pm**

Hi PB:

Thank you for your help.
I can understand the issues in VBA macro.
Is there any way to create chrat other than excuting VBA macro i.e using excel.PutProperty etc.?

Posted: **Mon Jan 02, 2012 6:39 pm**

Don't thank me for my help. In case you hadn't noticed, my solution has a fundamental flaw. You can't tell if the Application.Run call failed or not, as this method doesn't return a result but the call can of course still fall. I may be wrong, but I believe this is a limitation of implementation of wxWidget's OLE wrapper, which can't be easily worked around.

Edit
Sorry, it's been a long time since I used wxAutomation, so I forgot about wxAutomationObject::Invoke(). Using this method is more cumbersome, but it will tell you whether the call failed or not. Unfortunately, you still can't get the HRESULT to learn the possible reason but it's better than nothing. You can use it like this

Code: Select all

wxVariant params[2];
wxVariant retVal;
        
params[0] = macroName;                
if (!excel.Invoke("Application.Run", DISPATCH_METHOD, retVal, 1, params))
  wxMessageBox(wxString::Format("Failed to call Application.Run with macro %s.", macroName));

As for the chart generation: In theory, you should be able to emulate everything you can do in VBA using automation calls, but I can't really imagine doing that for anything complex. If you still want to try that and don't know how to create a chart in VBA, just record a macro of doing that in Excel manually and then mimic the resulting VBA code via C++ automation calls.

Sorry I couldn't be of more help.

wxWidgets Discussion Forum

wxAutomation and Excel 2007

wxAutomation and Excel 2007

Re: wxAutomation and Excel 2007

Re: wxAutomation and Excel 2007

Re: wxAutomation and Excel 2007