SphInX ‒ A pharma KPI tracking dashboard [5/5]

Hello readers!

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:

  1. 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
  2. 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:

  1. 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
  2. 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

With PPT_chart

 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

 Next

.Legend.Position = xlLegendPositionBottom

 With .Axes(1, 1)

With .TickLabels

.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

End With

 End With

 .Format.Line.BackColor.RGB = Linechart_Excel.Axes(1, 1).Format.Line.BackColor.RGB

 With .Axes(2, 1)

 With .TickLabels

.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

End With

 .Format.Line.BackColor.RGB = Linechart_Excel.Axes(2, 1).Format.Line.BackColor.RGB

With .MajorGridlines.Border

.Color = Linechart_Excel.Axes(2, 1).Border.Color

.LineStyle = Linechart_Excel.Axes(2, 1).Border.LineStyle

.Weight = Linechart_Excel.Axes(2, 1).Border.Weight

End With

End With

  End With

Gph_Object.Save

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”)

Else

DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Text = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter)

End If

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)

Else

DataTable_shp.Table.Cell(Rowcounter, Columncounter).Shape.TextFrame.TextRange.Font.Color = DisplaySheet.Cells(19 + Rowcounter, 1 + Columncounter).Font.Color

End If

Next Columncounter

Next Rowcounter

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 query@crabsheet.com 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!

Have a question? just leave a comment and we will answer A.S.A.P