HELLO EXCEL WIZARDS![:p] I’ve created a function that will put a chart within the current Excel Worksheet. It goes as follows: [^] EXCELADDCHART - VARIABLES Name DataType Length SheetNumber Integer CellRange Text 30 ChartName Text 30 ChartType Text 30 RowColumn Variant Category Variant Series Variant HasLegend Variant Title Text 30 CategoryTitle Variant ValuesTitle Variant SeriesTitle Variant NewSheet Boolean xlChartType Integer xlChartLocation Variant 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 [^] EXCELADDCHART - FUNCTION xlRange := xlSheet[SheetNumber].Range(CellRange); // FIND CHART TYPE ENUMERATOR 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; // ChartWizard Elements - Must pass into this function! // 1. Source for data. (CellRange) // 2. The Chart Type enumerator. (xlChartType) // 3. The option for the build-in autoformats.(7) // 4. Plot by rows or columns. (RowColumn - 1 for Rows, 2 for Columns) // 5. Integer for the number of rows or columns within the source that has the category labels. (Category) // 6. Integer for the number of rows or columns within the source that has the series labels. (Series) // 7. True to include legend, False for no legend… (HasLegend) // 8. Title of Chart. (Title) IF NewSheet = TRUE THEN BEGIN xlChart := xlBook.Charts.Add; xlChart.Name := ChartName; xlChart.SetSourceData(xlRange,RowColumn); xlChartLocation := ‘xlLocationAsNew’; Where := xlSheet[SheetNumber]; xlChart.Location(xlChartLocation,Where); xlChart.ChartType(xlChartType); END ELSE BEGIN 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; END; [^] The chart is added to the current worksheet (when NEWSHEET is false) beautifully! [:p] However, the chart ends up floating over the data on the spreadsheet [}:)] when I want it at the bottom of the data on the spreadsheet with the top left corner at the active cell. Any idea how I’d do this? I’ve looked at the code that moves the chart in VB: ActiveSheet.Shapes(“Chart 1”).IncrementLeft -378.75 ActiveSheet.Shapes(“Chart 1”).IncrementTop 457.5) However, the numbers given are in points - negative to move left, positive to move right. IF this is the way to go, then how do I find the number of points to move it? All suggestions appreciated.
The xlChartObject has ‘Top’ and ‘Left’ properties, that can be set equal to the ‘Top’ and ‘Left’ properties of some Range object. For example, to set it equal to the top left corner of the current active cell, you could write:xlChartObj.Top := xlApp.ActiveCell.Top; xlChartObj.Left := xlApp.ActiveCell.Left; Or, you could place the chart at some arbitrary cell as follows: xlChartObj.Left := xlSheet.Range('C10').Left; xlChartObj.Top := xlSheet.Range('C10').Top;
Well Fritz, you’ve done it again! It worked like a charm. Thanks so much for your help!