VBA copy multiple excel ranges and paste as linked tables to Powerpoint

[geniapli]

Hello VBA World

I am struggling with my VBA code (Excel 2010) and I hope someone here can help me out. First of all you should know that I am new to VBA (started a week ago).
So not all the fundamental VBA knowledge is in my head (... yet )

This is what I need my macro to do:

...1. Open an existing PowerPoint presentation. (works! yay!)
2. Copy multiple excel ranges (on 4 different sheets) and paste it as a linked table to different PowerPoint Slides
3. A way to update this process if excel data has changes

I am struggling with this:
1. Paste to powerpoint as a table using vba
so far I was only able to paste as an embedded object (mySlide3.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue)

I found a blog explaining a way (look as the bottom conversation), but was not able to implement it into my code. Can someone help me?
https://sysmod.wordpress.com/2014/10/27/excel-vba-copy-range-paste-as-table-to-powerpoint-20102013/

2. is there a way for my code to me "cleaner"? Maybe something like this:
Dim rng As Excel.Range
rng(1)..define range
rng(2)...define range

3. Does anyone has an idea how it would work to "refresh" my data in powerpoint, if data in excel has been changed?

4. How can I center my pasted object on the slide?


I am grateful for any advice, help and hints!

Regards
Genia

Here is my code:










Sub Export_To_PPT_As_Table()

Dim objPPT As Object
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range 'further ranges will follow
Dim PowerPointApp As PowerPoint.Application
Dim myPresentation As PowerPoint.Presentation
Dim mySlide1 As PowerPoint.Slide 'not in use yet
Dim mySlide2 As PowerPoint.Slide 'not in use yet
Dim mySlide3 As PowerPoint.Slide
Dim mySlide4 As PowerPoint.Slide
Dim myShapeRange As PowerPoint.Shape

'Copy Range from Excel
Sheets(2).Select
Set rng1 = Sheets(2).Range("A7:J9")
Set rng2 = Sheets(3).Range("A9:J12")


'Make PowerPoint visible
Set objPPT = CreateObject("PowerPoint.Application")
objPPT.Visible = True

'Open document
Set myPresentation = objPPT.Presentations.Open("L:\Mydocuments\Project VBA\Presentation.pptm")
Set mySlide3 = myPresentation.Slides(3)
Set mySlide4 = myPresentation.Slides(4)

'If PowerPoint is not already open then open PowerPoint
If PowerPointApp Is Nothing Then Set PowerPointApp = CreateObject(class:="PowerPoint.Application")

'Make PowerPoint Visible and Active
PowerPointApp.Visible = True
PowerPointApp.Activate

'Copy Excel Range
rng1.Copy

'Paste to PowerPoint and position
mySlide3.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
Set myShapeRange = mySlide3.Shapes(mySlide3.Shapes.Count)

'Set position:
myShapeRange.Left = 20
myShapeRange.Top = 250

'Add Textbox
mySlide3.Shapes.AddTextbox(msoTextOrientationHorizontal, _
Left:=50, Top:=50, Width:=300, Height:=80).TextFrame _
.TextRange.Text = "Top 10 Assets "

'Clear The Clipboard
Application.CutCopyMode = False

'Copy Excel Range
rng2.Copy

'Paste to PowerPoint and position
mySlide4.Shapes.PasteSpecial DataType:=ppPasteOLEObject, Link:=msoTrue
Set myShapeRange = mySlide4.Shapes(mySlide4.Shapes.Count)

'Set position:
myShapeRange.Left = 50
myShapeRange.Top = 250

'Add Textbox
mySlide4.Shapes.AddTextbox(msoTextOrientationHorizontal, _
Left:=50, Top:=50, Width:=300, Height:=80).TextFrame _
.TextRange.Text = "Top 10 Assets "

'Clear The Clipboard
Application.CutCopyMode = False


End Sub