1

Topic: As through OLE the schedule in MS Excel

Kind time of days, dear colleagues! There is an application which sends the data to Microsoft Excel. Sends through OLE-automation. That is - fills cells in the table in digits. How, transferring commands through OLE, to create the schedule? P.S. I use Qt, but it is not so basic. Me interface commands (methods and properties) interest. Examples of operation with MS Excel means Qt: https://forum.qt.io/topic/50062/help-to … -qaxobject http://www.qtcentre.org/archive/index.php/t-8288.html But how to build the schedule? Tried to view  a file Excel.exe through OLE/Com Object Viewer (menu File/View-TypeLib...) - there the whole dial-up of interfaces, I do not know - whether brings such search the positive result?  it is glad to any reasons!

2

Re: As through OLE the schedule in MS Excel

3

Re: As through OLE the schedule in MS Excel

Here any material is (it was possible ): https://docs.aspose.com/display/slidesn … OLE+Object

4

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> There is an application which sends the data to Microsoft Excel. Sends through OLE-automation. AG> That is - fills cells in the table in digits. AG> as, transferring commands through OLE, to create the schedule? The most simple method is to create a macro on already filled data (it is possible and to fill in a macro). Then to correct behavior of a macro, replacing global calls in a macro on exterior, accessible through Application and other.

5

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> As, transferring commands through OLE, to create the schedule? AG> Zarenee is glad to any reasons! To include record of a macro and to create the schedule hands then to stop record. To look at the code of a macro and to do all too most from the outside. I so always in a Word did.

6

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> Kind time of days, dear colleagues! AG> There is an application which sends the data to Microsoft Excel. Sends through OLE-automation. AG> That is - fills cells in the table in digits. AG> as, transferring commands through OLE, to create the schedule? AG> P.S. I use Qt, but it is not so basic. Me interface commands (methods and properties) interest. AG> Examples of operation with MS Excel means Qt: AG> https://forum.qt.io/topic/50062/help-to … -qaxobject AG> http://www.qtcentre.org/archive/index.php/t-8288.html AG> But how to build the schedule? AG> Tried to view  a file Excel.exe through OLE/Com Object Viewer (menu File/View-TypeLib...) - there the whole dial-up of interfaces, AG> I do not know - whether brings such search the positive result? AG> Zarenee is glad to any reasons! Try to look at examples on VBA here for example https://msdn.microsoft.com/en-us/librar … e/ff629397 (v=office.14).aspx https://msdn.microsoft.com/en-us/librar … e/aa203725 (v=office.11).aspx And further it is possible to translate it already c ++

7

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> As, transferring commands through OLE, to create the schedule? AG> me interface commands (methods and properties) interest. For this purpose in office applications there is a built in macro-recorder. From the menu to select "to begin macro record", then to make that it is necessary (to create the schedule) hands. The code on VBA  will be created, it is possible to look at it to fulfill, switched in VBA (Alt+F11) That there was a button with macroes, it is necessary to include  "development" on a tape. NB: AFAIK, the term "transmission of OLE verbs" has other value, it is better than it in given context not to use.

8

Re: As through OLE the schedule in MS Excel

Hello, bnk, you wrote: bnk> For this purpose in office applications there is a built in macro-recorder. bnk> from the menu to select "to begin macro record", then to make that it is necessary (to create the schedule) hands. bnk> the code on VBA  will be created, it is possible to look at it to fulfill, switched in VBA (Alt+F11) bnk> That there was a button with macroes, it is necessary to include  "development" on a tape. Yes, thanks, I in course. bnk> NB: AFAIK, the term "transmission of OLE verbs" has other value, it is better than it in given context not to use. It agree that " commands" - slightly another. Well and then - through utility OLE/COM Object Viewer - to view interface MS Excel and to select that it is necessary to cause from our application. Approximately so.

9

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> Well and then - through utility OLE/COM Object Viewer - to view interface MS Excel and to select that it is necessary to cause AG> from our application. Approximately so. In the same place ten thousand lines of the auto-generated code will be, is not present? How it is planned to "select"? I would not advise such approach. I would advise to use built in in Excel makro-rekorer.

10

Re: As through OLE the schedule in MS Excel

