Link to home
Start Free TrialLog in
Avatar of TheHartford
TheHartford

asked on

Create and run a excel macro from c++

Hello experts

Can anyone give me a sample code for opening excel, adding a simple macro and then executing the macro and showing the excel workbook using C++ ?

Thanks
The hartford
Avatar of jkr
jkr
Flag of Germany image

Check out http://support.microsoft.com/kb/194906/en-us ("How To Add and Run a VBA Macro Using Automation from MFC")
Avatar of TheHartford
TheHartford

ASKER

I looked into that code and it mainly talks abt MS-word, but amlooking for excel stuff, also i am a VB guy not too familiar with c++
I am now able to open excel and use its methods, but I am stuck when it comes to execute a macro from a work book that iwork with Can somebody help me out with that ? the code that i use the following : during compilation I get the error as "test undeclared identifier"

Excel::_ApplicationPtr excel;
      HRESULT hr = excel.CreateInstance(L"Excel.Application"); // Start excel
      
      if(FAILED(hr))
    {
AfxMessageBox("Excel problem");
    }
      
      Excel::_WorkbookPtr workbook = excel->Workbooks->Open("C:\\BOOK1.xls");
      
      excel->DisplayAlerts=FALSE;
      
      excel->Run test;  // PROBLEM HERE RUNNING THE MACRO

      workbook->Save();
    workbook->Close();
    excel->Quit();


any help is appreciated

thanks
ASKER CERTIFIED SOLUTION
Avatar of jkr
jkr
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks jkr

It works fine, but one small problem though, once my macro is run, I use the ShellExecute(NULL, _T("open"),  to open that excel file, excel stays in the background infront of my c++ front end. IN above code, i am making sure that i am destroying all excel references, but still am not able to set the focus of excel

how do i fix this ?

I'm not sure, but will setting
excel->visable = true

bring it to the front?
Nope I tried that ...i won't work also I tried the following ...i am getting a Unhandled exception ..so nto sure which one to use

      excel->Release();
any more suggestions ?

I am posting the code that I use to run a macro in excel, save it and quit. But it looks like the Quit() does not really close th excel.

any help is appreciated. I need this quick. so  MFC experts take a look and see what am i missing

 COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
  Excel::_ApplicationPtr excel;
  Excel::_WorkbookPtr workbook;

HRESULT hr = excel.CreateInstance("Excel.Application"); // Start excel
      if(FAILED(hr))
      {
            char msg[1024] = {0};
            sprintf(msg, "E: There was an error starting Excel: %d", hr);
      }
            
          excel->Workbooks->Open(sFile,VOptional, VOptional, VOptional, VOptional,
                                                VOptional, VOptional, VOptional, VOptional,
                                                VOptional, VOptional, VOptional, VOptional);

            excel->DisplayAlerts=FALSE;
            excel->Run("test");
            excel->Save();
            excel->Quit();
            

Thanks
All experts,

I figured out the issue and it now works fine. All I did was to make Excel visible to true and it worked. I was surprised that it took just this one line of code made things happen. anyway i will award the points to jkr for telling me how to call a macro from vc++

Thanks to all