Unholy Trinity of R, Excel VBA and Powerpoint - Part 2 of 2
Using R to trigger Excel VBA macros to create PowerPoint presentations
TL;DR
This post demonstrates how to run VBA macros in Excel which in turn creates Presentations based off PowerPoint Templates.
This is part 2 of 2. Read part 1 here.
Quick Recap
In the previous post, we create the .potx template from the .pptx
file we wanted to automate and the Excel template with the macro .xlsm
that uses the PowerPoint template to create a new .pptx file with
given data using VBA.
The report we want to automate is…

…and the Excel and PowerPoint template we created are shown in Figure 2.

In this post, we will write the R script that will first massage the data into desired format and then load the data for one region into the Excel template and execute the VBA macro that will create the PowerPoint file with that data.
Strategy
Before we dive into code, we need to check a few things. We wish to create a presentation for each continent in the Gapminder data. A closer look at the Presentation will tell you what kind of data we need for each slide/graph/table while the Excel template will reveal what should the structure of each dataset should be. While looking into this structure, some questions will pop-up. The idea here is to create the datasets in such a way that they can be easily filtered for each continent and the resultant table can be written to the Excel template without any or very little modification. Let us proceed slide-by-slide.
Creating the datasets
Slide 1
Slide 1 is the title page and needs 2 strings; one for Title, one for Subtitle. The Title for the base presentation is “World Population”. For each continent, it could be “<continent_name> Population”. The subtitle is a combination of Author Name and Created Date. So we need a string like “<author_name> | <created_date>” where created_date is the formatted system date.
These strings can be created while writing the data to the Excel template.
Slide 2
The chart on slide 2 needs raw data structured as below. You will notice
that at a continent-level, this table needs a minimum of 5 countries. Do
we have any continents in the Gapminder data with less than 5 countries?
Yes, we have Oceania with only Australia and New Zealand. For ease of
use, let us include these countries along with Asian countries in a new
Region variable.

