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

[jkim9312]

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've looked through the zipped files but I couldn't find anything in the .xlm files. I've noticed when you 'Edit' the OLE Object, the displayed range is captured by shaded border, see below for a picture.

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!

Apologies if this is not the correct subforum. Let me know if it is and I will repost to the 'General Discussion & Other Applications' subforum.

Thank you!

 

[jkim9312]

Right. I should have probably added more context to my situation. 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. Do you have any suggestions on how to extract the data in the embedded ole excel objects?

Side note, I'm looking into Apache Tika and I'm not entirely sure if it satisfies my needs but it captures the displayed range in my sample file but not in the actual powerpoint files containing the data in scope of my project. Additionally, it doesn't look like the string result from Tika can be cleaned/transformed to a dataframe. So...idk

sorry - forgot a word 'in' in the 2nd paragraph.

 

[jkim9312]

sorry - forgot a word 'in' in the 2nd paragraph.

Again, I wish there was an edit option (sorry if there is and I'm completely missing it). The data ranges are dynamic for each embedded excel object which is why cannot just establish a static range.

 

[Domenic]

When you're editing your OLE object and adjusting the visible range, store the visible range as text in the AlternativeText property of the Shape object. Then refer to the AlternativeText property to copy and paste your range.

To store the visible range in the AlternativeText property, right click the OLE object, and select 'Edit Alt Text'. Then store your range in the alternative text window (ie. A1:C4). Now you can copy as follows . . .

ws.Range(pptShape.AlternativeText).Copy

Will this work for you?

 

[jkim9312]

When you're editing your OLE object and adjusting the visible range, store the visible range as text in the AlternativeText property of the Shape object. Then refer to the AlternativeText property to copy and paste your range.

To store the visible range in the AlternativeText property, right click the OLE object, and select 'Edit Alt Text'. Then store your range in the alternative text window (ie. A1:C4). Now you can copy as follows . . .

ws.Range(pptShape.AlternativeText).Copy

Will this work for you?

Unfortunately, we have a large amount of ole objects/ppt files within scope and manually extracting the range is not feasible. Thanks again, Domenic.