I’m back with the final version of SphInX. In this post, we will discuss the process of exporting our results on the dashboard, to a MS PowerPoint presentation. Thought this has already been covered by Keshu in the final post of CompTraX, the major difference in this post would be that in CompTraX, we exported images from Excel to PowerPoint whereas in SphInX, we will create editable charts and editable tables in the PowerPoint presentation to represent the analysis (click here to download the dashboard with the PowerPoint template; .zip, .xlsm)
The video below shows the tool in action:
This approach of making editable slides would solve two purposes:
- You can make as many set of selections on the dashboard as you like and then export the results to a PowerPoint presentations to analyze/present them later
- If you find an anomaly in the data, you need not go through the entire process of generating the report again. Since the charts and tables exported to the PowerPoint presentation would be editable, you can make changes directly in the slides
There are two ways of creating editable objects in PowerPoint:
- Create new objects in the template, enter data in them and format them each time the export process is carried out. The line chart representing sales in SphInX has been exported using this methodology
- Keep an object in the template itself and edit it only when the export process is called. The tables in SphInX have been exported using this method. While this approach saves a lot of time, as you don’t have to create the object again and again, it has its own nuances; often fails in case of complex graphs.
The section below describes the two major code segments that have been used to export the data.
Code for creating an editable chart in PowerPoint from Excel using VBA
Set Gph_Shape = New_PPT_File.Slides(2).Shapes.AddOLEObject(ClassName:=”Excel.Chart”, Link:=msoFalse)
Set Gph_Object = Gph_Shape.OLEFormat.Object
Set PPT_chart = Gph_Object.Charts(1)
Set Chart_sheet = Gph_Object.Worksheets(1)
PPT_chart.SetSourceData Source:=Chart_sheet.Range(Chart_sheet.Cells(1, 1), Chart_sheet.Cells(Data_rows, Data_columns + 1))
PPT_chart.ChartType = xlLine
PPT_chart.PlotBy = xlRows
Gph_Shape.Line.BackColor.RGB = DisplaySheet.ChartObjects(“Linechart”).ShapeRange.Line.BackColor.RGB
For i = 1 To .SeriesCollection.Count
.SeriesCollection(i).Border.Weight = Linechart_Excel.SeriesCollection(i).Border.Weight
.SeriesCollection(i).Border.Color = Linechart_Excel.SeriesCollection(i).Border.Color
.Legend.Position = xlLegendPositionBottom
With .Axes(1, 1)
.NumberFormat = Linechart_Excel.Axes(1, 1).TickLabels.NumberFormat
.Font.Color = Linechart_Excel.Axes(1, 1).TickLabels.Font.Color
.Font.Bold = Linechart_Excel.Axes(1, 1).TickLabels.Font.Bold
.Format.Line.BackColor.RGB = Linechart_Excel.Axes(1, 1).Format.Line.BackColor.RGB
With .Axes(2, 1)
.NumberFormat = Linechart_Excel.Axes(2, 1).TickLabels.NumberFormat
.Font.Color = Linechart_Excel.Axes(2, 1).TickLabels.Font.Color
.Font.Bold = Linechart_Excel.Axes(2, 1).TickLabels.Font.Bold
.Format.Line.BackColor.RGB = Linechart_Excel.Axes(2, 1).Format.Line.BackColor.RGB
.Color = Linechart_Excel.Axes(2, 1).Border.Color
.LineStyle = Linechart_Excel.Axes(2, 1).Border.LineStyle
.Weight = Linechart_Excel.Axes(2, 1).Border.Weight
The above code is responsible for creating and adding data to the chart in PowerPoint‒ the data is picked up from the ‘Display Sheet’ and entered into the chart sheet. It also takes care of the formatting of the chart in PowerPoint‒ the code applies the same formatting to the chart in PowerPoint as it is present in our dashboard. So, as it can be seen in the code, we have taken care of formatting for all series values, the chart axes, the gridlines, etc. For the entire code with comments, you can download the dashboard (link given in the beginning of the post).
Code for creating an editable table in PowerPoint from Excel using VBA
Set DataTable_shp = New_PPT_File.Slides(2).Shapes(“DataTable”)
For Rowcounter = Data_rows To 1 Step -1
For Columncounter = Data_columns + 1 To 1 Step -1
If Rowcounter = 1 Then
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Text = Format(DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter), “mmm-yy”)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Text = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Bold = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter).Font.Bold
If Rowcounter = Max_val_row + 1 Then
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.Fill.BackColor.RGB = RGB(146, 208, 80)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Color = RGB(255, 255, 255)
DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Color = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter).Font.Color
This code picks up data from the data table in the ‘Display Sheet’ and enters it into an already created table on Slide 1 of the presentation. It also formats the table as it is formatted in the dashboard‒ the text fonts, the background fill, etc. have to be taken care of while exporting the table.
Two loops, one within the other, are used for the data transfer process. Once the data has been transferred, the extra rows and column are deleted from the table (code for this can be found in CreatePPT module of the dashboard).
These are only minor snippets of code, picked up to explain the important parts. You will need to go through the entire code thoroughly to understand it completely. For any queries, you can contact us at firstname.lastname@example.org and we’ll be happy to address them.
This will be all for the SphInX dashboard from my side‒ hope you enjoyed as much as I did. We can further improve it by incorporating your suggestions and feedback. So, please feel free to post them in our comments section.
I’ll be back soon with a new idea, and a new tool. Till then, keep Excelling!