Resizing an XLS embedded in a PPT with VBA

[snicks]

I've created a PPT and embedded an XLS with 11 columns by 16 rows of data. I
only want to display 3 columns by 3 rows of data. If I double-click on the
embedded XLS I can resize the XLS such that only 3 columns and 3 rows of data
are displayed. I would like to do this with a VBA macro.

My first attempt at dealing with this was to record a PPT macro of my
actions. This is what was recorded.

ActiveWindow.Selection.SlideRange.Shapes("Object 4").Select
ActiveWindow.Selection.ShapeRange.OLEFormat.DoVerb Index:=1
ActiveWindow.Selection.Unselect

The resizing of the embedded XLS was not recorded. I also tried recording
an XLS macro of this resizing and nothing was recorded at all. I then tried
bolding some XLS text and that was record but still the resizing of the XLS
to only show 3 columns by 3 rows of data was not recorded.

How can I resize the embedded XLS with VBA?

Thank you.

 

[Peter Huang [MSFT]]

Hi,

Based on my research, it is hard to change the size of the windows. The
window is in the Excel OLEObject. But when we active the excel sheet and
try to get the its ActiveWindow property, the property is not valid which
will be valid in the Excel application.

So far as a workaround we may try to adjust the size when we create the
WorkSheet.
e.g.
ActiveWindow.Selection.SlideRange.Shapes.AddOLEObject(Left:=10, Top:=10,
Width:=200, Height:=200, ClassName:="Excel.Sheet.8", Link:=msoFalse)

This will set the size of excel sheet of 200x200.

You may have a try and let me know the result.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[snicks]

Unfortunately I'm working with a PPT that has already been created (the macro
runs after the XLS's have already been embedded into the PPT). Further my
only way of accessing the XLS sheets is through the PPT.

Is there some way to copy the existing embedded XLS's into the newly created
embedded XLS that preserves all of the formatting and data of the original
embedded XLS?

Also when I run the code below I get a "Compile Error Expected: =). If I
prepend your statement with i = then the code will run but I still get a
runtime error '438', Object does not support this property or method.
Another odd thing is that when it does run some of the vertical lines in the
newly created XLS are not visible.

Thanks.

 

[Peter Huang [MSFT]]

Hi

The code in my last post is recorded when I add an sheet object.
You may try to record on your side to test.

Also we can try to add a reference to the Excel Object Type libray and then
copy/paste the excel content.
You may have a try.



Sub Test()
Dim wk As Workbook
Dim ws As Worksheet
Application.ActivePresentation.Slides(1).Shapes(1).OLEFormat.DoVerb Index:=1
Set wk = Application.ActivePresentation.Slides(1).Shapes(1).OLEFormat.Object
Set ws = wk.Worksheets(1)
ws.Range("A1:AA100").Copy

Application.ActivePresentation.Slides(1).Shapes.AddOLEObject Left:=10,
Top:=10, Width:=480, Height:=480, ClassName:="Excel.Sheet.8", Link:=msoFalse
Dim wk1 As Workbook
Dim ws1 As Worksheet
Application.ActivePresentation.Slides(1).Shapes(2).OLEFormat.DoVerb Index:=1
Set wk = Application.ActivePresentation.Slides(1).Shapes(2).OLEFormat.Object
Set ws = wk.Worksheets(1)
ws.Range("A1").PasteSpecial xlPasteAll

wk.Application.Quit
End Sub


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.