Hello, bnk, you wrote: bnk> Hello, AlexGin, you wrote: AG>> Well and then - through utility OLE/COM Object Viewer - to view interface MS Excel and to select that it is necessary to cause AG>> from our application. Approximately so. bnk> In the same place ten thousand lines of the auto-generated code will be, is not present? How it is planned to "select"? Whence at such method  ? Here references - on  examples (just for my tasks on Qt): 1) http://www.qtcentre.org/threads/41857-A … cel-charts 2) https://forum.qt.io/topic/72943/generat … qt-activex 3) http://www.qtcentre.org/archive/index.php/t-33584.html 4) https://code.csdn.net/snippets/143217 5) https://www.qtforum.de/viewtopic.php?p=58468 bnk> I such approach would not advise. I would advise to use built in in Excel makro-rekorer. +100500 Yes, it needless to say! Only here after that, the received macro (VBA script) - is required to be "translated" to a C ++ (for start of this code on "client"). I above resulted the useful references which I use in the course of my experiments under the given task: Transfer of the received macro in a C ++ the code, for start on my application. P.S. Here a role of utility OLE/COM Object Viewer - she allows to view COM API the demanded OLE-server, in this case it MS EXCEL - about the interfaces/methods/constants implemented in it.

11

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> P.S. Here a role of utility OLE/COM Object Viewer - she allows to view COM API the demanded OLE-server, AG> in this case it MS EXCEL - about the interfaces/methods/constants implemented in it. If the Microsoft compiler with COM the directive #import is much more convenient for operation. You receive a car-komplit and all  values of parameters from a box. https://rsdn.org/article/vcpp/import.xml the author (): Igor Tkachyov Data: 3/9/2001 In given article is resulted an explanation of operation of the directive #import the Visual C compiler ++ and examples of its usage with MS Word, MS Excel, ADO DB and ActiveX Control are given. Article truth 15  prescription, but the directive #import is still better than ole object viewer.

12

Re: As through OLE the schedule in MS Excel

Hello, respected bnk, you wrote: bnk> If the Microsoft compiler with COM the directive #import is much more convenient for operation. Yes, I about the directive #import In a case when I apply class library Qt, application of this directive does not help me with course. bnk> you receive a car-komplit and all  values of parameters from a box. Yes, I and did, applied #import to my projects on MFC. bnk> https://rsdn.org/article/vcpp/import.xml the Author (: Igor Tkachyov Data: 3/9/2001 In given article is resulted an explanation of operation of the directive #import the Visual C compiler ++ and examples of its usage with MS Word, MS Excel, ADO DB and ActiveX Control are given. bnk> Article truth 15  prescription, but the directive #import is still better than ole object viewer. With it anybody also does not argue. P.S. In a case with creation of schedules through MS Excel - a principal problem not in #import vs OLE/COM Viewer It is faster a taste question. Principal problem - in object model MS Excel in which GOOD INTENTIONS pave ROAD To the HELL Well here I I want to construct the schedule, imitating - one of table columns. Excel "swallows" as well remaining columns... Though anybody about it also DID NOT ASK it! It would Seem what is easier - build the schedule on that collection that to you transferred through OLE so is not present - picks up all around! If to build on the same sheet -  all columns of the table If to build in the same work book (workbook) swallows all  sheets... To Disconnect a mode of a software for the user of type ' the housewife ' in MS Excel it is impossible - to build the Unique output schedules in the separate book It is truth not absolutely conveniently for perception by the user, but eliminates such here collisions. Yes, the bulky decision, yes I want to make more compactly - but here Excel "too clever" for simple decisions!

13

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: bnk>> If the Microsoft compiler with COM the directive #import is much more convenient for operation. AG> yes, I about the directive #import AG> In a case when I apply class library Qt, application of this directive does not help me with course. Why? For it only compiler M $ is necessary. MFC/ATL it is not required

14

Re: As through OLE the schedule in MS Excel

Hello, bnk, you wrote: bnk> Hello, AlexGin, you wrote: bnk>>> If the Microsoft compiler with COM the directive #import is much more convenient for operation. AG>> yes, I about the directive #import AG>> In a case when I apply class library Qt, application of this directive does not help me with course. bnk> why? For it only compiler M $ is necessary. MFC/ATL it is not required MFC that it is not required, but all operation through OLE - is conducted by means of library Qt classes here one of the major classes, for support COM: QAxObject However, a problem described above, - the strange operation of object model MS Excel - passage to this or that ' wrapping ' does not close library. Here the reason on  COM the server and its functional.

15

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> Well here I want to construct the schedule, imitating - one of table columns. AG> Excel "swallows" as well remaining columns... Though anybody about it also DID NOT ASK it! AG> it would Seem what is easier - build the schedule on that collection that to you transferred through OLE so is not present - picks up all around! Never met such behavior. ActiveSheet. Shapes. AddChart. Select ' ActiveChart. SetSourceData Source: = Range ("List1! $A$1: $ B$9") ' two schedules ' ActiveChart. SetSourceData Source: = Range ("List1! $A$1: $ A$9") ' one schedule ActiveChart. SetSourceData Source: = Range ("List1! $A$1: $ C$9") ' three schedules ActiveChart. ChartType = xlLine AG> If to build on the same sheet -  all columns of table AG> If to build in the same work book (workbook) swallows all  sheets... You would accompany the code - it would be possible to advise/explain something. As a rule, the server fulfills that the CATFISH the client asks. Probably, you speak to it about it to swallow.