We will create the region variable in the gapminder data. But first,
let us load some relevant packages.
options(tidyverse.quiet = TRUE)
library(tidyverse) # duh!
library(rmarkdown) # to display the tables interactively in this post. Not really needed for the final solution.
library(openxlsx) # to write the data to the Excel Template.
# library(RDCOMClient) # to load and run the Excel macro post data load.# Read in Gapminder data
gp <- gapminder::gapminder
# Create new region variable
gp <- gp %>%
mutate(region = if_else(as.character(continent) %in% c("Asia","Oceania"),
"Asia-Pacific",
as.character(continent)),
country = as.character(country))
# Keep only relevant columns
gp <- gp %>% select(region, country, year, pop)
# View details
glimpse(gp)Rows: 1,704
Columns: 4
$ region <chr> "Asia-Pacific", "Asia-Pacific", "Asia-Pacific", "Asia-Pacific"~
$ country <chr> "Afghanistan", "Afghanistan", "Afghanistan", "Afghanistan", "A~
$ year <int> 1952, 1957, 1962, 1967, 1972, 1977, 1982, 1987, 1992, 1997, 20~
$ pop <int> 8425333, 9240934, 10267083, 11537966, 13079460, 14880372, 1288~Now that we have the source data available, we must now create the datasets we need that we can write to the Excel template for each region.
The required table in Figure 3 shows the top 4 countries (based on 2007 population) and all other countries clubbed into ‘others’ in a given region and then the total population of the region on a yearly basis. This table has to be created for all 4 regions.
pop_trend <- gp %>%
group_by(region, country, year) %>%
summarise(pop = sum(pop, na.rm = TRUE),
.groups = 'drop') %>%
mutate(pop = round(pop/1E6, 0)) %>% # population in millions
pivot_wider(names_from = year, values_from = pop, names_sort = TRUE) %>%
arrange(desc(`2007`)) # sort by max pop to min pop in latest year i.e. 2007
paged_table(pop_trend)| regionchr | countrychr | 1952dbl | 1957dbl | 1962dbl | 1967dbl | 1972dbl | 1977dbl | 1982dbl | 1987dbl | 1992dbl | 1997dbl | 2002dbl | 2007dbl |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Asia-Pacific | China | 556 | 637 | 666 | 755 | 862 | 943 | 1000 | 1084 | 1165 | 1230 | 1280 | 1319 |
| Asia-Pacific | India | 372 | 409 | 454 | 506 | 567 | 634 | 708 | 788 | 872 | 959 | 1034 | 1110 |
| Americas | United States | 158 | 172 | 187 | 199 | 210 | 220 | 232 | 243 | 257 | 273 | 288 | 301 |
| Asia-Pacific | Indonesia | 82 | 90 | 99 | 109 | 121 | 137 | 153 | 169 | 185 | 199 | 211 | 224 |
| Americas | Brazil | 57 | 66 | 76 | 88 | 101 | 114 | 129 | 143 | 156 | 169 | 180 | 190 |
| Asia-Pacific | Pakistan | 41 | 47 | 53 | 61 | 69 | 78 | 91 | 105 | 120 | 136 | 153 | 169 |
| Asia-Pacific | Bangladesh | 47 | 51 | 57 | 63 | 71 | 80 | 93 | 104 | 114 | 123 | 136 | 150 |
| Africa | Nigeria | 33 | 37 | 42 | 47 | 54 | 62 | 73 | 82 | 93 | 106 | 120 | 135 |
| Asia-Pacific | Japan | 86 | 92 | 96 | 101 | 107 | 114 | 118 | 122 | 124 | 126 | 127 | 127 |
| Americas | Mexico | 30 | 35 | 41 | 48 | 56 | 64 | 72 | 80 | 88 | 96 | 102 | 109 |
| Asia-Pacific | Philippines | 22 | 26 | 30 | 35 | 41 | 47 | 53 | 60 | 67 | 75 | 83 | 91 |
| Asia-Pacific | Vietnam | 26 | 29 | 34 | 39 | 45 | 51 | 56 | 63 | 70 | 76 | 81 | 85 |
| Europe | Germany | 69 | 71 | 74 | 76 | 79 | 78 | 78 | 78 | 81 | 82 | 82 | 82 |
| Africa | Egypt | 22 | 25 | 28 | 32 | 35 | 39 | 46 | 53 | 59 | 66 | 73 | 80 |
| Africa | Ethiopia | 21 | 23 | 25 | 28 | 31 | 35 | 38 | 43 | 52 | 60 | 68 | 77 |
| Europe | Turkey | 22 | 26 | 30 | 33 | 37 | 42 | 47 | 53 | 58 | 63 | 67 | 71 |
| Asia-Pacific | Iran | 17 | 20 | 23 | 27 | 31 | 35 | 43 | 52 | 60 | 63 | 67 | 69 |
| Africa | Congo, Dem. Rep. | 14 | 16 | 17 | 20 | 23 | 26 | 31 | 35 | 42 | 48 | 55 | 65 |
| Asia-Pacific | Thailand | 21 | 25 | 29 | 34 | 39 | 44 | 49 | 53 | 57 | 60 | 63 | 65 |
| Europe | France | 42 | 44 | 47 | 50 | 52 | 53 | 54 | 56 | 57 | 59 | 60 | 61 |
| Europe | United Kingdom | 50 | 51 | 53 | 55 | 56 | 56 | 56 | 57 | 58 | 59 | 60 | 61 |
| Europe | Italy | 48 | 49 | 51 | 53 | 54 | 56 | 57 | 57 | 57 | 57 | 58 | 58 |
| Asia-Pacific | Korea, Rep. | 21 | 23 | 26 | 30 | 34 | 36 | 39 | 42 | 44 | 46 | 48 | 49 |
| Asia-Pacific | Myanmar | 20 | 22 | 24 | 26 | 28 | 32 | 35 | 38 | 41 | 43 | 46 | 48 |
| Africa | South Africa | 14 | 16 | 18 | 21 | 24 | 27 | 31 | 36 | 40 | 43 | 44 | 44 |
| Americas | Colombia | 12 | 14 | 17 | 20 | 23 | 25 | 28 | 31 | 34 | 38 | 41 | 44 |
| Africa | Sudan | 9 | 10 | 11 | 13 | 15 | 17 | 20 | 25 | 28 | 32 | 37 | 42 |
| Americas | Argentina | 18 | 20 | 21 | 23 | 25 | 27 | 29 | 32 | 34 | 36 | 38 | 40 |
| Europe | Spain | 29 | 30 | 31 | 33 | 35 | 36 | 38 | 39 | 40 | 40 | 40 | 40 |
| Europe | Poland | 26 | 28 | 30 | 32 | 33 | 35 | 36 | 38 | 38 | 39 | 39 | 39 |
| Africa | Tanzania | 8 | 9 | 11 | 13 | 15 | 17 | 20 | 23 | 27 | 31 | 35 | 38 |
| Africa | Kenya | 6 | 7 | 9 | 10 | 12 | 15 | 18 | 21 | 25 | 28 | 31 | 36 |
| Africa | Morocco | 10 | 11 | 13 | 15 | 17 | 18 | 20 | 23 | 26 | 29 | 31 | 34 |
| Africa | Algeria | 9 | 10 | 11 | 13 | 15 | 17 | 20 | 23 | 26 | 29 | 31 | 33 |
| Americas | Canada | 15 | 17 | 19 | 21 | 22 | 24 | 25 | 27 | 29 | 30 | 32 | 33 |
| Asia-Pacific | Afghanistan | 8 | 9 | 10 | 12 | 13 | 15 | 13 | 14 | 16 | 22 | 25 | 32 |
| Africa | Uganda | 6 | 7 | 8 | 9 | 10 | 11 | 13 | 15 | 18 | 21 | 25 | 29 |
| Americas | Peru | 8 | 9 | 11 | 12 | 14 | 16 | 18 | 20 | 22 | 25 | 27 | 29 |
| Asia-Pacific | Nepal | 9 | 10 | 10 | 11 | 12 | 14 | 16 | 18 | 20 | 23 | 26 | 29 |
| Asia-Pacific | Saudi Arabia | 4 | 4 | 5 | 6 | 6 | 8 | 11 | 15 | 17 | 21 | 25 | 28 |
| Asia-Pacific | Iraq | 5 | 6 | 7 | 9 | 10 | 12 | 14 | 17 | 18 | 21 | 24 | 27 |
| Americas | Venezuela | 5 | 7 | 8 | 10 | 12 | 14 | 16 | 18 | 20 | 22 | 24 | 26 |
| Asia-Pacific | Malaysia | 7 | 8 | 9 | 10 | 11 | 13 | 14 | 16 | 18 | 20 | 23 | 25 |
| Africa | Ghana | 6 | 6 | 7 | 8 | 9 | 11 | 11 | 14 | 16 | 18 | 21 | 23 |
| Asia-Pacific | Korea, Dem. Rep. | 9 | 9 | 11 | 13 | 15 | 16 | 18 | 19 | 21 | 22 | 22 | 23 |
| Asia-Pacific | Taiwan | 9 | 10 | 12 | 14 | 15 | 17 | 19 | 20 | 21 | 22 | 22 | 23 |
| Asia-Pacific | Yemen, Rep. | 5 | 5 | 6 | 7 | 7 | 8 | 10 | 11 | 13 | 16 | 19 | 22 |
| Europe | Romania | 17 | 18 | 19 | 19 | 21 | 22 | 22 | 23 | 23 | 23 | 22 | 22 |
| Africa | Mozambique | 6 | 7 | 8 | 9 | 10 | 11 | 13 | 13 | 13 | 17 | 18 | 20 |
| Asia-Pacific | Australia | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 19 | 20 | 20 |
| Asia-Pacific | Sri Lanka | 8 | 9 | 10 | 12 | 13 | 14 | 15 | 16 | 18 | 19 | 20 | 20 |
| Africa | Madagascar | 5 | 5 | 6 | 6 | 7 | 8 | 9 | 11 | 12 | 14 | 16 | 19 |
| Asia-Pacific | Syria | 4 | 4 | 5 | 6 | 7 | 8 | 9 | 11 | 13 | 15 | 17 | 19 |
| Africa | Cameroon | 5 | 5 | 6 | 6 | 7 | 8 | 9 | 11 | 12 | 14 | 16 | 18 |
| Africa | Cote d'Ivoire | 3 | 3 | 4 | 5 | 6 | 7 | 9 | 11 | 13 | 15 | 16 | 18 |
| Europe | Netherlands | 10 | 11 | 12 | 13 | 13 | 14 | 14 | 15 | 15 | 16 | 16 | 17 |
| Americas | Chile | 6 | 7 | 8 | 9 | 10 | 11 | 11 | 12 | 14 | 15 | 15 | 16 |
| Africa | Burkina Faso | 4 | 5 | 5 | 5 | 5 | 6 | 7 | 8 | 9 | 10 | 12 | 14 |
| Americas | Ecuador | 4 | 4 | 5 | 5 | 6 | 7 | 8 | 10 | 11 | 12 | 13 | 14 |
| Asia-Pacific | Cambodia | 5 | 5 | 6 | 7 | 7 | 7 | 7 | 8 | 10 | 12 | 13 | 14 |
| Africa | Malawi | 3 | 3 | 4 | 4 | 5 | 6 | 7 | 8 | 10 | 10 | 12 | 13 |
| Africa | Niger | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 8 | 10 | 11 | 13 |
| Americas | Guatemala | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 8 | 10 | 11 | 13 |
| Africa | Angola | 4 | 5 | 5 | 5 | 6 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| Africa | Mali | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 8 | 8 | 9 | 11 | 12 |
| Africa | Senegal | 3 | 3 | 3 | 4 | 5 | 5 | 6 | 7 | 8 | 10 | 11 | 12 |
| Africa | Zambia | 3 | 3 | 3 | 4 | 5 | 5 | 6 | 7 | 8 | 9 | 11 | 12 |
| Africa | Zimbabwe | 3 | 4 | 4 | 5 | 6 | 7 | 8 | 9 | 11 | 11 | 12 | 12 |
| Americas | Cuba | 6 | 7 | 7 | 8 | 9 | 10 | 10 | 10 | 11 | 11 | 11 | 11 |
| Europe | Greece | 8 | 8 | 8 | 9 | 9 | 9 | 10 | 10 | 10 | 11 | 11 | 11 |
| Europe | Portugal | 9 | 9 | 9 | 9 | 9 | 10 | 10 | 10 | 10 | 10 | 10 | 11 |
| Africa | Chad | 3 | 3 | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 8 | 9 | 10 |
| Africa | Guinea | 3 | 3 | 3 | 3 | 4 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
| Africa | Tunisia | 4 | 4 | 4 | 5 | 5 | 6 | 7 | 8 | 9 | 9 | 10 | 10 |
| Europe | Belgium | 9 | 9 | 9 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| Europe | Czech Republic | 9 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 |
| Europe | Hungary | 10 | 10 | 10 | 10 | 10 | 11 | 11 | 11 | 10 | 10 | 10 | 10 |
| Europe | Serbia | 7 | 7 | 8 | 8 | 8 | 9 | 9 | 9 | 10 | 10 | 10 | 10 |
| Africa | Rwanda | 3 | 3 | 3 | 3 | 4 | 5 | 6 | 6 | 7 | 7 | 8 | 9 |
| Africa | Somalia | 3 | 3 | 3 | 3 | 4 | 4 | 6 | 7 | 6 | 7 | 8 | 9 |
| Americas | Bolivia | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 8 | 8 | 9 |
| Americas | Dominican Republic | 2 | 3 | 3 | 4 | 5 | 5 | 6 | 7 | 7 | 8 | 9 | 9 |
| Americas | Haiti | 3 | 4 | 4 | 4 | 5 | 5 | 5 | 6 | 6 | 7 | 8 | 9 |
| Europe | Sweden | 7 | 7 | 8 | 8 | 8 | 8 | 8 | 8 | 9 | 9 | 9 | 9 |
| Africa | Benin | 2 | 2 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 6 | 7 | 8 |
| Africa | Burundi | 2 | 3 | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 7 | 8 |
| Europe | Austria | 7 | 7 | 7 | 7 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 8 |
| Europe | Switzerland | 5 | 5 | 6 | 6 | 6 | 6 | 6 | 7 | 7 | 7 | 7 | 8 |
| Americas | El Salvador | 2 | 2 | 3 | 3 | 4 | 4 | 4 | 5 | 5 | 6 | 6 | 7 |
| Americas | Honduras | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 4 | 5 | 6 | 7 | 7 |
| Americas | Paraguay | 2 | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 4 | 5 | 6 | 7 |
| Asia-Pacific | Hong Kong, China | 2 | 3 | 3 | 4 | 4 | 5 | 5 | 6 | 6 | 6 | 7 | 7 |
| Europe | Bulgaria | 7 | 8 | 8 | 8 | 9 | 9 | 9 | 9 | 9 | 8 | 8 | 7 |
| Africa | Libya | 1 | 1 | 1 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 5 | 6 |
| Africa | Sierra Leone | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 4 | 4 | 5 | 5 | 6 |
| Africa | Togo | 1 | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 4 | 4 | 5 | 6 |
| Americas | Nicaragua | 1 | 1 | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 5 | 5 | 6 |
| Asia-Pacific | Israel | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 4 | 5 | 6 | 6 | 6 |
| Asia-Pacific | Jordan | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 3 | 4 | 5 | 5 | 6 |
| Africa | Eritrea | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 4 | 4 | 5 |
| Asia-Pacific | Singapore | 1 | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 3 | 4 | 4 | 5 |
| Europe | Bosnia and Herzegovina | 3 | 3 | 3 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 5 |
| Europe | Denmark | 4 | 4 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Europe | Finland | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Europe | Norway | 3 | 3 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 5 | 5 |
| Europe | Slovak Republic | 4 | 4 | 4 | 4 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 5 |
| Africa | Central African Republic | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 3 | 3 | 4 | 4 | 4 |
| Africa | Congo, Rep. | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 4 |
| Americas | Costa Rica | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 3 | 3 | 4 | 4 | 4 |
| Americas | Puerto Rico | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 4 | 4 |
| Asia-Pacific | Lebanon | 1 | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 |
| Asia-Pacific | New Zealand | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 4 |
| Asia-Pacific | West Bank and Gaza | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 3 | 3 | 4 |
| Europe | Albania | 1 | 1 | 2 | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 4 | 4 |
| Europe | Croatia | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 |
| Europe | Ireland | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 4 | 4 | 4 | 4 | 4 |
| Africa | Liberia | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 3 | 3 |
| Africa | Mauritania | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 3 | 3 |
| Americas | Jamaica | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 3 | 3 | 3 |
| Americas | Panama | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 3 | 3 | 3 |
| Americas | Uruguay | 2 | 2 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 |
| Asia-Pacific | Kuwait | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 2 | 1 | 2 | 2 | 3 |
| Asia-Pacific | Mongolia | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 | 3 | 3 |
| Asia-Pacific | Oman | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 3 | 3 |
| Africa | Botswana | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 |
| Africa | Gambia | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 |
| Africa | Lesotho | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 | 2 |
| Africa | Namibia | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 2 | 2 | 2 |
| Europe | Slovenia | 1 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 |
| Africa | Comoros | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 |
| Africa | Equatorial Guinea | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
| Africa | Gabon | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Africa | Guinea-Bissau | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Africa | Mauritius | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Africa | Reunion | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 |
| Africa | Swaziland | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Americas | Trinidad and Tobago | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Asia-Pacific | Bahrain | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
| Europe | Montenegro | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Africa | Djibouti | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Africa | Sao Tome and Principe | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| Europe | Iceland | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Now that we have the required columns, let’s plan the row order. We
notice that, for each region, we have the top 4 countries (as per 2007)
, followed by ‘Others’. Let’s create the top-4 dataset.
top4 <- pop_trend %>%
group_by(region) %>%
slice_max(`2007`, n = 4, with_ties = FALSE) %>%
ungroup()
paged_table(top4)| regionchr | countrychr | 1952dbl | 1957dbl | 1962dbl | 1967dbl | 1972dbl | 1977dbl | 1982dbl | 1987dbl | 1992dbl | 1997dbl | 2002dbl | 2007dbl |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Africa | Nigeria | 33 | 37 | 42 | 47 | 54 | 62 | 73 | 82 | 93 | 106 | 120 | 135 |
| Africa | Egypt | 22 | 25 | 28 | 32 | 35 | 39 | 46 | 53 | 59 | 66 | 73 | 80 |
| Africa | Ethiopia | 21 | 23 | 25 | 28 | 31 | 35 | 38 | 43 | 52 | 60 | 68 | 77 |
| Africa | Congo, Dem. Rep. | 14 | 16 | 17 | 20 | 23 | 26 | 31 | 35 | 42 | 48 | 55 | 65 |
| Americas | United States | 158 | 172 | 187 | 199 | 210 | 220 | 232 | 243 | 257 | 273 | 288 | 301 |
| Americas | Brazil | 57 | 66 | 76 | 88 | 101 | 114 | 129 | 143 | 156 | 169 | 180 | 190 |
| Americas | Mexico | 30 | 35 | 41 | 48 | 56 | 64 | 72 | 80 | 88 | 96 | 102 | 109 |
| Americas | Colombia | 12 | 14 | 17 | 20 | 23 | 25 | 28 | 31 | 34 | 38 | 41 | 44 |
| Asia-Pacific | China | 556 | 637 | 666 | 755 | 862 | 943 | 1000 | 1084 | 1165 | 1230 | 1280 | 1319 |
| Asia-Pacific | India | 372 | 409 | 454 | 506 | 567 | 634 | 708 | 788 | 872 | 959 | 1034 | 1110 |
| Asia-Pacific | Indonesia | 82 | 90 | 99 | 109 | 121 | 137 | 153 | 169 | 185 | 199 | 211 | 224 |
| Asia-Pacific | Pakistan | 41 | 47 | 53 | 61 | 69 | 78 | 91 | 105 | 120 | 136 | 153 | 169 |
| Europe | Germany | 69 | 71 | 74 | 76 | 79 | 78 | 78 | 78 | 81 | 82 | 82 | 82 |
| Europe | Turkey | 22 | 26 | 30 | 33 | 37 | 42 | 47 | 53 | 58 | 63 | 67 | 71 |
| Europe | France | 42 | 44 | 47 | 50 | 52 | 53 | 54 | 56 | 57 | 59 | 60 | 61 |
| Europe | United Kingdom | 50 | 51 | 53 | 55 | 56 | 56 | 56 | 57 | 58 | 59 | 60 | 61 |
To create the others dataset, we exclude all countries that are
present in the top-4.
others <- pop_trend %>%
filter(!country %in% top4$country) %>%
group_by(region) %>%
summarise(across(.cols = -country, .fns = sum),
.groups = 'drop') %>%
mutate(country = "Others") %>%
select(region, country, everything())
paged_table(others)| regionchr | countrychr | 1952dbl | 1957dbl | 1962dbl | 1967dbl | 1972dbl | 1977dbl | 1982dbl | 1987dbl | 1992dbl | 1997dbl | 2002dbl | 2007dbl |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Africa | Others | 146 | 162 | 183 | 206 | 239 | 270 | 314 | 362 | 409 | 463 | 515 | 570 |
| Americas | Others | 88 | 101 | 113 | 127 | 143 | 156 | 167 | 185 | 202 | 224 | 238 | 254 |
| Asia-Pacific | Others | 354 | 391 | 436 | 493 | 546 | 608 | 672 | 745 | 811 | 882 | 949 | 1012 |
| Europe | Others | 235 | 244 | 257 | 269 | 278 | 289 | 294 | 301 | 303 | 305 | 307 | 310 |
While we create the top-4 and others datasets separately, we will
combine them later at the very last moment before writing them to the
Excel template.
Now that we have the datasets needed for 02_chart, let’s proceed to
the create 02_table . This table gives you the count of countries that
fall under various population ranges.

