All posts

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…

The Gapminder Report : The PowerPoint presentation we want to automate
Figure 1: The Gapminder Report : The PowerPoint presentation we want to automate

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

Excel Template with VBA macro
Figure 2: Excel Template with VBA macro

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.

02_chart
Figure 3: 02_chart

We will create the region variable in the gapminder data. But first, let us load some relevant packages.

R
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.
R
# 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)
Output
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.

R
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)
regionchrcountrychr1952dbl1957dbl1962dbl1967dbl1972dbl1977dbl1982dbl1987dbl1992dbl1997dbl2002dbl2007dbl
Asia-PacificChina556637666755862943100010841165123012801319
Asia-PacificIndia37240945450656763470878887295910341110
AmericasUnited States158172187199210220232243257273288301
Asia-PacificIndonesia829099109121137153169185199211224
AmericasBrazil57667688101114129143156169180190
Asia-PacificPakistan41475361697891105120136153169
Asia-PacificBangladesh47515763718093104114123136150
AfricaNigeria333742475462738293106120135
Asia-PacificJapan869296101107114118122124126127127
AmericasMexico30354148566472808896102109
Asia-PacificPhilippines222630354147536067758391
Asia-PacificVietnam262934394551566370768185
EuropeGermany697174767978787881828282
AfricaEgypt222528323539465359667380
AfricaEthiopia212325283135384352606877
EuropeTurkey222630333742475358636771
Asia-PacificIran172023273135435260636769
AfricaCongo, Dem. Rep.141617202326313542485565
Asia-PacificThailand212529343944495357606365
EuropeFrance424447505253545657596061
EuropeUnited Kingdom505153555656565758596061
EuropeItaly484951535456575757575858
Asia-PacificKorea, Rep.212326303436394244464849
Asia-PacificMyanmar202224262832353841434648
AfricaSouth Africa141618212427313640434444
AmericasColombia121417202325283134384144
AfricaSudan91011131517202528323742
AmericasArgentina182021232527293234363840
EuropeSpain293031333536383940404040
EuropePoland262830323335363838393939
AfricaTanzania8911131517202327313538
AfricaKenya679101215182125283136
AfricaMorocco101113151718202326293134
AfricaAlgeria91011131517202326293133
AmericasCanada151719212224252729303233
Asia-PacificAfghanistan8910121315131416222532
AfricaUganda67891011131518212529
AmericasPeru8911121416182022252729
Asia-PacificNepal91010111214161820232629
Asia-PacificSaudi Arabia445668111517212528
Asia-PacificIraq56791012141718212427
AmericasVenezuela578101214161820222426
Asia-PacificMalaysia789101113141618202325
AfricaGhana6678911111416182123
Asia-PacificKorea, Dem. Rep.9911131516181921222223
Asia-PacificTaiwan91012141517192021222223
Asia-PacificYemen, Rep.556778101113161922
EuropeRomania171819192122222323232222
AfricaMozambique67891011131313171820
Asia-PacificAustralia91011121314151617192020
Asia-PacificSri Lanka8910121314151618192020
AfricaMadagascar55667891112141619
Asia-PacificSyria44567891113151719
AfricaCameroon55667891112141618
AfricaCote d'Ivoire33456791113151618
EuropeNetherlands101112131314141515161617
AmericasChile67891011111214151516
AfricaBurkina Faso455556789101214
AmericasEcuador44556781011121314
Asia-PacificCambodia5567777810121314
AfricaMalawi3344567810101213
AfricaNiger344556678101113
AmericasGuatemala344556678101113
AfricaAngola455566789101112
AfricaMali44556678891112
AfricaSenegal333455678101112
AfricaZambia33345567891112
AfricaZimbabwe3445678911111212
AmericasCuba6778910101011111111
EuropeGreece888999101010111111
EuropePortugal9999910101010101011
AfricaChad3333445568910
AfricaGuinea3333445678910
AfricaTunisia44455678991010
EuropeBelgium999101010101010101010
EuropeCzech Republic91010101010101010101010
EuropeHungary101010101011111110101010
EuropeSerbia7788899910101010
AfricaRwanda333345667789
AfricaSomalia333344676789
AmericasBolivia334455667889
AmericasDominican Republic233455677899
AmericasHaiti344455566789
EuropeSweden778888889999
AfricaBenin222233445678
AfricaBurundi233344556678
EuropeAustria777788888888
EuropeSwitzerland556666677778
AmericasEl Salvador223344455667
AmericasHonduras222333445677
AmericasParaguay222233344567
Asia-PacificHong Kong, China233445566677
EuropeBulgaria788899999887
AfricaLibya111223344556
AfricaSierra Leone222333344556
AfricaTogo112222334456
AmericasNicaragua112223334556
Asia-PacificIsrael222333445666
Asia-PacificJordan111122234556
AfricaEritrea122223334445
Asia-PacificSingapore112222333445
EuropeBosnia and Herzegovina333444444445
EuropeDenmark445555555555
EuropeFinland444555555555
EuropeNorway334444444455
EuropeSlovak Republic444455555555
AfricaCentral African Republic112222233444
AfricaCongo, Rep.111112222334
AmericasCosta Rica111222233444
AmericasPuerto Rico222333334444
Asia-PacificLebanon122233333344
Asia-PacificNew Zealand222333333444
Asia-PacificWest Bank and Gaza111111122334
EuropeAlbania112223333344
EuropeCroatia444444444444
EuropeIreland333333344444
AfricaLiberia111112222233
AfricaMauritania111111222233
AmericasJamaica122222222333
AmericasPanama111122222333
AmericasUruguay223333333333
Asia-PacificKuwait000111121223
Asia-PacificMongolia111112222233
Asia-PacificOman111111122233
AfricaBotswana001111111222
AfricaGambia000011111112
AfricaLesotho111111122222
AfricaNamibia011111112222
EuropeSlovenia122222222222
AfricaComoros000000000111
AfricaEquatorial Guinea000000000001
AfricaGabon000011111111
AfricaGuinea-Bissau111111111111
AfricaMauritius111111111111
AfricaReunion000000111111
AfricaSwaziland000001111111
AmericasTrinidad and Tobago111111111111
Asia-PacificBahrain000000001111
EuropeMontenegro000111111111
AfricaDjibouti000000000000
AfricaSao Tome and Principe000000000000
EuropeIceland000000000000

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.

