Access/PPT VBA difficulties

Thompyt · #1 03-05-2015, 02:35 PM

I am starting out with the following code for PPT interface. I get it to open, display a title and a field, but it makes a new slide each time. I can change the while next loop so that only the Field changes and 1 slide is made. BUT the field swaps until it gets to EOF. I would like it to insert a new line for each record. I would like to have 5 fields to insert.

Title
Levels
CR_No
Change Requested
Status

Code:

Sub cmdPPT_Click()
    Dim db As Database, rs As Recordset
    Dim ppObj As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
 
    On Error GoTo err_cmdOLEPowerPoint
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Copy of Weekly Closed", dbOpenDynaset)
 
    Set ppObj = New PowerPoint.Application
    Set ppPres = ppObj.Presentations.Add
 
    With ppPres
        With .Slides.Add(rs.AbsolutePosition + 1, ppLayoutTitle)
                .Shapes(1).TextFrame.TextRange.Text = CStr(rs.Fields("[Levels]").Value)
                .Shapes(1).TextFrame.TextRange.Characters.Font.Size = 30
        While Not rs.EOF
                .Shapes(2).TextFrame.TextRange.Text = CStr(rs.Fields("[Closed]").Value)
           rs.MoveNext
        Wend
    Exit Sub
err_cmdOLEPowerPoint:
    MsgBox Err.Number & " " & Err.Description
    End With
    End With
End Sub

I am trying to make the below example on PPT:

JohnWilson · #2 03-05-2015, 11:43 PM

Looks like you are maybe changing (not adding to) the shapes 2 text with each loop.

Code:

While Not rs.EOF
.Shapes(2).TextFrame.TextRange.Text = .Shapes(2).TextFrame.TextRange.Text & _
CStr(rs.Fields("[Closed]").Value) & vbCrLf
rs.MoveNext
Wend

Thompyt · #3 03-06-2015, 08:07 AM

Thats a good assumption. I am unfamilarwith the intricacies of PPT VBA, How would I add to the Field?
What would happen if it extended past the slide? Would I need a check routine inserted?

JohnWilson · #4 03-06-2015, 08:14 AM

I posted a code example.

It should (I didn't test it) add a new line to the existing text in that shape.

Thompyt · #5 03-06-2015, 08:17 AM

Thanks, I'll give it a shot, and then have my eyes checks, and my realization capacities upgraded.

Thompyt · #6 03-06-2015, 08:32 AM

Thanks John, that worked. How do I get to add and then save a template in PPT, and call it out in VBA? I have tried the online Office 2013 examples for setting the default Template & Theme (Blank).

Is there a possibility of having different fields in that line? I have concantenated 3 fields (CR_No, Change Requested, Status) The levels field I put in Shapes(1), but I wanted to use that to seperate the 3 fields. I.e.:

Formating:
Title (Centered, Align Centered)
Levels (Centered, Align Centered)
CR_No (Left, Align Left) - Change Requested (Center, Align Left) - Status (Right) Align Center)

Example:
Title
.............................................Level 1
CR_No......Change Requested.....................................Stat us
CR_No......Change Requested.....................................Stat us
.............................................Level 2
CR_No......Change Requested.....................................Stat us
CR_No......Change Requested.....................................Stat us
.............................................Level 3
etcetra

JohnWilson · #7 03-06-2015, 11:04 AM

You cannot really have a line of text in PPt with some left aligned and some right aligned. The best solution would be to start with a table.

Thompyt · #8 03-06-2015, 12:25 PM

Not even if they are different shapes?

Shape(1) = Title
Shape(2) = Levels
Shape(3) = CR_No
Shape(4) = Change Request
Shape(5) = Status

But Shapes(3) - (5) on same line.

In a table means export Access Quesry to Excel, then import the table?

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Difficulties with bullets in Word	Cityroller	Word	1	02-24-2015 07:15 PM
Random number difficulties	ahrinn	Word	12	08-01-2012 01:04 PM
Access 2007 Upgrade After Initial Install - Access 2003 Won't Remove	wcb	Office	1	01-25-2010 04:08 AM
Can Access access folders & sub-folders and dig out data?	nrg	Office	4	06-20-2005 11:09 PM