VBA help to set position and size of table once copied to PowerPoint from Excel

ashley1984

New Member
Joined
Mar 31, 2018
Messages
19
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've written some code to move a table range from Excel to PowerPoint. The code works and the table is pasted into PowerPoint.

I'm now trying to get the table to sit in a certain location on the PowerPoint slide and be to be a certain width & height. Code is below

Sub Copy2PowerPoint()

Dim r As Range
Dim powerpointapp As Object
Dim presentation As Object
Dim slides As Object
Dim table As Object

'assign the range of the table into variable
Set r = ThisWorkbook.Worksheets("Savoury Pastry Market Data").Range("B3:M15")

'if PowerPoint is already open
Set powerpointapp = GetObject(class:="powerpoint.Application")

'if PowerPoint is not open
If powerpointapp Is Nothing Then Set powerpointapp = CreateObject(class:="PowerPoint.application")

'To create a new presentation
Set presentation = powerpointapp.Presentations.Add

Set slides = presentation.slides.Add(1, 11)

r.Copy

'to paste range into PowerPOint

slides.Shapes.PasteSpecial DataType:=2
Set table = slides.Shapes(slides.Shapes.Count)

'set position in PowerPoint
table.Left = 350
table.Top = 400

'Set height & width of table in PowerPoint
table.Height = 17.69
table.Width = 31.81

powerpointapp.Visible = True
powerpointapp.Activate

'clear copy from clipboard
Application.CutCopyMode = False

End Sub

The table height & width code works but the table ends up shrinking to a size you cant see. I want the table to be 17.69cm in height and 31.81cm in width.

Finaly, is there also a way to work out what placement values you need to place the table where you want it? In my code I have left set to 350 and top set to 400 but it's not where I want it placed. I want the tables horizontal position to be 1.03cm from top left corner and vertical position to be 10.89cm from top left corner

I've uploaded an image to illustrate where I want the table

Thanks

Upload.jpg
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,340
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top