As my last post http://www.navision.net/forum/topic.asp?TOPIC_ID=6377 is still unanswered. I am trying to solve my problem on a diffrent way. Now i want to create a Excel Makro through automation. here is my starting code event := ‘ACTIVATE’; evobject :=‘Workbook’; cline := VBComponents.CreateEventProc(event, evobject); MakroCode := ‘ActiveChart.SeriesCollection.NewSeries’; VBComponents.InsertLines(cline, MakroCode); But doing so does not work, an error comes up, wich says, i frist have to create an instance for that automation. But i don’t know the right notation to create such an instance. I tried many ways but it did not work. So maybe anyone here can point me to the right direction.
Ufuk, Forgive me if I am saying the totally obvious to you but when using automation in C/SIDE you can create instances of automation objects by using the create keyword … CREATE(LautExcelApplication); or assigning it with a method from and existing object… LautExcelWorksheet := LautExcelApplication.Worksheet.Add; Regards, Chris.
What is VBComponents? If it’s Automation then use CREATE(VBComponents) to start use it!
@Chris @Arthur Thanks for your help, seems that i nearly have solved my problem, but next troubble comes, read above. After finding some helpfull examples (here in the forum)i changed my code totaly, it now looks : //PART1 oExcelSheets := oExcelWorkbook.Charts(); oExcelChart := oExcelSheets.Add(); oExcelRange := oExcelWorksheet.Range(‘B3’,‘C12’); nGallery := -4100; // nFormat := 1; nPlotBy := 2; nCategoryLabels := 1; nSeriesLabels := 0; bHasLegend := FALSE; oExcelChart.ChartWizard(oExcelRange, nGallery, nFormat, nPlotBy, nCategoryLabels, nSeriesLabels, bHasLegend); //PART2 VBProject := oExcelWorkbook.VBProject; VBComponents := VBProject.VBComponents; VBComponent := VBComponents.Add(1); CodeModule := VBComponent.CodeModule; testarry[1] := ‘Sub ufuk()’; testarry[2] := ‘ActiveChart.SeriesCollection.NewSeries’; testarry[3] := ‘ActiveChart.SeriesCollection(2).Values = “=Tabelle1!R3C4:R12C4”’; testarry[4] := ‘End Sub’; CodeModule.InsertLines(1,testarry[1]); CodeModule.InsertLines(2,testarry[2]); CodeModule.InsertLines(3,testarry[3]); CodeModule.InsertLines(4,testarry[4]); oExcelApplication.Run(ufuk); So far so good, the first part draws the chart. The second part creates a 4 line Excell makro named ufuk(for inserting the new range for the chart). But when executing the makro through Automation(oExcelApplication.Run(ufuk)) an visual basic runtime error accours saying (translated from German): - Value property of Series Object could not be defined So in the first moment i thought there must be a typo, but the code is totaly ok. If just let my codeunit creating the makro and excuting it from excel, everything works without a problem. And i realy don’t know why this happens, hopefully somone here can explain me, why this happens.
Hi
quote:
Originally posted by Ufuk Altinkaynak
… oExcelApplication.Run(ufuk); …
I love such programming [:D]. Create a small program at runtime. Cool. But wouldn’t it be better to try to solve the real problem? As far as I understand the code below is your real problem: ActiveChart.SeriesCollection(2).Values = "=Tabelle1!R3C4:R12C4"'; Doesn’t work a text variable? Something like this: MyRange:= 'R3C4:R12C4'; MyVar:= '"=' + Sheet.Name + '!' + MyRange +'"'; ActiveChart.SeriesCollection(2).Values(MyVar); Did you tried the SeriesCollection- Automation? There you can find a SeriesCollection.Add (with Source). bye André
Ufuk, I agreed with Andre. Wht not to create the chart as you want it with only methods and properties from excel automation objects in C/SDIE. A useful technique in these situations is to record a macro in excel while creating the chart and then view the VBA macro code and use this as a template for your C/SIDE automation code. Chris.
@André @Chris off course it would be better to do the whole automation process through C/SDIE, but it seems that this is at the moment impossible for me. I am repeeting myslef, but i tried so many code variations and they are simply not working. i don’t know if you can imagine how frusted it is, to sit on such simple code(in VB) so long. Anyway i will take a look at tha series automation, but i don’t think, that my try will be succesfuly. I will report later.
Hi Ufuk
quote:
Originally posted by Ufuk Altinkaynak
… I am repeeting myslef, but i tried so many code variations and they are simply not working. i don’t know if you can imagine how frusted it is, to sit on such simple code(in VB) so long. …
You are right. But bear in mind you are working at first with Navision not with Excel. You try to do ‘special’ things . This will need a ‘special’ time to create.
quote:
… Anyway i will take a look at tha series automation, but i don’t think, that my try will be succesfuly. …
Hey - I’m sure you will find a way. Good luck. Perhaps I can play at home a little with Navision and charts. Then I will post the results. bye André
@André As i thought it does not run 
if doing SeriesCollection.NewSeries; then navision comes up with (…create an instance) If doing CREATE(SeriesCollection) then navision comes up with (…Series instance could not be created…) and so on and son don’t ask me how many varionts i tried.
quote:
Hey - I’m sure you will find a way. Good luck. Perhaps I can play at home a little with Navision and charts. Then I will post the results.
Oh that would be realy great. I will give up for today, and will try on monday again.
Hi Ufuk
quote:
Originally posted by Ufuk Altinkaynak
As i thought it does not run
This one if the basics if you work with automation. You have to create always an instance for each automation object. Some examples: Create(xlApp); xlSheets:= xlapp.Sheets; xlSheet:= xlSheets.Add; and so on You will find in my example below how it works with charts. I use this sheet A B C D E *************************************** 1 * * 1.QRT * 2.QRT * 3.QRT * 4.QRT * *************************************** 2 *North* 300 * 180 * 290 * 230 * *************************************** 3 *South* 150 * 270 * 240 * 200 * *************************************** 4 *West * 220 * 260 * 190 * 300 * *************************************** 5 *East * 280 * 230 * 180 * 320 * *************************************** I assume that all other Excel objects in your code work correct. I start with parts of your code (with some changes) Name DataType Subtype Length xlApp Automation 'Microsoft Excel 9.0 Object Library'.Application xlBooks Automation 'Microsoft Excel 9.0 Object Library'.Workbooks xlBook Automation 'Microsoft Excel 9.0 Object Library'.Workbook xlSheets Automation 'Microsoft Excel 9.0 Object Library'.Worksheets xlSheet Automation 'Microsoft Excel 9.0 Object Library'.Worksheet xlRange Automation 'Microsoft Excel 9.0 Object Library'.Range xlChart Automation 'Microsoft Excel 9.0 Object Library'.Chart xlCharts Automation 'Microsoft Excel 9.0 Object Library'.Charts xlSeriesCollection Automation 'Microsoft Excel 9.0 Object Library'.SeriesCollection xlChartObject Automation 'Microsoft Excel 9.0 Object Library'.ChartObject xlChartObjects Automation 'Microsoft Excel 9.0 Object Library'.ChartObjects xlRange:= xlSheet.Range(**'A1:C5'**); Left:=50; Top:=50; Width:=300; Height:=300; xlChartObjects := xlSheet.ChartObjects; xlChartObject := xlChartObjects.Add(Left,Top,Width,Height); nGallery := -4100; nFormat := 1; nPlotBy := 2; nCategoryLabels := 1; nSeriesLabels := 0; bHasLegend := TRUE; xlChartObject.Chart.ChartWizard(xlRange, nGallery, nFormat, nPlotBy, nCategoryLabels, nSeriesLabels, bHasLegend); xlChartObject.Chart.SetSourceData(xlRange); The first range doesn’t work correct. I don’t know why. Therefore the last line correct the wrong entry. Now the Add Section xlSeriesCollection:= xlChartObject.Chart.SeriesCollection; xlSeriesCollection.Add(**'Data!D1:D5'**); xlSeriesCollection.Add(**'Data!E1:E5'**); Note: The name of my sheet: Data. Thats all! Now to the fun part. Show the creating of your chart. Same Excel-file / empty data area: A B C D E *************************************** 1 * * 1.QRT * 2.QRT * 3.QRT * 4.QRT * *************************************** 2 *North* * * * * *************************************** 3 *South* * * * * *************************************** 4 *West * * * * * *************************************** 5 *East * * * * * *************************************** 6 * * * * * * *************************************** 7 * * 300 * 180 * 290 * 230 * *************************************** 8 * * 150 * 270 * 240 * 200 * *************************************** 9 * * 220 * 260 * 190 * 300 * *************************************** 10* * 280 * 230 * 180 * 320 * *************************************** On runtime each value will be divided by 10. After filling the value into the data area a chart refresh shows the steps. xlRange:= xlSheet.Range('A1:E5'); Left:=50; Top:=50; Width:=300; Height:=300; xlChartObjects := xlSheet.ChartObjects; xlChartObject := xlChartObjects.Add(Left,Top,Width,Height); nGallery := -4100; nFormat := 1; nPlotBy := 2; nCategoryLabels := 1; nSeriesLabels := 0; bHasLegend := TRUE; xlChartObject.Chart.ChartWizard(xlRange, nGallery, nFormat, nPlotBy, nCategoryLabels, nSeriesLabels, bHasLegend); xlChartObject.Chart.SetSourceData(xlRange); End:= FALSE; xlRange:= xlSheet.Range('B7'); REPEAT IF FORMAT(xlRange.Value) <> '' THEN BEGIN TotalInteger:= xlRange.Value; TempInteger:= ROUND(TotalInteger / 10); REPEAT xlRange.Offset(-5,0).Value:= TempInteger; xlChartObject.Chart.Refresh; TempInteger:= TempInteger + ROUND(TotalInteger/10); UNTIL TempInteger > TotalInteger; xlRange.Offset(-5,0).Value:= TotalInteger; xlRange:= xlRange.Offset(1,0); IF FORMAT(xlRange.Value) = '' THEN xlRange:= xlRange.Offset(-4,1); END ELSE BEGIN End:= TRUE; END; UNTIL End = TRUE; bye André
Hi André, THNAKS A LOT !!! 
As it is weekend, i can’t test your code (i don’t have a dev system at home). I will xamine your code and try to understand what i did wrong. Again a BIG THANK YOU.(oder auf deutsch danke mann) !!
Hi Ufuk
quote:
Originally posted by Ufuk Altinkaynak
… Again a BIG THANK YOU.(oder auf deutsch danke mann) !! …
You are welcome! (oder auf deutsch Ist schon OK [:D] )! PS. Please note: I used ChartObject instead of Chart. But the Add section should be almost the same in both versions. bye André
Hi André! I checked your code and as expected, it simply does what it was written for. [:D]
quote:
PS. Please note: I used ChartObject instead of Chart. But the Add section should be almost the same in both versions
I changed that already in my code, and during that change, i recognized my “problem”. What i have to learn(understand or to find out) is, wich object can be assigned from wich object. Like the code: SeriesCollection:= oExcelChart.SeriesCollection; where do i find out, that SeriesCollection must be assigend from oExcelChart.SeriesCollection ?? If you have a hint from that would be realy great. And again a big award from me for gerat work/help you did/made. I think this would be needfull for the Tip’s and Tricks Section. BTW. This is getting more an d more a private chat [:D] Regards Ufuk Altinkaynak (Gruß aus Hamburg)
Hi Ufuk
quote:
Originally posted by Ufuk Altinkaynak
Hi André! I checked your code and as expected, it simply does what it was written for. [:D] …
[:D][:D][:D]. The cool thing is it was my first chart by automation. So you have a solution and I know a little bit more about automation[8D].
quote:
… What i have to learn(understand or to find out) is, wich object can be assigned from wich object. Like the code: SeriesCollection:= oExcelChart.SeriesCollection; where do i find out, that SeriesCollection must be assigend from oExcelChart.SeriesCollection ?? If you have a hint from that would be realy great. …
It is quite easy. You have to browse the methods and properties from each object for this one you want to do. So you need to create only Excel as instance. From this point you can assign all other objects (like in my example posting). In your special case you search for SeriesCollection. You don’t find it in xlApp, in Workbook or in Sheet. But in Chart (assigned from Workbook) or in ChartObject (assigned from Sheet) you can find the SeriesCollection. Then you know there you can assign your SeriesCollection- object. I hope this is understandable.
quote:
… I think this would be needfull for the Tip’s and Tricks Section. BTW. This is getting more an d more a private chat [:D] …
If somebody search for Excel, Chart and Automation he will find it here also [;)]. It seems to be private but it is on a worldwide forum. Perhaps some other users have tried this code also [8D]. Greetings from sunny Berlin bye André
Hi André
quote:
It is quite easy. You have to browse the methods and properties from each object for this one you want to do. So you need to create only Excel as instance. From this point you can assign all other objects (like in my example posting). In your special case you search for SeriesCollection. You don’t find it in xlApp, in Workbook or in Sheet. But in Chart (assigned from Workbook) or in ChartObject (assigned from Sheet) you can find the SeriesCollection. Then you know there you can assign your SeriesCollection- object. I hope this is understandable.
Afer reading this part (some times [:D]), i think that i understood it [:p] With this new knowledge it is now quite easy for me to modify the the whole chart. But finding the right “assign object” is sometimes “sysifus Job”, but it work’s. I now added some modifications like Axtitel.Characters.Text and so on. Regards BTW Did any off your distil Products “helped” you to solve the problem [:D]
Hi Ufuk, If you open Excel, press Alt+F11 and after this press F2, you get an overview of all Obejcts, Methods and Properties belonging to Excel-Automation (same with Word!). After clicking an Object, Method or Property you can read a short but helpfull description in the bottom of the overview. You can see, if you can assign the chosen Object from another. Hope this helps. Greetings, Frank
Hi Frank
quote:
Originally posted by FPulsfort
Hi Ufuk, If you open Excel, press Alt+F11 and after this press F2, you get an overview of all Obejcts, Methods and Properties belonging to Excel-Automation (same with Word!). After clicking an Object, Method or Property you can read a short but helpfull description in the bottom of the overview. You can see, if you can assign the chosen Object from another. Hope this helps. Greetings, Frank
Hmm. This was my first idea too. But it can lead you to the wrong objects. For example our SeriesCollection. Here you will see in the bottom ‘Object from Excel’. Excel has no method or property SeriesCollection. Another way would be to go to the help. Browse for SeriesCollection and look into the help for this method. There you will find: xxxx.SeriesCollection xxxx = necessary Chart or ChartGroup @Ufuk: Automation is fun for me. I don’t need something to drink to solve such ‘problems’ [8D]. I have to admit there are several other circumstances when should take a glass before I start to work [}:)]. bye André
Hi Frank, hi André,
quote:
Another way would be to go to the help. Browse for SeriesCollection and look into the help for this method. There you will find: xxxx.SeriesCollection xxxx = necessary Chart or ChartGroup
Thats the way i am actualy playing with. Plus recording excel macros where sometimes the required object can be seen.
quote:
@Ufuk: Automation is fun for me. I don’t need something to drink to solve such ‘problems’ . I have to admit there are several other circumstances when should take a glass before I start to work .
André i was just kidding, but i agree with your second sentence[}:)]
I have a related question. How do I find out if an object instantiation failed? For example, many Automation servers assume you are using VB. In VB you can do this: Set AutomationType2 = AutomationType1.makeType1 IF AutomationType2 is Nothing THEN >> deal with error ELSE >> work with data in AutomationType2 Is there a way in Navision to ask the “is Nothing” question?
In the words of Homer Simpson, “DOH!” Now that I finally searched the CSIDE Reference Guide using the right search ( I kept looking in the Index for null, nothing, empty … finally did a “Search” for null and it came back with two hits… one of which is …) The command I was looking for is “ISCLEAR”: (It would have been nice if the CLEAR command or the CREATE command had mentioned this useful and related function, oh well.) I quote from the Reference Guide: " Use this variable function to check whether an automation object has been created or not. Ok := ISCLEAR(Automation) Ok Data type: boolean The return value of Ok is TRUE if: the automation variable has not been created. See CREATE. the automation variable has been cleared. See CLEAR. the automation variable has been assigned NULL by an automation server. Automation Data type: automation "