VBA code for data in PowerPoint table, not objects

[hobbes_]

Hi guys,

I have this VBA code which I use for transferring data from my data sheet to slides in PowerPoint - it works fine.

However in PowerPoint I'm currently forced to transfer data to "text-objects" which is a bit of a drag because I'm used to working in a table in Powerpoint when organizing data.

Therefore my question is how to edit my VBA code so I can transfer data to a table in PowerPoint instead of an object.

Thanks.

---

Here's the VBA code:

Sub TDPTest()
    Dim shtStudent As Worksheet
    Dim strMedarbejder As String
    Dim strTitel As String
    Dim strFastholdelse As String
    Dim lngRow As Long
    Dim objPPT As Object
    Dim objPres As Object
    Dim objSld As Object
    Dim objShp As Object
    
    Set shtStudent = Worksheets("Ark1")
        
    Set objPPT = CreateObject("Powerpoint.Application")
    objPPT.Visible = True
    Set objPres = objPPT.presentations.Open(ThisWorkbook.Path & "\JBX_test.ppt")
    objPres.SaveAs ThisWorkbook.Path & "\TDPTest.ppt"
    
    lngRow = 2
    Do While shtStudent.Cells(lngRow, 2) <> ""
        
        strMedarbejder = shtStudent.Cells(lngRow, 1)
        strTitel = shtStudent.Cells(lngRow, 2)
        strFastholdelse = shtStudent.Cells(lngRow, 3)
                       
        Set objSld = objPres.slides(1).Duplicate
        For Each objShp In objSld.Shapes
            If objShp.HasTextFrame Then
                If objShp.TextFrame.HasText Then
                    objShp.TextFrame.TextRange.Replace "<Medarbejder>", strMedarbejder
                    objShp.TextFrame.TextRange.Replace "<Titel>", strTitel
                    objShp.TextFrame.TextRange.Replace "<Fastholdelse>", strFastholdelse
                End If
            End If
        Next
        lngRow = lngRow + 1
    Loop
    objPres.slides(1).Delete
    objPres.Save
    objPres.Close
    
End Sub

 

[binuoocep]

Thanks for this post. It really helped me to create a dashboard that runs dynamically from excel data. But i have a small issue.

The powerpoint and back end excel file (both in 2010 full version) are sitting in my PC and is shared with another Windows 7 portable machine which is connected to a TV. The output comes on a TV. The portable machine has light version of office 2003 and Powerpoint Viewer 2007 installed.

The code updates the slide during slideshow in my PC. But when it runs in the machine connected to TV, the code is not running. I assume it is due to the missing Microsoft Excel 14.0 object library in the portable machine and I cannot install the full version of Office 2010 in that machine because it doesn't have that much capacity.

Can you help me with a solution to this?

 

[Worf]

- Do you get any error messages? Is the code residing in an Excel module or PP module?
- If it’s an Excel macro, a reference to the PowerPoint library is required. If it’s a PowerPoint macro, a reference to the Excel library is required.
- Each Office version has its own library number. For example, Office 2013 is 15.0; when using another version, the reference has to be adjusted.
- Does the light version of Office 2003 include PowerPoint? Is the presentation running on PowerPoint 2003 or Viewer 2007?
- Can you run the code on the PC and provide the portable machine with a standalone ppt or pptx file?

 

[binuoocep]

Dear Worf,

thank you very much for the response. please see my response below

- Do you get any error messages? Is the code residing in an Excel module or PP module? No error message appears. The code is in Powerpoint.
- If it’s an Excel macro, a reference to the PowerPoint library is required. If it’s a PowerPoint macro, a reference to the Excel library is required. It is a Powerpoint macro and reference to Exel library is done.

- Each Office version has its own library number. For example, Office 2013 is 15.0; when using another version, the reference has to be adjusted. This is the issue. my machine has 2010 version and the portable machine has 2003 version of excel. I cannot open the powerpoint file (pptm) in the portable machine since it has limited functionality. All i can do it run the pptsm file through PPt Viewer 2007.

- Does the light version of Office 2003 include PowerPoint? Is the presentation running on PowerPoint 2003 or Viewer 2007?
Yes. But i installed Powerpoint Viewer 2007 so that it can run macro enabled powerpoint show which was not possible in 2003 version.

- Can you run the code on the PC and provide the portable machine with a standalone ppt or pptx file? The portable machine has its own OS. it is taking the file from my pc through network.

i tried late binding to excel library. but it generated "Object required" error.

 

[Worf]

PowerPoint 2003 can run macro enabled files using the ppt extension, which can be generated by newer Office versions.
If you are not using properties, methods or new features introduced with Office 2007 or later, it should work. Add a reference to the Excel 2003 library.
It doesn’t seem possible to achieve your goal with PP Viewer.