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!

 

[Domenic]

The following code assumes that the variable pptShape has been assigned the PowerPoint shape containing the embedded workbook.

    'get the embedded workbook
    Dim wb As Workbook
    Set wb = pptShape.OLEFormat.Object
 
    'get the worksheet from the embedded workbook
    Dim ws As Worksheet
    Set ws = wb.Worksheets("Sheet1")

    'copy the used range from the worksheet
    ws.UsedRange.Copy

     'your code here to paste where desired

    'close embedded workbook without saving
    wb.Close SaveChanges:=False

Hope this helps!

 

[jkim9312]

Hi Domenic,

ws.UsedRange.Copy captures more than the displayed data. (i.e. - if there are cells adjacent to the data displayed, those cells are returned in the range). I'll try and figure out how to provide a sample file with the code I've tried and circumstances I'm dealing with.

Thanks!

 

[jkim9312]

Can't figure out how to edit a post/comment but here's a sample file with the circumstances I'm dealing with:

File on MEGA

mega.nz

 

[Domenic]

I haven't looked at your workbook, but you pretty well had it in your original post . . .

wb.Windows(1).VisibleRange.Copy

 

[jkim9312]

Your suggested code seems to capture a larger range than what's required. The ole object in slide 2 of the sample file should return a range of $E$9:$I$13 but wb.Windows(1).VisibleRange.Copy returns $E$9:$AC$49. Do you have any insight into why this might be occuring? There isn't any data in cell AC49.

 

[Domenic]

I just tested it, and you're right. It doesn't capture only the visible range. I don't know why, nor am I aware of a method that would do so.

By the way, now that I have re-read your post, I see that you were already at that point. You only needed the visible range.

My apologies for misunderstanding your question.

 

[jkim9312]

I just tested it, and you're right. It doesn't capture only the visible range. I don't know why, nor am I aware of a method that would do so.

By the way, now that I have re-read your post, I see that you were already at that point. You only needed the visible range.

My apologies for misunderstanding your question.

No worries. I really appreciate your input and perspective. Although it isn't the solution, you've provided a much needed validation in my struggle to solve this issue...Thank you again!

 

[Domenic]

I guess you can't simply specified a static range?

 

[jkim9312]

I guess you can't simply specified a static range?

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