OH GREAT EXCEL AUTOMATION WIZARDS! I PLEA FOR YOUR HELP! I’m creating an Excel Chart within a workbook. I have two sheets in the book - one has detail data (sheetnumber=1), and the second has the summary data (sheetnumber=2) that I want to chart. I determine within the function whether the chart should be a new sheet or within the current by passing a boolean NEWSHEET - which if false, should create the sheet within the current worksheet. I’m getting my chart - but it’s a brand new sheet! And, it’s the right type although the xlChartType variant has not yet been set - hows that??? I’m trying the LOCATION subtype of Chart, which according to all the stuff I can find to read - is the subtype to use. My problem is getting the chart to be within the worksheet with the summary data. I get an error that says: “Type conversion of Text is not possible. VT_USERDEFINED was probably the target type.” Here’s to code: VARIABLES: Name DataType xlBook Automation’Microsoft Excel 8.0 Object Library’.Workbook xlSheet Automation’Microsoft Excel 8.0 Object Library’.Worksheet xlSheets Automation’Microsoft Excel 8.0 Object Library’.Worksheet xlSheet1 Automation’Microsoft Excel 8.0 Object Library’.Worksheet xlChart Automation’Microsoft Excel 8.0 Object Library’.Chart xlRange Automation’Microsoft Excel 8.0 Object Library’.Range xlChartObject Automation’Microsoft Excel 8.0 Object Library’.ChartObject xlWorksheet Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlWorksheets Automation ‘Microsoft Excel 8.0 Object Library’.Worksheets PARAMETERS PASSED: Name DataType Subtype Length SheetNumber Integer CellRange Text 30 ChartName Text 30 ChartType Text 30 RowColumn Variant Category Variant Series Variant HasLegend Variant Title Variant CategoryTitle Variant ValuesTitle Variant SeriesTitle Variant NewSheet Boolean function code: xlRange := xlSheet[SheetNumber].Range(CellRange); xlChart := xlBook.Charts.Add; xlChart.Name := ChartName; ChartType := UPPERCASE(ChartType); CASE ChartType OF ‘BARCLUSTERED’: xlChartType := 57; ‘CLUSTEREDCOLUMN’: xlChartType := 51; ‘3DCLUSTEREDCOLUMN’: xlChartType := 54; ‘3DCLUSTERBAR’: xlChartType := 60; ‘LINE’: xlChartType := 4; ‘PIE’: xlChartType := 5; ‘STACKEDAREA’: xlChartType := 76; ‘3DPIE’: xlChartType := -4102; ‘3DPIEEXPLOSED’: xlChartType := 70; END; xlChart.SetSourceData(xlRange,RowColumn); IF NewSheet = TRUE THEN BEGIN xlChartLocation := ‘xlLocationAsNew’; xlChart.Location(xlChartLocation,xlSheet[SheetNumber]); END ELSE BEGIN ( [B)]error occurs here ) xlChartLocation := ‘xlLocationAsObject’; xlChart.Location(xlChartLocation,xlSheet[SheetNumber]); END; xlChart.ChartType := xlChartType; xlChart.HasTitle(Title); xlChart.SeriesCollection(xlRange); Please don’t tell me to look at the VB for Excel - that’s how I’ve gotten this far, and believe you me - it’s not much help on this. Maybe it’s the sequence of what I’m doing - maybe the Chart should be a Chartobject type - maybe I’m in over my head![V] I did try using ChartWizard - which is what the Appl Developer book says to do, but it doesn’t give the opportunity to put it in the current worksheet - so I’m now using the individual properties for the chart. All assistance is appreciated! Thanks in advance!
Try the number 2 instead of ‘xlLocationAsObject’ so like this xlChartLocation := 2; Explanation: xlChartLocation is a constant variable in visual basic. The represent a number. You can find out what number this is by clicking on the variable in VBA end press the CTRL+I combination. A tooltip with the value will show.
I originally had the xlChartLocation variant as a 2 but it still gave the same error. I just changed it back to see if perhaps something else I’d changed along the way now made this a viable option…no such luck! The error changed a little to say “Type conversion of INTEGER is not possible…” but I still don’t get past that single line of code.[V] I’ve read about CHARTOBJECT and CHARTOBJECTS types. I wonder if this isn’t my primary problem. Should I be using these instead of the chart type to embed the chart into the current worksheet? Incidentally, it does still give me the chart with the correct chart type, which I’m thinking must be some sort of default, because the type hasn’t been processed. Any ideas?
hmm cant tell what is going wrong. I would have to build a test project for that. Have no time for that. Maybe you could try using a variant datatype. I think the property expects a variant. Or consider this tool : [8D] http://www.mibuso.com/dlinfo.asp?FileID=333
Thanks for your help Jan Peiter. I did download the tool, but I really want to do this within the current code. Anyone else have any ideas, please?
Well, there’s something to be said for perserverance! [^] I figured it out! VARIABLES Name DataType Length xlSheet1 Automation ‘Microsoft Excel 8.0 Object Library’.Worksheet xlRange Automation’Microsoft Excel 8.0 Object Library’.Range xlChart Automation’Microsoft Excel 8.0 Object Library’.Chart xlChartObject Automation’Microsoft Excel 8.0 Object Library’.ChartObject xlChartObjects Automation’Microsoft Excel 8.0 Object Library’.ChartObjects xlWorksheet Automation’Microsoft Excel 8.0 Object Library’.Worksheet xlWorksheets Automation’Microsoft Excel 8.0 Object Library’.Worksheets Here’s the code: xlChartObjects := xlSheet[SheetNumber].ChartObjects; xlChartObject := xlChartObjects.Add(100,100,500,200); xlChartObject.Chart.SetSourceData(xlRange,RowColumn); xlChartObject.Chart.ChartType(xlChartType); IF Title <> ‘’ THEN BEGIN xlChartObject.Chart.HasTitle := TRUE; xlChartObject.Chart.ChartTitle.Text := Title; END; The chart is added to the exisiting worksheet! The issue I was having was with the methods. Best I can understand, a xlChart is a chart “sheet”, where an XLChartObject is a chart within a sheet - like a picture in a sheet. By using XlChartObjects, with member XlChartObject, then I could reference the Chart subtype. The methods seem to go like this (smart people - please correct me if I’m wrong): xlbook → xlsheets → xlsheet ->xlchartobjects → xlchartobject → xlchart. Once I got the correct automation variable defined, and figured out which was to be added, it worked like a charm. I had to play around with the “ADD” locations, but it came out correct. TWO ADDITIONAL QUESTIONS - RELATED TO THIS: 1. Does anyone know how I’d collect from the existing sheet the location of the last row used so I could find the ADD location on the fly? 2. I’m still having problems setting the legend colors, and the titles for the x and y axis. I can’t a property that’s valid in Navision. Thanks. FL
Two quick code samples form visual basic: navChart.Charts(0).SetData chDimCategories, chDataLiteral, CategorieNames navChart.Charts(0).SeriesCollection(intCountY).SetData chDimSeriesNames, chDataLiteral, SerieNames(intCountY) But, this is an array (CategoryNames) i’m using here and i’m affraid you cant pass arrays in navision but maybe i’m wrong … And maybe there is another way. Setting Color in visual basic = navChart.Charts(0).SeriesCollection(Y).Line.Color = newValue (this should be possible in navision) I know it may still be hard but maybe this gets you going the right direction. I wonder if this all is posible form within Navision.
Jan-Pieter, You bring up a good point. My chart is a bar chart, so what I find in VB when I use the macro - VB Editor method to find the commands, is: Worksheets(“sheet1”).ChartObjects(1).Chart. _ SeriesCollection(1).Interior.Color = RGB(255, 0, 0) Looks like the command you gave is for a line chart. That means - since I want a function that can be called and passed a chart type - I’ve gotta code for the different types of charts! I’m still trying to get the Interior command to work. There is an Interior Automation type, and I’m working on that now - to no avail![V] Ya know, someone should really write a book with all these excel commands in Navision! Gee, wouldn’t that be a great thing for oh, say, NAVISION to do![:0)] Thanks for your help.
In my opinion the best thing is to create a wrapper in visual basic that eases the use of the chart in Navision. You just cant bat anything in Navision.[V]
Well, perserverance won out again! [^] Here’s the code - for those who may attempt this some day - to set the colors of the bars on the chart: xlChartObject := xlSheet[SheetNumber].ChartObjects(ChartNumber); xlSeries := xlChartObject.Chart.SeriesCollection(SeriesNumber); xlSeries.Interior.ColorIndex := ColorNumber; Where ColorNumber is the index of the color to use (found using Macro Edit with Visual Basic). Still would like to know how to set the titles of the category and values, but hey, this code works good enough now that I can relax a while! Thanks for your help again Jan-Pieter! [:D]