Insert Row in Table Linked to PowerPoint

[JohnathanVBA]

Hello,

I'm having a problem with my linked tables in PowerPoint. Currently, I have a master excel file that contains a lot of tables on different sheets. The tables link perfectly fine to my PowerPoint file. However, when I try and add/delete a row in the master file, the linked table in PowerPoint ends up cutting off the table (when adding a row) or having a blank row (when deleting a row). See below for an example of the table.

[table="width: 500, class: grid"]
[tr]
[td]Group[/td]
[td]#[/td]
[td]%[/td]
[/tr]
[tr]
[td]SP[/td]
[td]34[/td]
[td]6.9[/td]
[/tr]
[tr]
[td]GG[/td]
[td]144[/td]
[td]23.8[/td]
[/tr]
[tr]
[td]PP[/td]
[td]12[/td]
[td]2.1[/td]
[/tr]
[/table]


I'm wondering if there's a solution other than re-establishing a new link in PowerPoint to my master excel file.

Thanks!

 

[Worf]

Hello

See if this example helps:

' Excel module
Sub Excel_PP()
Dim ppapp As PowerPoint.Application, pres As PowerPoint.Presentation, shp As PowerPoint.shape, s, rng As Range, s2$
On Error Resume Next
Set ppapp = GetObject(, "PowerPoint.Application")
If Err.Number <> 0 Then Set ppapp = CreateObject("PowerPoint.Application")
Err.Clear: On Error GoTo 0
ppapp.Visible = msoTrue
Set pres = ppapp.ActivePresentation
For Each shp In pres.Slides(2).Shapes
    If shp.Type = msoLinkedOLEObject Then
        Select Case InStr(shp.LinkFormat.SourceFullName, "L2C2") > 0    ' update table that starts at B2
            Case True
                s = Split(shp.LinkFormat.SourceFullName, "!")
                Set rng = Range("b2").CurrentRegion                     ' B2 is upper left corner
                s2 = "L" & rng.Rows(1).Row & "C" & rng.Columns(1).Column & _
                ":L" & rng.Rows(rng.Rows.Count).Row & "C" & rng.Columns(rng.Columns.Count).Column
                shp.LinkFormat.SourceFullName = s(0) & "!" & s(1) & "!" & s2
                shp.LinkFormat.Update
            Case False
                ' do nothing
        End Select
    End If
Next
End Sub

 

[JohnathanVBA]

Thanks for the response,

Should I just place this code on my Excel sheet containing the tables?

 

[Worf]

This is an initial example that works on one table, as commented below:

• Paste it on any standard Excel module
• Change the slide number to the one containing the Excel table
• Change the LC reference for the table’s upper left corner. For example, cell E4 would be L4C5
• Change the range reference for the upper left corner, for example “e4”

After successfully testing this, we can expand it to all tables.

' Excel module (Module1,Module2,...)
Sub Excel_PP()
Dim ppapp As PowerPoint.Application, pres As PowerPoint.Presentation, shp As PowerPoint.Shape, s, rng As Range, s2$
On Error Resume Next
Set ppapp = GetObject(, "PowerPoint.Application")
If Err.Number <> 0 Then Set ppapp = CreateObject("PowerPoint.Application")
Err.Clear: On Error GoTo 0
ppapp.Visible = msoTrue
Set pres = ppapp.ActivePresentation
For Each shp In pres.Slides(5).Shapes                                   ' change slide number here
    If shp.Type = msoLinkedOLEObject Then
        Select Case InStr(shp.LinkFormat.SourceFullName, "L4C2") > 0    ' change table reference here
            Case True
                s = Split(shp.LinkFormat.SourceFullName, "!")
                Set rng = Range("b4").CurrentRegion                     ' change upper left corner here
                s2 = "L" & rng.Rows(1).Row & "C" & rng.Columns(1).Column & _
                ":L" & rng.Rows(rng.Rows.Count).Row & "C" & rng.Columns(rng.Columns.Count).Column
                shp.LinkFormat.SourceFullName = s(0) & "!" & s(1) & "!" & s2
                shp.LinkFormat.Update
            Case False
                ' do nothing
        End Select
    End If
Next
End Sub