Create Business Intelligence Excel Charts Using VBA And Upload To SharePoint

We can derive business intelligence out of the Excel data by making use of its charting capability. In order to work with charting options, Excel provides the chart object. The chart can either be an embedded chart (contained in a ChartObject object) or a separate chart sheet.

We have a lot of options to create business intelligence charts within SharePoint like SSRS, PerformancePoint, PowerPivot, Power BI, Client Side Rendering and so on. In this article, we will see how to get started with the charting capability of Excel using VBA, upload it and view it from SharePoint.

Prerequisites

We have to make sure that the developer tab is available in the Excel sheet in order to get started with development using VBA. In order to do tha,t right click anywhere on the Tool bar and select "Customize the Ribbon" option.

From the list box, select Developer option and click on OK.

This will activate the Developer tab in the Ribbon menu.

Click on "View Code" to open up the Visual Basic Code window.

Make charts with sample data

Before making use of the Excel data, we will see how to create a simple chart in Excel by making use of hard coded data to create a business intelligence chart out of it. We will be using the "charts.Add" method to add a new chart to the Excel sheet. We will then use ‘ActiveChart.SetSourceData Sheets("Sheet1").Range’ to set the data range and will populate the series data using ‘ ActiveChart.SeriesCollection(1).Formula’ .

Sub ConvertDataToChart()
'Add a new chart
Add
'Set a data range for the chart which will be populated later
SetSourceData Sheets("Sheet1").Range("a1:d4")
'Set the chart type
ChartType = xlBarClustered
'Set arbitrary values for the data series
SeriesCollection(1).Formula = _
"=SERIES(""Maruthi Baleno"",{""Q1"",""Q2"",""Q3"",""Q4""},{2000,3000,4000,5000},1)"
SeriesCollection(2).Formula = _
"=SERIES(""Ford Figo"",{""Q1"",""Q2"",""Q3"",""Q4""},{1500,2200,3400,4500},2)"
SeriesCollection(3).Formula = _
"=SERIES(""Renault Duster"",{""Q1"",""Q2"",""Q3"",""Q4""},{100,1100,3120,7300},3)"
End Sub

On running the macro, we will get the below chart output in the sheet.

We can change the type of the chart we want to display by modifying the ChartType property, as shown below.

ChartType = xlCylinderColStacked

 

Create Chart from Excel Data

Now, we will see how to create charts from the data present in the Excel sheet. Say for instance, we have the below set of data that indicates the mark list for a set of students and we want to create chart from this data.

We can use fairly simple VBA macro to get this done. We can add a chart to the sheet using ‘Chart. Add’ and set the properties for the ‘ActiveChart’ object. We will specify the range of Excel cells upon which the chart should be created by setting the value for ‘ActiveChart.SetSourceData Source’ . ‘ ActiveChart.ChartType’ will set the type of chart we want to use.

Sub MakeColumnChart()
'Add a new chart object
Add
'Set the Chart Properties
ChartType = xl3DColumn
SetSourceData Source:=Sheets("Sheet1").Range("A1:E7")
Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

We can now run the script and see how the chart looks like by clicking on the "Run" button.

Select the macro name and Click on Run.


This will generate the chart for us in the Excel sheet next to the data.

Create Pie Charts

Using the same technique, we can create similar Pie Charts to derive business intelligence. We will make use of the below Excel data to demo the pie chart experience.

The code is similar to the Column chart that we created above. The only change is the ‘ChartType’ property. We will be setting it to ‘xlPie’ this time.

Sub MakePieChart()
'Add a new chart object
Add
'Set the Chart Properties
ChartType = xlPie
SetSourceData Source:=Sheets("Sheet2").Range("A4:B7")
Location Where:=xlLocationAsObject, Name:="Sheet2"
End Sub

Let’s go ahead and select the macro and run it to see the output chart.

Thus, we can see the pie chart representation for the Excel data.

Build Line Chart and Cylinder Charts

By changing the ChartType to ‘xlLine’, we can convert the previous Pie Chart to a Line Chart as shown below.

ChartType = xlLine

 

Similarly, changing the ‘ChartType’ to ‘xlCylinderCol’ will fetch us the below Cylinder chart.

ChartType = xlCylinderCol

From the design tab, we can modify these charts and give a better look and feel to it.

Upload to SharePoint

So far, we saw how to create the charts from the Excel data. Now, we will upload the Excel to SharePoint so that it can be used for better collaboration among business users. Before uploading, let's save it. Do ensure that you save it in the xlsm format as specified by the warning message below so as to preserve the macros.

Now, head over to the SharePoint document library and upload the file.

As we can see the Excel file has been uploaded successfully. On clicking the Excel document, it will be rendered using Excel Online.

