listi.net

Generate_deck - udf - that runs commands in spreadsheet to generate ppt slides

Overview

Main command processor of the PPT generation. It contains the commands needed to generate the slides.

Important that it uses a Template concept and each slide is named which has a meaning.

Instruction Spreadsheet

Command

parameter to command

Algorithm

Setup

Error handling
Recalc Full
Ignore remote requests
Log to file
Don't Ask setting because running unattended

Inputs

Get what to generate from range name that is a data validation
decide whether "quick" version
All decks or one deck?
Verify to the user they're ok
Get the Month-end

Process

Recalc if one is needed
Open the input ppt template
Expand hidden stuff via the outline
get the last row
Close the outline (hide rows
Go to the Generator sheet
For each row in the PPT instruction
- update statusbar
- Process commands in column 1
  - stop
  - end
  - comment
  - Delete - deletes a slide name in column 2
  - Deleteslides - deletes all slides containing text it col 2
  - fileout Filename - opens filename
  - close - closes the activeworkbook
  - save - Saves excel to the fileout name
  - copy - col 2 (sheet name) col 4 (from address) col 5 (to Address) with special logic if a chart (chart in col 4)
    - activate the chart based on its name
    - copy selected chart or data to clipboard
    - save the top, left, height, the width of the "to" chart
    - Activate the to chart on PPT in the template and delete it
    - Paste in the chart and set its name, top, left, height and width that was saved
    - if just data then paste it
  - file
    - close active excel don't save the changes
    - open the filename in col 2 read only
  - Sheet in col 4 ??
  - xl chart in col 5
  - check that it's a chart
  - CopyChartToPPT
Update TOC
Set PPT as normal file attribute so it can be updated as it was readonly
Update the PPT
handle case where in use by appending a random number to the end of it
Add other slides if the "Global deck"