How to extract embedded Excel worksheets from PowerPoint using just Power Query

I’ve been using my solution to extract tables from Powerpoint a lot recently.  However, I’ve come across some situations where what looks like a table is actually an embedded Excel worksheet.

This can be quite useful in Powerpoint as it provides the ability to create subtotals and apply other formulae.

You can add these sort of tables by Insert->Table->Excel Spreadsheet or Insert->Object->Microsoft Excel Worksheet or if you Copy and Paste->Embed.

Of course, it also has the side effect of completely breaking my approach for extracting the data using Power Query.

Initially I thought I was going to have to find the reference in the XML, track it through the relationship file and then to the actual embedded workbook.  However, I quickly realised that there wasn’t much point.

The embedded worksheets always end up in the same folder within the zip file – ppt\embeddings –  so we can just grab them from there.

The code then becomes pretty simple.

  1. Unzip the .pptx file using the UnzipContents function from previous postings
  2. Filter for just files within the embedded folder
  3. Tell PowerQuery to treat the content as an Excel Workbook.

Unfortunately, it didn’t work.

It turns out that, for reasons unclear to me, Powerpoint doesn’t compress the content of embedded Excel worksheets.  This means that when the UnzipContents function tries to decompress them it fails and produces null instead.  I’ve noticed it seems to apply the same approach to embedded images such as JPGs.

There was a quick fix to this though.  I made a slight modification to the UnzipContents function and told it to return the raw binary data instead of null when it fails to decompress.

with:

And voila, PowerQuery was able to open it as an Excel workbook, confirming that the content wasn’t compressed.

The code then becomes:

ExtractEmbeddedWorksheetsFromPowerpoint:

And you can call it from a Query like this:

This should yield a result like this:

2016-08-06 14_36_51-Query1 (2) - Query Editor

From here you can select the table you are interested in and drilldown the sheet:

2016-08-06 14_40_33-Query1 (2) - Query Editor

Here’s a sample and a workbook with a working example:

Sample2

Extract Powerpoint embedded v2

1 thought on “How to extract embedded Excel worksheets from PowerPoint using just Power Query”

Leave a Reply

Your email address will not be published. Required fields are marked *