Create Business Intelligence Excel Charts Using VBA And Upload it To SharePoint
Introduction
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.
http://csharpcorner.mindcrackerinc.netdna-cdn.com/article/create-business-intelligence-excel-charts-using-vba-and-upload-to-sharepoint/Images/image009.png
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.