[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1897
  • Last Modified:

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
0
TheHartford
Asked:
TheHartford
  • 6
  • 2
1 Solution
 
jkrCommented:
Check out http://support.microsoft.com/kb/194906/en-us ("How To Add and Run a VBA Macro Using Automation from MFC")
0
 
TheHartfordAuthor Commented:
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++
0
 
TheHartfordAuthor Commented:
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jkrCommented:
Hmm, IMHO

excel->Run test;  // PROBLEM HERE RUNNING THE MACRO

should be

excel->Run("test");
0
 
TheHartfordAuthor Commented:
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 ?

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

bring it to the front?
0
 
TheHartfordAuthor Commented:
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 ?

0
 
TheHartfordAuthor Commented:
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
0
 
TheHartfordAuthor Commented:
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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now