Let’s create 02_table. To create this table, we first create a new
variable called pop_range.
pop_levels <- c('Less than 500K','500K - 1 Million',
'1M - 10 Million', '10M - 100 Million',
'100M - 1 Billion', 'More than 1 Billion')
gp2007 <- gp %>%
filter(year == 2007) %>%
mutate(pop_range = case_when(pop < 5E5 ~ pop_levels[1],
pop < 1E6 ~ pop_levels[2],
pop < 1E7 ~ pop_levels[3],
pop < 1E8 ~ pop_levels[4],
pop < 1E9 ~ pop_levels[5],
TRUE ~ pop_levels[6]),
pop_range = factor(pop_range, levels = pop_levels))
pop_groups <- gp2007 %>%
group_by(region, pop_range, .drop = FALSE) %>%
summarise(`# of Countries` = n(),
.groups = 'drop') %>%
arrange(region, pop_range) %>%
rename(`Population Category` = pop_range)
paged_table(pop_groups)| regionchr | Population Categoryfct | # of Countriesint |
|---|---|---|
| Africa | Less than 500K | 2 |
| Africa | 500K - 1 Million | 3 |
| Africa | 1M - 10 Million | 21 |
| Africa | 10M - 100 Million | 25 |
| Africa | 100M - 1 Billion | 1 |
| Africa | More than 1 Billion | 0 |
| Americas | Less than 500K | 0 |
| Americas | 500K - 1 Million | 0 |
| Americas | 1M - 10 Million | 13 |
| Americas | 10M - 100 Million | 9 |
| Americas | 100M - 1 Billion | 3 |
| Americas | More than 1 Billion | 0 |
| Asia-Pacific | Less than 500K | 0 |
| Asia-Pacific | 500K - 1 Million | 1 |
| Asia-Pacific | 1M - 10 Million | 10 |
| Asia-Pacific | 10M - 100 Million | 18 |
| Asia-Pacific | 100M - 1 Billion | 4 |
| Asia-Pacific | More than 1 Billion | 2 |
| Europe | Less than 500K | 1 |
| Europe | 500K - 1 Million | 1 |
| Europe | 1M - 10 Million | 14 |
| Europe | 10M - 100 Million | 14 |
| Europe | 100M - 1 Billion | 0 |
| Europe | More than 1 Billion | 0 |
Slide 3
Slide 3 contains 2 strings and one chart. The data for the chart looks as shown below.