16

Re: As through OLE the schedule in MS Excel

Hello, Vi2, you wrote: Vi2> You would accompany the code - it would be possible to advise/explain something. As a rule, the server fulfills that the CATFISH the client asks. Probably, you speak to it about it to swallow. Here an example of my code: QAxObject* pSheet = RetrieveSelectedSheet (); if (pSheet) {pSheet-> dynamicCall ("Activate (void)"); QAxObject *pShapes = pSheet-> querySubObject ("Shapes"); if (pShapes) {QAxObject *emptyCell = pSheet-> querySubObject ("Cells (int, int)", 1, 1); emptyCell-> dynamicCall ("Select (void)"); pShapes-> dynamicCall ("AddChart (int)", 51, 100, 100, 500, 800);//xlColumnClustered = 51 int nbChart = pShapes-> property ("Count").toInt (); QAxObject *pShape = pShapes-> querySubObject ("Range (int)", nbChart); pShape-> dynamicCall ("Select (void)"); QAxObject *pChart = m_pWorkbook-> querySubObject ("ActiveChart"); if (pChart) {QAxObject *pLegend = pChart-> querySubObject ("Legend"); pChart-> setProperty ("Element", 334);//msoElementPrimaryCategoryGridLinesMajor pChart-> setProperty ("Element", 2);//msoElementChartTitleAboveChart pLegend-> setProperty ("Caption", (QVariant) "I very good"); pChart-> setProperty ("Element", 309);//msoElementChartTitleAboveChart QAxObject *pSeriesCollection = pChart-> querySubObject ("SeriesCollection ()"); pSeriesCollection-> dynamicCall ("NewSeries (void)"); int iNumber = pSeriesCollection-> property ("Count").toInt (); QAxObject *pSeries = pChart-> querySubObject ("SeriesCollection (int)", iNumber); QString strChartName = QString:: asprintf ("Chart N %u", aiPriority); pSeries-> setProperty ("Name", (QVariant) strChartName); pSeries-> setProperty ("XValues", alistXValues); pSeries-> setProperty ("Values", alistValues); pSeries-> dynamicCall ("Select (void)");}}} And further - Excel creates "miracles": the Variant 1: if on current sheet something was (in sense rows of numeric data), my values transferred in calls pSeries-> setProperty ("XValues", alistXValues); and pSeries-> setProperty ("Values", alistValues); are ignored!!! A variant 2: if on current sheet was nothing - that all is fulfilled quite correctly (my values are accepted and the schedule is drawn). P.S. These all miracles are watched on Windows 7 / 64 (SP1); MS OFFICE 2010.

17

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> MFC that is not required, but all operation through OLE - is conducted by means of library Qt AG classes> here one of the major classes, for support COM: QAxObject AG> However, a problem described above, - the strange operation of object model MS Excel - passage to this or that ' wrapping ' does not close library. AG> Here the reason on  COM the server and its functional. Those matter is not that you do not know an object model of Ekselja, in that,  use for certain curve in questions of COMAS of KjuT, and that in Eksele all is curve, and the server to us wrong. Well-well. On a sentence to make a macro and the help of the built in means of record (an ideal method to study models of programs of Office), you answer very strange. It is possible to wish only the big successes.

18

Re: As through OLE the schedule in MS Excel

Hello, AlexGin, you wrote: AG> Hello, Vi2, you wrote: Vi2>> You would accompany the code - it would be possible to advise/explain something. As a rule, the server fulfills that the CATFISH the client asks. Probably, you speak to it about it to swallow. AG> here an example of my code: AG> AG> QAxObject* pSheet = RetrieveSelectedSheet (); AG> if (pSheet) AG> {AG> pSheet-> dynamicCall ("Activate (void)"); AG> QAxObject *pShapes = pSheet-> querySubObject ("Shapes"); AG> if (pShapes) AG> {AG> QAxObject *emptyCell = pSheet-> querySubObject ("Cells (int, int)", 1, 1); AG> emptyCell-> dynamicCall ("Select (void)"); AG> pShapes-> dynamicCall ("AddChart (int)", 51, 100, 100, 500, 800);//xlColumnClustered = 51 AG> int nbChart = pShapes-> property ("Count").toInt (); AG> QAxObject *pShape = pShapes-> querySubObject ("Range (int)", nbChart); AG> pShape-> dynamicCall ("Select (void)"); AG> QAxObject *pChart = m_pWorkbook-> querySubObject ("ActiveChart"); AG> if (pChart) AG> {AG> QAxObject *pLegend = pChart-> querySubObject ("Legend"); AG> pChart-> setProperty ("Element", 334);//msoElementPrimaryCategoryGridLinesMajor AG> pChart-> setProperty ("Element", 2);//msoElementChartTitleAboveChart AG> pLegend-> setProperty ("Caption", (QVariant) "I very good"); AG> pChart-> setProperty ("Element", 309);//msoElementChartTitleAboveChart AG> QAxObject *pSeriesCollection = pChart-> querySubObject ("SeriesCollection ()"); AG> pSeriesCollection-> dynamicCall ("NewSeries (void)"); AG> int iNumber = pSeriesCollection-> property ("Count").toInt (); AG> QAxObject *pSeries = pChart-> querySubObject ("SeriesCollection (int)", iNumber); AG> QString strChartName = QString:: asprintf ("Chart N %u", aiPriority); AG> pSeries-> setProperty ("Name", (QVariant) strChartName); AG> pSeries-> setProperty ("XValues", alistXValues); AG> pSeries-> setProperty ("Values", alistValues); AG> pSeries-> dynamicCall ("Select (void)"); AG>} AG>} AG>} AG> AG> And further - Excel creates "miracles": AG> the Variant 1: if on current sheet something was (in sense rows of numeric data), my values transferred in calls AG> pSeries-> setProperty ("XValues", alistXValues); and pSeries-> setProperty ("Values", alistValues); are ignored!!! AG> a variant 2: if on current sheet was nothing - that all is fulfilled quite correctly (my values are accepted and the schedule is drawn). AG> P.S. These all miracles are watched on Windows 7 / 64 (SP1); MS OFFICE 2010. And Release () to all this good Pushkin will cause?

19

Re: As through OLE the schedule in MS Excel

Hello, aloch, you wrote: A> On a sentence to make a macro and the help of the built in means of record (an ideal method to study models of programs of Office), you answer very strange. It is possible to wish only the big successes. The macro is made, is more exact - the whole series of macroes is made. Here, for example, one of them: Dim s As Integer Range ("D5").Select ' to be stored In cell D5 the number - an amount of lines for  a drawing s = ActiveCell. Cells. Value Range ("C9").Select ' MsgBox s ActiveSheet. Shapes. AddChart. Select ActiveChart. ChartType = xlColumnClustered ActiveChart. SetSourceData Source: = Range ("C9:C" and s) ActiveChart. Legend. Select Selection. Delete ActiveChart. SetElement (msoElementPrimaryCategoryGridLinesMajor) ActiveChart. SetElement (msoElementChartTitleAboveChart) Selection. Caption = "the Amount of events on intervals" ActiveChart. SetElement (msoElementPrimaryValueAxisTitleRotated) Selection. Caption = "the Amount of events" ActiveChart. SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) Selection. Caption = "interval Number (duration of an interval 2 )" ActiveChart. SeriesCollection (1).Select ActiveChart. ChartGroups (1).GapWidth = 2 ActiveChart. ChartArea. Select Range ("D5").Select On this macro, the appropriate C ++ the code is made. In this branch I result an example of such code. However,  object model MS Excel - all it, unfortunately, does not cancel Krivost that the schedule "picks up" given, NOT ADDRESSED to it, and simply allocated on a work sheet. It was necessary to get out - at first (with empty sheet) to build the schedule, then to "raise" to sheet the text and digits... As to splicing COM and Qt - that for it precisely it is not necessary to worry, it is all works as the Swiss watch.

20

Re: As through OLE the schedule in MS Excel

Hello, aloch, you wrote:... A> And Release () to all this good Pushkin will cause?  object of type QAxObject provides Release () is happens at an output of object from visibility area. Here is more detailed: http://doc.qt.io/qt-5/qaxobject.html#dtor.QAxObject

21

Re: As through OLE the schedule in MS Excel

Hello, aloch, you wrote: A> And Release () to all this good Pushkin will cause? What for so it is categorical? Method Release () in QAxObject (and generally in Qt) is not present. And how then in Qt COM objects who causes Release () for a COM object are released? The world falls? No, the boundary of the unknown person simply extended. But generally yes, if it would be desirable to work with Excel exceptional means Qt, yes, QAxObject. I simply wanted to tell what to do it optionally. And in the project on Qt it is still possible to use #import (under condition of compiler M usage $), and to have normal  and check of syntax by the compiler instead of the lines which are torn out with meat through Ole Viewer. Generally, all is fuss with generation of documents through Automation should die for a long time already after appearance at office 2007 Open Xml a format (.xlsx), and libraries, its supporting, type: https://libxlsxwriter.github.io/working … harts.html the Heap of problems out of the blue: - Excel should be installed - will not work on the server - Extremely  because of  and that that Excel it STA. What for to use Excel Automation for generation of documents? There are certainly variants. The Reasonable reason use Automation can to be for example that it is necessary to fill the data in the live document already opened in Excel.