Thus, the pie chart has come up next to the Excel data just like in the desktop Excel application.

Available Chart Types

We can make use of the entire set of Charts provided by the Microsoft Excel team to derive and showcase business intelligence reports with the slightest customization. Refer the below table to see the complete list of available charts. We just have to change the ‘ChartType’ property to the required Name.

ChartType = <Specify ChartName Here>

Name	Value	Description
xl3DArea	-4098	3D Area.
xl3DAreaStacked	78	3D Stacked Area.
xl3DAreaStacked100	79	100% Stacked Area.
xl3DBarClustered	60	3D Clustered Bar.
xl3DBarStacked	61	3D Stacked Bar.
xl3DBarStacked100	62	3D 100% Stacked Bar.
xl3DColumn	-4100	3D Column.
xl3DColumnClustered	54	3D Clustered Column.
xl3DColumnStacked	55	3D Stacked Column.
xl3DColumnStacked100	56	3D 100% Stacked Column.
xl3DLine	-4101	3D Line.
xl3DPie	-4102	3D Pie.
xl3DPieExploded	70	Exploded 3D Pie.
xlArea	1	Area
xlAreaStacked	76	Stacked Area.
xlAreaStacked100	77	100% Stacked Area.
xlBarClustered	57	Clustered Bar.
xlBarOfPie	71	Bar of Pie.
xlBarStacked	58	Stacked Bar.
xlBarStacked100	59	100% Stacked Bar.
xlBubble	15	Bubble.
xlBubble3DEffect	87	Bubble with 3D effects.
xlColumnClustered	51	Clustered Column.
xlColumnStacked	52	Stacked Column.
xlColumnStacked100	53	100% Stacked Column.
xlConeBarClustered	102	Clustered Cone Bar.
xlConeBarStacked	103	Stacked Cone Bar.
xlConeBarStacked100	104	100% Stacked Cone Bar.
xlConeCol	105	3D Cone Column.
xlConeColClustered	99	Clustered Cone Column.
xlConeColStacked	100	Stacked Cone Column.
xlConeColStacked100	101	100% Stacked Cone Column.
xlCylinderBarClustered	95	Clustered Cylinder Bar.
xlCylinderBarStacked	96	Stacked Cylinder Bar.
xlCylinderBarStacked100	97	100% Stacked Cylinder Bar.
xlCylinderCol	98	3D Cylinder Column.
xlCylinderColClustered	92	Clustered Cone Column.
xlCylinderColStacked	93	Stacked Cone Column.
xlCylinderColStacked100	94	100% Stacked Cylinder Column.
xlDoughnut	-4120	Doughnut.
xlDoughnutExploded	80	Exploded Doughnut.
xlLine	4	Line.
xlLineMarkers	65	Line with Markers.
xlLineMarkersStacked	66	Stacked Line with Markers.
xlLineMarkersStacked100	67	100% Stacked Line with Markers.
xlLineStacked	63	Stacked Line.
xlLineStacked100	64	100% Stacked Line.
xlPie	5	Pie.
xlPieExploded	69	Exploded Pie.
xlPieOfPie	68	Pie of Pie.
xlPyramidBarClustered	109	Clustered Pyramid Bar.
xlPyramidBarStacked	110	Stacked Pyramid Bar.
xlPyramidBarStacked100	111	100% Stacked Pyramid Bar.
xlPyramidCol	112	3D Pyramid Column.
xlPyramidColClustered	106	Clustered Pyramid Column.
xlPyramidColStacked	107	Stacked Pyramid Column.
xlPyramidColStacked100	108	100% Stacked Pyramid Column.
xlRadar	-4151	Radar.
xlRadarFilled	82	Filled Radar.
xlRadarMarkers	81	Radar with Data Markers.
xlStockHLC	88	High-Low-Close.
xlStockOHLC	89	Open-High-Low-Close.
xlStockVHLC	90	Volume-High-Low-Close.
xlStockVOHLC	91	Volume-Open-High-Low-Close.
xlSurface	83	3D Surface.
xlSurfaceTopView	85	Surface (Top View).
xlSurfaceTopViewWireframe	86	Surface (Top View wireframe).
xlSurfaceWireframe	84	3D Surface (wireframe).
xlXYScatter	-4169	Scatter.
xlXYScatterLines	74	Scatter with Lines.
xlXYScatterLinesNoMarkers	75	Scatter with Lines and No Data Markers.
xlXYScatterSmooth	72	Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers	73	Scatter with Smoothed Lines and No Data Markers.

Source: TechNet

Summary

Thus, we saw how we can create Business Intelligence Charts from Excel Data and upload it to SharePoint.