The data for 03_chart is the list of top 10 countries in each region
as per latest record i.e. 2007. Let’s create the top10 table.
top10 <- gp %>%
filter(year == 2007) %>%
group_by(region) %>%
slice_max(pop, n = 10, with_ties = FALSE) %>%
ungroup() %>%
select(-year) %>%
mutate(pop = round(pop/1E6, 4)) %>% # population in millions
set_names(c("region","country","population"))
paged_table(top10)| regionchr | countrychr | populationdbl |
|---|---|---|
| Africa | Nigeria | 135.0312 |
| Africa | Egypt | 80.2645 |
| Africa | Ethiopia | 76.5119 |
| Africa | Congo, Dem. Rep. | 64.6068 |
| Africa | South Africa | 43.9978 |
| Africa | Sudan | 42.2929 |
| Africa | Tanzania | 38.1396 |
| Africa | Kenya | 35.6102 |
| Africa | Morocco | 33.7572 |
| Africa | Algeria | 33.3332 |
| Americas | United States | 301.1399 |
| Americas | Brazil | 190.0106 |
| Americas | Mexico | 108.7009 |
| Americas | Colombia | 44.2276 |
| Americas | Argentina | 40.3019 |
| Americas | Canada | 33.3901 |
| Americas | Peru | 28.6748 |
| Americas | Venezuela | 26.0847 |
| Americas | Chile | 16.2847 |
| Americas | Ecuador | 13.7557 |
| Asia-Pacific | China | 1318.6831 |
| Asia-Pacific | India | 1110.3963 |
| Asia-Pacific | Indonesia | 223.5470 |
| Asia-Pacific | Pakistan | 169.2706 |
| Asia-Pacific | Bangladesh | 150.4483 |
| Asia-Pacific | Japan | 127.4680 |
| Asia-Pacific | Philippines | 91.0773 |
| Asia-Pacific | Vietnam | 85.2624 |
| Asia-Pacific | Iran | 69.4536 |
| Asia-Pacific | Thailand | 65.0681 |
| Europe | Germany | 82.4010 |
| Europe | Turkey | 71.1586 |
| Europe | France | 61.0839 |
| Europe | United Kingdom | 60.7762 |
| Europe | Italy | 58.1477 |
| Europe | Spain | 40.4482 |
| Europe | Poland | 38.5182 |
| Europe | Romania | 22.2761 |
| Europe | Netherlands | 16.5706 |
| Europe | Greece | 10.7063 |
The for loop!
We now have to load the Excel template with the data at appropriate cell locations for one region at a time. Since we have about 4 regions, we will create a vector of unique regions to iterate over.
unique_regions <- gp %>% distinct(region) %>% pull()
cat(unique_regions, sep = "\n")Asia-Pacific
Europe
Africa
AmericasAs our last step, we will create the for loop that will iterate over
unique_regions , filter the datasets for each region, write them to
the Excel Template, save the template with temporary name. We save the
file with different name to prevent unintentionally corrupting the Excel
macro template. Finally, we run the macro in the renamed file.
The code will look something like this
for (region in unique_regions) {
# Step 1: filter the data sets
# Step 2: write the data sets
# Step 3: save the excel template with different name
# Step 4: load the renamed Excel file
# Step 5: run macro
}Let’s populate the above for loop with the code we need.
for (curr_region in unique_regions) {
# Step 1: filter the data sets
# Slide 1
S1_title <- paste(curr_region, "Population")
S1_subtitle <- paste("Vishal Katti","|",format(Sys.Date(),"%b %d, %Y"), sep = " ")
# Slide 2
S2_title <- paste(curr_region, "Population since 1952")
S2_top4 <- top4 %>% filter(region == all_of(curr_region)) %>% select(-region) %>% arrange(desc(`2007`))
S2_others <- others %>% filter(region == all_of(curr_region)) %>% select(-region)
S2_top5 <- bind_rows(S2_top4, S2_others)
S2_table <- pop_groups %>% filter(region == all_of(curr_region)) %>% select(-region)
# Slide 3
S3_title <- paste("Top 10 most populated countries in", curr_region)
S3_chart <- top10 %>% filter(region == all_of(curr_region)) %>% select(-region)
S3_factoid <- paste("The population of", S3_chart$country[1], "is approx.",
round(S3_chart$population[1]/S3_chart$population[10], 0),
"times that of", S3_chart$country[10])
# Step 2: write the data sets
# Load the template
wb <- loadWorkbook("path/to/template/XL2PPT.xlsm") # relative to this R script
sht <- "Sheet1"
# write data to coordinate (col, row)
writeData(wb, sht, S1_title, xy = c(3, 3), colNames = FALSE)
writeData(wb, sht, S1_subtitle, xy = c(3, 4), colNames = FALSE)
writeData(wb, sht, S2_title, xy = c(3, 7), colNames = FALSE)
writeData(wb, sht, S2_top5, xy = c(3, 9), colNames = TRUE)
writeData(wb, sht, S2_table, xy = c(18, 9), colNames = TRUE)
writeData(wb, sht, S3_title, xy = c(3, 18), colNames = FALSE)
writeData(wb, sht, S3_factoid, xy = c(3, 19), colNames = FALSE)
writeData(wb, sht, S3_chart, xy = c(3, 21), colNames = TRUE)
# Step 3: save the excel template with different name
saveWorkbook(wb, "path/to/template/XL2PPT_edited.xlsm", overwrite = TRUE)
gc(verbose = TRUE)
Sys.sleep(2)
# Step 4: load the renamed Excel file
# Create Excel Application
xlApp <- COMCreate("Excel.Application")
# Open the Macro Excel book
xlWbk <- xlApp$Workbooks()$Open(normalizePath("path/to/template/XL2PPT_edited.xlsm", winslash = "/")) # Change to your directory
# its ok to run macro without visible excel application
# If you want to see your workbook, please set it to TRUE
xlApp[["Visible"]] <- FALSE
# Step 5: run macro
xlApp$Run("Create_Continental_Deck") # Name of Macro to run
xlWbk$Close(TRUE) # save and close excel book
xlApp$Quit()
gc(verbose = TRUE)
Sys.sleep(2)
}Once the code runs completely, you will see 4 new PowerPoint Presentations in your working folder.

You can download the full R script from here.
References & Citations
-
Jennifer Bryan (2017). gapminder: Data from Gapminder. R package version 0.3.0. https://CRAN.R-project.org/package=gapminder
-
Hadley Wickham, Romain Francois, Lionel Henry and Kirill Muller (2021). dplyr: A Grammar of Data Manipulation. R package version 1.0.7. https://CRAN.R-project.org/package=dplyr
-
Hadley Wickham (2021). tidyr: Tidy Messy Data. R package version 1.1.3. https://CRAN.R-project.org/package=tidyr
-
Greg Lin (2020). reactable: Interactive Data Tables Based on ‘React Table’. R package version 0.2.3. https://CRAN.R-project.org/package=reactable
-
Philipp Schauberger and Alexander Walker (2021). openxlsx: Read, Write and Edit xlsx Files. R package version 4.2.4. https://CRAN.R-project.org/package=openxlsx
-
Duncan Temple Lang (NA). RDCOMClient: R-DCOM client. http://www.omegahat.net/RDCOMClient, http://www.omegahat.net http://www.omegahat.net/bugs.
-
https://docs.microsoft.com/en-us/office/vba/api/overview/excel
-
https://docs.microsoft.com/en-us/office/vba/api/overview/powerpoint