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
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
Check out http://support.microsoft.com/kb/194906/en-us ("How To Add and Run a VBA Macro Using Automation from MFC")
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++
ASKER
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"Exc el.Applica tion"); // Start excel
if(FAILED(hr))
{
AfxMessageBox("Excel problem");
}
Excel::_WorkbookPtr workbook = excel->Workbooks->Open("C: \\BOOK1.xl s");
excel->DisplayAlerts=FALSE ;
excel->Run test; // PROBLEM HERE RUNNING THE MACRO
workbook->Save();
workbook->Close();
excel->Quit();
any help is appreciated
thanks
Excel::_ApplicationPtr excel;
HRESULT hr = excel.CreateInstance(L"Exc
if(FAILED(hr))
{
AfxMessageBox("Excel problem");
}
Excel::_WorkbookPtr workbook = excel->Workbooks->Open("C:
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 ?
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?
excel->visable = true
bring it to the front?
ASKER
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 ?
excel->Release();
any more suggestions ?
ASKER
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_PAR AMNOTFOUND , VT_ERROR);
Excel::_ApplicationPtr excel;
Excel::_WorkbookPtr workbook;
HRESULT hr = excel.CreateInstance("Exce l.Applicat ion"); // Start excel
if(FAILED(hr))
{
char msg[1024] = {0};
sprintf(msg, "E: There was an error starting Excel: %d", hr);
}
excel->Workbooks->Open(sFi le,VOption al, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional);
excel->DisplayAlerts=FALSE ;
excel->Run("test");
excel->Save();
excel->Quit();
Thanks
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_PAR
Excel::_ApplicationPtr excel;
Excel::_WorkbookPtr workbook;
HRESULT hr = excel.CreateInstance("Exce
if(FAILED(hr))
{
char msg[1024] = {0};
sprintf(msg, "E: There was an error starting Excel: %d", hr);
}
excel->Workbooks->Open(sFi
VOptional, VOptional, VOptional, VOptional,
VOptional, VOptional, VOptional, VOptional);
excel->DisplayAlerts=FALSE
excel->Run("test");
excel->Save();
excel->Quit();
Thanks
ASKER
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
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