- Forum
- Windows Programming
- Excel automation in C++
Excel automation in C++
Pages: 12
Could some please share a working example of C++ code which performs the following:
- connects to running Excel if any, otherwise throws an exception,
- connects to current worksheet, otherwise throws an exception,
- read at least one cell from connected worksheet if any.
I hope someone did things like that before and would kindly share the code. Example should use C++ only, while Excel should be any 2003/2007/2010. Any approach can be used COM, OLE, MSAA.
Thank you.
(PS: pray, please do not refer to some link or say something like "google is your friend", I already browse a lot and found no appropriate example)
- connects to running Excel if any, otherwise throws an exception,
- connects to current worksheet, otherwise throws an exception,
- read at least one cell from connected worksheet if any.
I hope someone did things like that before and would kindly share the code. Example should use C++ only, while Excel should be any 2003/2007/2010. Any approach can be used COM, OLE, MSAA.
Thank you.
(PS: pray, please do not refer to some link or say something like "google is your friend", I already browse a lot and found no appropriate example)
Last edited on
Sorry, but I must point it out: Googling the exact title of this thread yields a Microsoft-provided link as second result that shows C++ code that starts MS Excel (any installed version), adds a workbook and fills some cells in the active sheet. What have you been googling before???
To "webJose",
you absolutely right, but watch carefully what you wrote "result that shows C++ code that starts MS Excel" while I do not need to start any Excel Instance, I need to connect to existing one if any, otherwise throw exception and pass by.
On the other hand if MS example is giving to you a source which can be easily reworked to what I need I would greatly appreciate if you show me how.
you absolutely right, but watch carefully what you wrote "result that shows C++ code that starts MS Excel" while I do not need to start any Excel Instance, I need to connect to existing one if any, otherwise throw exception and pass by.
On the other hand if MS example is giving to you a source which can be easily reworked to what I need I would greatly appreciate if you show me how.
Well, you'll hardly find an exact example of what you want, exactly, right? The code presented there is quite suitable to your needs. If you had googled correctly, your question today would have been "I have this C++ sample code from MS that creates a new instance of Excel, but I need to connect to an existing one". Then you would have received the answer: Use GetActiveObject() (http://msdn.microsoft.com/en-us/library/windows/desktop/ms221467(v=vs.85).aspx ) to retrieve the running instance from the ROT (Running Object Table). Note that this function may not return you the one you need if there is more than one Excel process running, in which case you'll probably have to enumerate the ROT. Google for "enumerating the ROT" for more information.
See? A lot of difference there.
Now, in order to accomplish what you want you need to know what properties and methods the Excel objects expose. Do you know them? If not I guess you can use OleView (comes with Visual Studio or the Windows SDK) to examine Excel's type library, or your can use some other tool that can read type libraries. Or you are welcome to explore the MSDN library (http://msdn.microsoft.com/library ). The ActiveSheet property will give you the current worksheet, hopefully. Remember that Excel sheets are not necessarily WORKsheets; they could be graphic sheets, for example.
But assuming ActiveSheet works just dandy for you, you just obtain the Range object of the cell you want to read (the MS sample uses the Range property so you have sample code to retrieve a range object), and then you obtain its value using the Value property. The MS sample uses this property to set values, so you must use DISPATCH_PROPERTYGET instead.
See? A lot of difference there.
Now, in order to accomplish what you want you need to know what properties and methods the Excel objects expose. Do you know them? If not I guess you can use OleView (comes with Visual Studio or the Windows SDK) to examine Excel's type library, or your can use some other tool that can read type libraries. Or you are welcome to explore the MSDN library (http://msdn.microsoft.com/library ). The ActiveSheet property will give you the current worksheet, hopefully. Remember that Excel sheets are not necessarily WORKsheets; they could be graphic sheets, for example.
But assuming ActiveSheet works just dandy for you, you just obtain the Range object of the cell you want to read (the MS sample uses the Range property so you have sample code to retrieve a range object), and then you obtain its value using the Value property. The MS sample uses this property to set values, so you must use DISPATCH_PROPERTYGET instead.
webJose,
it looks pretty simple all you say, but still I can't compile a sample from those resources. Unfortunately I have a very poor experience of Windows programming, thus all these COM and OLE looks weird to me.
I would appreciate a code snippet I could start learning from, at least something which captures Excel currently running and gets it's ActiveSheet. (Let's simplify here and say that we have one Excel window with one workbook and one worksheet which is active)
Thank you.
it looks pretty simple all you say, but still I can't compile a sample from those resources. Unfortunately I have a very poor experience of Windows programming, thus all these COM and OLE looks weird to me.
I would appreciate a code snippet I could start learning from, at least something which captures Excel currently running and gets it's ActiveSheet. (Let's simplify here and say that we have one Excel window with one workbook and one worksheet which is active)
Thank you.
If you do go the GetActiveObject route, be aware that Excel can be a bit dozy when it comes to registering itself in the Running Object Table:
"GetObject or GetActiveObject cannot find a running Office application"
http://support.microsoft.com/kb/238610
And only the first instance of the Excel app registers itself in the ROT, as the moniker for Excel is always the same.
How To Attach to a Running Instance of an Office Application
http://support.microsoft.com/kb/238975
But Office apps also register their documents in the ROT, so you can enumerate them, instead.
"GetObject or GetActiveObject cannot find a running Office application"
http://support.microsoft.com/kb/238610
And only the first instance of the Excel app registers itself in the ROT, as the moniker for Excel is always the same.
How To Attach to a Running Instance of an Office Application
http://support.microsoft.com/kb/238975
But Office apps also register their documents in the ROT, so you can enumerate them, instead.
Last edited on
Even though the example is quite good to show how to perform Automation, it has a serious problem: It incorrectly uses the Windows API function macro names with ANSI strings. It then omits the fact that you need to #include <cstdio> for the sprintf() function. Other than that, it compiles fine.
More explicitly: Because of the serious problem, you must compile the project forcing an ANSI build. If you are using Visual Studio from Microsoft, all projects default to Unicode and not ANSI. Either change that in the project properties or #undef UNICODE and #undef _UNICODE. After this and #including <cstdio>, the program compiles.
To be even more explicit:
1. Create a new empty C++ project of type console.
2. Add a new source file. Call it main.cpp.
3. #undef _UNICODE and UNICODE or change the project settings.
4. #include <ole2.h> as instructed by the MS code.
5. #include <cstdio>
6. Copy the AutoWrap() function.
7. Add the int main() function.
8. Copy the other code inside main().
9. Compile.
After that, study the Excel object model in order to understand the different properties and methods, and then exchange the call to CoCreateInstance() with a call to GetActiveObject(). At this point you can use the Excel object given by this function to enumerate the Workbooks collection to find the one you look for, or you can just obtain the active workbook via the ActiveWorkbook property.
Now you have a workbook. Either enumerate the Sheets collection (or there might be a Worksheets collection that holds only sheets of type worksheet, which is better) and settle for one of the worksheets, or use the ActiveSheet property on the workbook object to get the active sheet. Finally obtain the range you want by modifying the MS-provided sample code.
I understand that all these might look weird, but alas, that's how it is. This is how Automation looks like in C++.
BTW, other higher-level languages provide nice wrappers for Automation. You could do what you want to do very easily with a simple VB script! In VBScript you could do what you need in under 20 lines.
More explicitly: Because of the serious problem, you must compile the project forcing an ANSI build. If you are using Visual Studio from Microsoft, all projects default to Unicode and not ANSI. Either change that in the project properties or #undef UNICODE and #undef _UNICODE. After this and #including <cstdio>, the program compiles.
To be even more explicit:
1. Create a new empty C++ project of type console.
2. Add a new source file. Call it main.cpp.
3. #undef _UNICODE and UNICODE or change the project settings.
4. #include <ole2.h> as instructed by the MS code.
5. #include <cstdio>
6. Copy the AutoWrap() function.
7. Add the int main() function.
8. Copy the other code inside main().
9. Compile.
After that, study the Excel object model in order to understand the different properties and methods, and then exchange the call to CoCreateInstance() with a call to GetActiveObject(). At this point you can use the Excel object given by this function to enumerate the Workbooks collection to find the one you look for, or you can just obtain the active workbook via the ActiveWorkbook property.
Now you have a workbook. Either enumerate the Sheets collection (or there might be a Worksheets collection that holds only sheets of type worksheet, which is better) and settle for one of the worksheets, or use the ActiveSheet property on the workbook object to get the active sheet. Finally obtain the range you want by modifying the MS-provided sample code.
I understand that all these might look weird, but alas, that's how it is. This is how Automation looks like in C++.
BTW, other higher-level languages provide nice wrappers for Automation. You could do what you want to do very easily with a simple VB script! In VBScript you could do what you need in under 20 lines.
Hello
WebJose, I assume you apply to the following link as an example: http://support.microsoft.com/kb/216686/en-us?fr=1
I also used the way provided by Andywesteken: http://support.microsoft.com/kb/238975
And here is a code snippet I'd like to question about:
What I can't understand is how GetActiveObject can return "Success" is I do not have Excel currently running?
I never used VB or VBScript. Also I suspect that in other language this task could be easier and intuitively understandable. However I have a strong restriction to use C++ only.
WebJose, I assume you apply to the following link as an example: http://support.microsoft.com/kb/216686/en-us?fr=1
I also used the way provided by Andywesteken: http://support.microsoft.com/kb/238975
And here is a code snippet I'd like to question about:
|
|
What I can't understand is how GetActiveObject can return "Success" is I do not have Excel currently running?
I never used VB or VBScript. Also I suspect that in other language this task could be easier and intuitively understandable. However I have a strong restriction to use C++ only.
Last edited on
Yes.
Sure it was there in the Processes. I killed Excel and code began to FAIL, which is correct.
Well now it becomes more and more clear for me. Thank you.
I will try to proceed from here and post additional questions if any into this topic.
Sure it was there in the Processes. I killed Excel and code began to FAIL, which is correct.
Well now it becomes more and more clear for me. Thank you.
I will try to proceed from here and post additional questions if any into this topic.
Last edited on
I believe its a property of the "Application" object in Excel to set 'Visible' to either true or false. That's also another reason you are getting an S_OK return and not seeing anything. Try setting the visible property to true and it should become visible as soon as you run your code, same as if you started it with a shortcut or double clicked on the exe
As WebJose said, other languages have built in COM implementations that make everything a lot easier with COM. I've always used PowerBASIC in the Excel work I do, and it only takes a few lines of code to start Excel (there is an option to start a new instance, or use an already running instance if one can be found) , make it visible, create worksheets or iterate through them, read/write cells, etc.
One thing about Excel - I think it can only be run through what is termed in COM the IDispatch interface. I have it on good authority that one can't successfully make direct VTable interface calls on it. When I get a chance I'd like to check that out for myself and verify it. Direct interface calls on object methods through a VTable are much faster than the IDispatch late binding technique. However, the difference is considerably less on out of process servers, which is what Excel is.
As WebJose said, other languages have built in COM implementations that make everything a lot easier with COM. I've always used PowerBASIC in the Excel work I do, and it only takes a few lines of code to start Excel (there is an option to start a new instance, or use an already running instance if one can be found) , make it visible, create worksheets or iterate through them, read/write cells, etc.
One thing about Excel - I think it can only be run through what is termed in COM the IDispatch interface. I have it on good authority that one can't successfully make direct VTable interface calls on it. When I get a chance I'd like to check that out for myself and verify it. Direct interface calls on object methods through a VTable are much faster than the IDispatch late binding technique. However, the difference is considerably less on out of process servers, which is what Excel is.
I just checked the Excel type library using OleView and indeed all interfaces a dispinterfaces, meaning no VTable access as freddie1 stated. Oddly enough, MS Word's type library does declare dual interfaces, meaning VTable access.
Yea, I've seen posts about that too over in the PowerBASIC forums (about the direct interface calls with Word). That was a new feature of the PowerBASIC compiler a couple versions ago. Before that it just did dispinterface calls. I'd like to update some of my programs at work to use the direct VTable calls when I get a chance.
You seem to be a COM guy too WebJose. You might be interested in something I'm working on right now. I've nearly finished a Type Lib Browser tool that creates the interface header files necessary to connect to a COM component. In fact, I'm typing this post in a Code::Blocks created program that hosts the "Shell.Explorer" object, i.e., Internet Explorer in ieframe.dll. I created an instance of it in a window and navigated to this site. Here is my auto-generated IWebBrowser interface...
I'm pretty excited about it. I'll post the code when I get it ready. My program is 9728 bytes only!!!!
You seem to be a COM guy too WebJose. You might be interested in something I'm working on right now. I've nearly finished a Type Lib Browser tool that creates the interface header files necessary to connect to a COM component. In fact, I'm typing this post in a Code::Blocks created program that hosts the "Shell.Explorer" object, i.e., Internet Explorer in ieframe.dll. I created an instance of it in a window and navigated to this site. Here is my auto-generated IWebBrowser interface...
|
|
I'm pretty excited about it. I'll post the code when I get it ready. My program is 9728 bytes only!!!!
Last edited on
Looks very interesting, freedie, but you say it generates header files. I guess for unmanaged C++, right? If so shouldn't that be a class? Looks more like a hybrid between IDL and C++.
Its pure, unmanaged, Win32 C++. Objbase.h defines the word 'interface' to be a struct. So, this...
...is really this...
The 'class' keyword would work equally as well as struct or interface, however, then it would be necessary to clutter up the declarations with the 'public' keyword (I think).
So the ediface above simply defines for the client of ieframe.dll (Internet Explorer) a block of memory of a size of 128 bytes (25 X 4 + 7 X 4, i.e., IWebBrowser has 25 methods, and IDispatch 7), because there are 27 virtual functions.
Clicking a button to auto-generate a proper header is a million times easier than converting an OleView Idl dump to compilable C++.
|
|
...is really this...
|
|
The 'class' keyword would work equally as well as struct or interface, however, then it would be necessary to clutter up the declarations with the 'public' keyword (I think).
So the ediface above simply defines for the client of ieframe.dll (Internet Explorer) a block of memory of a size of 128 bytes (25 X 4 + 7 X 4, i.e., IWebBrowser has 25 methods, and IDispatch 7), because there are 27 virtual functions.
Clicking a button to auto-generate a proper header is a million times easier than converting an OleView Idl dump to compilable C++.
Last edited on
Ah, I see. I was unaware about the interface keyword being defined like that, plus I was unaware that the keyword "public" was not needed for structs.
So yes, I like the idea a lot. What I wouldn't mind having too is of course the GUID's for coclasses and interfaces, enumerations and other data types. I also hope that the tool can properly show inheritance of other interfaces? Like IWebBrowser2 inheriting from IWebBrowser.
So yes, I like the idea a lot. What I wouldn't mind having too is of course the GUID's for coclasses and interfaces, enumerations and other data types. I also hope that the tool can properly show inheritance of other interfaces? Like IWebBrowser2 inheriting from IWebBrowser.
Yes, I have to code that yet about adding the GUIDs to the interface header. Of course, they're accessable from the type library and just need to be read. Tell you what, here is my 9216 byte Web Browser (I'm using it now to post) I just created. IWebBrowser.h is the auto-generated file...
continued...
|
|
continued...
|
|
I'll probably have to break this up...
|
|
|
|
Pages: 12