R Code for R2VBA2PPT Post

rtovba2ppt.R

## ----setup, include=FALSE---------------------------------------------------------------------------
knitr::opts_chunk$set(echo = TRUE)


## ----load-packages----------------------------------------------------------------------------------
options(tidyverse.quiet = TRUE)
library(tidyverse) # duh!
library(reactable) # 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-data--------------------------------------------------------------------------------------

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


## ----02_chart, layout="l-body-outset"---------------------------------------------------------------
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

reactable(pop_trend, compact=TRUE,
                     style = "font-size:12px")


## ----top-4, layout="l-body-outset"------------------------------------------------------------------
top4 <- pop_trend %>%
  group_by(region) %>%
  slice_max(`2007`, n = 4, with_ties = FALSE) %>%
  ungroup()

reactable(top4, compact=TRUE, style = "font-size:12px")


## ----others, layout="l-body-outset"-----------------------------------------------------------------
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())

reactable(others, compact=TRUE, style = "font-size:12px")


## ----02_table---------------------------------------------------------------------------------------

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)

reactable(pop_groups, compact=TRUE, style = "font-size:12px")


## ----top10, layout="l-body-outset"------------------------------------------------------------------
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"))

reactable(top10, compact=TRUE, style = "font-size:12px")


## ----unique-regions---------------------------------------------------------------------------------
unique_regions <- gp %>% distinct(region) %>% pull()
cat(unique_regions, sep = "\n")


## ----for-loop-skeleton, eval=FALSE------------------------------------------------------------------
## 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
## }


## ----create-ppt, eval=FALSE-------------------------------------------------------------------------
 for (curr_region in unique_regions) {
   gc(verbose = TRUE)
   Sys.sleep(2)
   # 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("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, "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("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")

   xlWbk$Close(TRUE) # save and close excel book
   xlApp$Quit()
   gc(verbose = TRUE)
   Sys.sleep(2)
   print(paste("Presentation for", curr_region,"created successfully"))
 }