R
top4 <- pop_trend %>% 
  group_by(region) %>% 
  slice_max(`2007`, n = 4, with_ties = FALSE) %>% 
  ungroup()

paged_table(top4)
regionchrcountrychr1952dbl1957dbl1962dbl1967dbl1972dbl1977dbl1982dbl1987dbl1992dbl1997dbl2002dbl2007dbl
AfricaNigeria333742475462738293106120135
AfricaEgypt222528323539465359667380
AfricaEthiopia212325283135384352606877
AfricaCongo, Dem. Rep.141617202326313542485565
AmericasUnited States158172187199210220232243257273288301
AmericasBrazil57667688101114129143156169180190
AmericasMexico30354148566472808896102109
AmericasColombia121417202325283134384144
Asia-PacificChina556637666755862943100010841165123012801319
Asia-PacificIndia37240945450656763470878887295910341110
Asia-PacificIndonesia829099109121137153169185199211224
Asia-PacificPakistan41475361697891105120136153169
EuropeGermany697174767978787881828282
EuropeTurkey222630333742475358636771
EuropeFrance424447505253545657596061
EuropeUnited Kingdom505153555656565758596061

To create the others dataset, we exclude all countries that are present in the top-4.

R
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)
regionchrcountrychr1952dbl1957dbl1962dbl1967dbl1972dbl1977dbl1982dbl1987dbl1992dbl1997dbl2002dbl2007dbl
AfricaOthers146162183206239270314362409463515570
AmericasOthers88101113127143156167185202224238254
Asia-PacificOthers3543914364935466086727458118829491012
EuropeOthers235244257269278289294301303305307310

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.

02_table on Slide 2
Figure 4: 02_table on Slide 2

Let’s create 02_table. To create this table, we first create a new variable called pop_range.

R
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)
regionchrPopulation Categoryfct# of Countriesint
AfricaLess than 500K2
Africa500K - 1 Million3
Africa1M - 10 Million21
Africa10M - 100 Million25
Africa100M - 1 Billion1
AfricaMore than 1 Billion0
AmericasLess than 500K0
Americas500K - 1 Million0
Americas1M - 10 Million13
Americas10M - 100 Million9
Americas100M - 1 Billion3
AmericasMore than 1 Billion0
Asia-PacificLess than 500K0
Asia-Pacific500K - 1 Million1
Asia-Pacific1M - 10 Million10
Asia-Pacific10M - 100 Million18
Asia-Pacific100M - 1 Billion4
Asia-PacificMore than 1 Billion2
EuropeLess than 500K1
Europe500K - 1 Million1
Europe1M - 10 Million14
Europe10M - 100 Million14
Europe100M - 1 Billion0
EuropeMore than 1 Billion0

Slide 3

Slide 3 contains 2 strings and one chart. The data for the chart looks as shown below.

03_chart table for Slide 3
Figure 5: 03_chart table for Slide 3

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.

R
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)
regionchrcountrychrpopulationdbl
AfricaNigeria135.0312
AfricaEgypt80.2645
AfricaEthiopia76.5119
AfricaCongo, Dem. Rep.64.6068
AfricaSouth Africa43.9978
AfricaSudan42.2929
AfricaTanzania38.1396
AfricaKenya35.6102
AfricaMorocco33.7572
AfricaAlgeria33.3332
AmericasUnited States301.1399
AmericasBrazil190.0106
AmericasMexico108.7009
AmericasColombia44.2276
AmericasArgentina40.3019
AmericasCanada33.3901
AmericasPeru28.6748
AmericasVenezuela26.0847
AmericasChile16.2847
AmericasEcuador13.7557
Asia-PacificChina1318.6831
Asia-PacificIndia1110.3963
Asia-PacificIndonesia223.5470
Asia-PacificPakistan169.2706
Asia-PacificBangladesh150.4483
Asia-PacificJapan127.4680
Asia-PacificPhilippines91.0773
Asia-PacificVietnam85.2624
Asia-PacificIran69.4536
Asia-PacificThailand65.0681
EuropeGermany82.4010
EuropeTurkey71.1586
EuropeFrance61.0839
EuropeUnited Kingdom60.7762
EuropeItaly58.1477
EuropeSpain40.4482
EuropePoland38.5182
EuropeRomania22.2761
EuropeNetherlands16.5706
EuropeGreece10.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.

R
unique_regions <- gp %>% distinct(region) %>% pull()
cat(unique_regions, sep = "\n")
Output
Asia-Pacific
Europe
Africa
Americas

As 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

R
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.

R
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.

Output Files
Figure 6: Output Files

You can download the full R script from here.

References & Citations