How can I get the displayed range of an embedded Excel worksheet in a PowerPoint slide via VBA?

New Contributor

Hi, as the title explains, how do I gather the displayed range of an Excel OLE Object embedded in a PowerPoint slide via VBA? I need to extract data from powerpoint files that's contained in either ppt table objects or embedded ole excel objects. We already have python code to extract the ppt table objects but am having difficulty in extracting the ole objects. I'm attempting to copy the displayed range in the powerpoint slides of the embedded ole excel objects which led me to where I am. However, we're open to other potential solutions and/or any suggestions on how to extract the data in the embedded ole excel objects.

 

I've looked through the zipped files but I couldn't find anything in the .xlm files. However, I've noticed when you 'Edit' the OLE Object, the displayed range is captured by shaded border, see below for a picture.

jkim9312_0-1649770729618.png

 


I'm trying to gather this information to copy the cells in range and paste them back into the slide as ppt table objects. This is so that I can ultimately extract the data via python.

I've tried several different VBA functions including wb.Windows(1).VisibleRange.Address, wb.ActiveSheet.UsedRange.Address, wb.Windows(1).Selection.Address but none of them accurately capture just the displayed data.

Also, I'm working with Office 365 on Windows 10! See attached for a sample file. 

Apologies if this is not the correct subforum/hub. Let me know if it is and I will repost to your suggested location.

Thank you!

0 Replies