Access to PPoint / Determining SHAPE (INDEX) w/VBA

[Rob]

Hello all,

After much pain, I figured out how to open Powerpoint from MS
Access
and populate an existing PPoint Table with data from a n Access. The
reason I am not creating the table on the fly is I wanted specific
formatting, so it seemed easier to just create the table on the slide.

Once I played with the PPT presentation, something changed in the
shapes collection and it no longer pointed to the (TABLE) shape.


I had to chage the line:

Slide1.Shapes(1).Select

to

Slide1.Shapes(2).Select

I have no idea why my table is now Shapes(2) !!

All of my heading and slide formatting is in the SLIDE MASTER. The
only object on the Slide is the Table.

Using VBA, how can I retrieve the reference to a (TABLE) shape, so I
always point to the correct Shape?


BTW - I'm using MS Office 2003


Here's my code (be warned -- it's crude; I'm still experimenting)


Option Compare Database


Sub Create_PowerPoint_Slides()


Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim Slide1 As PowerPoint.Slide


Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible


Set Pres = PPT.Presentations.Open( _
FileName:="C:\Documents and Settings\Rob\Desktop\Evaluations
Template.ppt", ReadOnly:=msoFalse)


If Pres.Application.Version >= 9 Then
Pres.Application.Visible = msoTrue 'window must be
visible
End If


Set Slide1 = Pres.Application.ActivePresentation.Slides(1)


Slide1.Shapes(2).Select


Dim Cell_Row, Cell_Column As Integer
Dim Cell_Value As String


Set db = CurrentDb
Dim rs As DAO.Recordset
Dim x As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
[qryInHouse_Reports]", dbOpenDynaset)


Dim vMyRecords() As Variant


rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
vMyRecords() = rs.GetRows(x)


For intRow = 0 To (x - 1)
For intColumn = 1 To 6
Cell_Row = (intRow + 2)
Cell_Column = (intColumn)
Cell_Value = vMyRecords(intColumn, intRow)


Slide1.Application.ActiveWindow.Selection.ShapeRange.Table.Cell(Cell_Row,
Cell_Column).Shape.TextFrame.TextRange.Text = Cell_Value
Next intColumn
Next intRow


End Sub



Rob

 

[Austin Myers]

Rob,

I like to name the shapes to something easy to remember when its created and
then use that name for anything later in the code. As an example I might
create a box and name it like this.

Set myDocument = ActivePresentation.Slides(1)
With myDocument.Shapes.AddShape(Type:=msoShapeRectangle, _
Top:=144, Left:=144, Width:=72, Height:=72)
.Name = "Red Square"
End With

 

[Rob]

Rob,

I like to name the shapes to something easy to remember when its created and
then use that name for anything later in the code. As an example I might
create a box and name it like this.

Set myDocument = ActivePresentation.Slides(1)
With myDocument.Shapes.AddShape(Type:=msoShapeRectangle, _
Top:=144, Left:=144, Width:=72, Height:=72)
.Name = "Red Square"
End With

Hello all,

After much pain, I figured out how to open Powerpoint from MS
Access
and populate an existing PPoint Table with data from a n Access. The
reason I am not creating the table on the fly is I wanted specific
formatting, so it seemed easier to just create the table on the slide.

Once I played with the PPT presentation, something changed in the
shapes collection and it no longer pointed to the (TABLE) shape.

I had to chage the line:



I have no idea why my table is now Shapes(2) !!

All of my heading and slide formatting is in the SLIDE MASTER. The
only object on the Slide is the Table.

Using VBA, how can I retrieve the reference to a (TABLE) shape, so I
always point to the correct Shape?

BTW - I'm using MS Office 2003

Here's my code (be warned -- it's crude; I'm still experimenting)

Option Compare Database

Sub Create_PowerPoint_Slides()

Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim Slide1 As PowerPoint.Slide

Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible

Set Pres = PPT.Presentations.Open( _
FileName:="C:\Documents and Settings\Rob\Desktop\Evaluations
Template.ppt", ReadOnly:=msoFalse)

If Pres.Application.Version >= 9 Then
Pres.Application.Visible = msoTrue 'window must be
visible
End If

Set Slide1 = Pres.Application.ActivePresentation.Slides(1)

Dim Cell_Row, Cell_Column As Integer
Dim Cell_Value As String

Set db = CurrentDb
Dim rs As DAO.Recordset
Dim x As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
[qryInHouse_Reports]", dbOpenDynaset)

Dim vMyRecords() As Variant

rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
vMyRecords() = rs.GetRows(x)

For intRow = 0 To (x - 1)
For intColumn = 1 To 6
Cell_Row = (intRow + 2)
Cell_Column = (intColumn)
Cell_Value = vMyRecords(intColumn, intRow)

Slide1.Application.ActiveWindow.Selection.ShapeRange.Table.Cell(Cell_Row,
Cell_Column).Shape.TextFrame.TextRange.Text = Cell_Value
Next intColumn
Next intRow

End Sub

Rob- Hide quoted text -

- Show quoted text -

Austin,

I agree, it's easier (and cleaner) when you name all of your
objects. I use the same method to insert textboxes on new slides.

But how can I do that with a presentation that already has a table
created on that slide?

Rob

 

[Austin Myers]

I agree, it's easier (and cleaner) when you name all of your
objects. I use the same method to insert textboxes on new slides.

But how can I do that with a presentation that already has a table
created on that slide?

Ah, in that case you will need to enumerate all the shapes with shapes.count
and then cycle through them testing for the shape you want.

With ActivePresentation.Slides(2).Shapes
For i = 1 To .Count
If .Item(i).HasTable Then
***Your Code***
End If
Next
End With



Austin Myers
MS PowerPoint MVP Team

Provider of PFCPro, PFCMedia and PFCExpress
www.playsforcertain.com

 

[Steve Rindsberg]

Hello all,

After much pain, I figured out how to open Powerpoint from MS
Access
and populate an existing PPoint Table with data from a n Access. The
reason I am not creating the table on the fly is I wanted specific
formatting, so it seemed easier to just create the table on the slide.

Once I played with the PPT presentation, something changed in the
shapes collection and it no longer pointed to the (TABLE) shape.

Working with shapes by index is almost guaranteed to cause problems. Any time
a shape is added or removed, or shapes are reordered on the slide, your index
may point to a different shape.

As Austin suggests, naming the shapes is more reliable.
Better yet (usually) is tagging the shapes and writing yourself a little
function to return the shape tagged with whatever you're looking for.

To tag the selected shape in the first place:

With ActiveWindow.Selection.ShapeRange(1)
Call .Tags.Add("MyTagName", "MyTagValue")
End With

Then:

Dim oSh as Shape
Set oSh = ShapeTaggedWith("MyTagName", "MyTagValue", Slide1)
If not oSh is Nothing Then
WIth oSh
' do your stuff
End With
End If

Function ShapeTaggedWith(sTagName as string, _
sTagValue as String, _
oSl as Slide) as Shape

Dim oSh as Shape
For each oSh in oSl.Shapes
If oSh.Tags(sTagName) = sTagValue Then
' we found it
Set ShapeTaggedWith = oSh
Exit Function
End If
Next

End Function

I had to chage the line:

Slide1.Shapes(1).Select

to

Slide1.Shapes(2).Select

I have no idea why my table is now Shapes(2) !!

All of my heading and slide formatting is in the SLIDE MASTER. The
only object on the Slide is the Table.

Using VBA, how can I retrieve the reference to a (TABLE) shape, so I
always point to the correct Shape?

BTW - I'm using MS Office 2003

Here's my code (be warned -- it's crude; I'm still experimenting)

Option Compare Database

Sub Create_PowerPoint_Slides()

Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim Slide1 As PowerPoint.Slide

Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible

Set Pres = PPT.Presentations.Open( _
FileName:="C:\Documents and Settings\Rob\Desktop\Evaluations
Template.ppt", ReadOnly:=msoFalse)

If Pres.Application.Version >= 9 Then
Pres.Application.Visible = msoTrue 'window must be
visible
End If

Set Slide1 = Pres.Application.ActivePresentation.Slides(1)

Slide1.Shapes(2).Select

Dim Cell_Row, Cell_Column As Integer
Dim Cell_Value As String

Set db = CurrentDb
Dim rs As DAO.Recordset
Dim x As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
[qryInHouse_Reports]", dbOpenDynaset)

Dim vMyRecords() As Variant

rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
vMyRecords() = rs.GetRows(x)

For intRow = 0 To (x - 1)
For intColumn = 1 To 6
Cell_Row = (intRow + 2)
Cell_Column = (intColumn)
Cell_Value = vMyRecords(intColumn, intRow)

Slide1.Application.ActiveWindow.Selection.ShapeRange.Table.Cell(Cell_Row,
Cell_Column).Shape.TextFrame.TextRange.Text = Cell_Value
Next intColumn
Next intRow

End Sub

Rob

 

[Rob]

Hello all,

After much pain, I figured out how to open Powerpoint from MS
Access
and populate an existing PPoint Table with data from a n Access. The
reason I am not creating the table on the fly is I wanted specific
formatting, so it seemed easier to just create the table on the slide.

Once I played with the PPT presentation, something changed in the
shapes collection and it no longer pointed to the (TABLE) shape.

Working with shapes by index is almost guaranteed to cause problems. Any time
a shape is added or removed, or shapes are reordered on the slide, your index
may point to a different shape.

As Austin suggests, naming the shapes is more reliable.
Better yet (usually) is tagging the shapes and writing yourself a little
function to return the shape tagged with whatever you're looking for.

To tag the selected shape in the first place:

With ActiveWindow.Selection.ShapeRange(1)
Call .Tags.Add("MyTagName", "MyTagValue")
End With

Then:

Dim oSh as Shape
Set oSh = ShapeTaggedWith("MyTagName", "MyTagValue", Slide1)
If not oSh is Nothing Then
WIth oSh
' do your stuff
End With
End If

Function ShapeTaggedWith(sTagName as string, _
sTagValue as String, _
oSl as Slide) as Shape

Dim oSh as Shape
For each oSh in oSl.Shapes
If oSh.Tags(sTagName) = sTagValue Then
' we found it
Set ShapeTaggedWith = oSh
Exit Function
End If
Next

End Function

I had to chage the line:



I have no idea why my table is now Shapes(2) !!

All of my heading and slide formatting is in the SLIDE MASTER. The
only object on the Slide is the Table.

Using VBA, how can I retrieve the reference to a (TABLE) shape, so I
always point to the correct Shape?

BTW - I'm using MS Office 2003

Here's my code (be warned -- it's crude; I'm still experimenting)

Option Compare Database

Sub Create_PowerPoint_Slides()

Dim PPT As Object
Dim Pres As PowerPoint.Presentation
Dim Slide1 As PowerPoint.Slide

Set PPT = CreateObject("powerpoint.application")
PPT.Visible = True 'Makes PowerPoint visible

Set Pres = PPT.Presentations.Open( _
FileName:="C:\Documents and Settings\Rob\Desktop\Evaluations
Template.ppt", ReadOnly:=msoFalse)

If Pres.Application.Version >= 9 Then
Pres.Application.Visible = msoTrue 'window must be
visible
End If

Set Slide1 = Pres.Application.ActivePresentation.Slides(1)

Dim Cell_Row, Cell_Column As Integer
Dim Cell_Value As String

Set db = CurrentDb
Dim rs As DAO.Recordset
Dim x As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM
[qryInHouse_Reports]", dbOpenDynaset)

Dim vMyRecords() As Variant

rs.MoveLast
x = rs.RecordCount
rs.MoveFirst
vMyRecords() = rs.GetRows(x)

For intRow = 0 To (x - 1)
For intColumn = 1 To 6
Cell_Row = (intRow + 2)
Cell_Column = (intColumn)
Cell_Value = vMyRecords(intColumn, intRow)

Slide1.Application.ActiveWindow.Selection.ShapeRange.Table.Cell(Cell_Row,
Cell_Column).Shape.TextFrame.TextRange.Text = Cell_Value
Next intColumn
Next intRow

End Sub

Rob

-----------------------------------------
Steve Rindsberg, PPT MVP
PPT FAQ: www.pptfaq.com
PPTools: www.pptools.com
================================================- Hide quoted text -

- Show quoted text -

I'll give it a shot... Keep in mind I'm doing this from Access, so it
will probably be painful!

Aside from the SHAPES problem, the slides come out perfect, although
the code runs very SLOWWW! I can actually watch the data fill each
slides and table cell-by-cell. Has anyone come across this problem
before?

Rob

 

[Steve Rindsberg]

I'll give it a shot... Keep in mind I'm doing this from Access, so it
will probably be painful!

It shouldn't be that bad. I worked with an Access developer on some relatively
complex code to manipulate PPT. If you can get it working in PPT, you can almost
always move it to any other capable app. I'd write it in PPT where it's simpler to
test quickly, then move it to Excel to test whether it worked when automated from
another app, then send it off to him where he'd copy/paste into Access.

Get a reference to the PPT app, surround your PPT code with a "With appPPT" and
you've pretty much got it.

Aside from the SHAPES problem, the slides come out perfect, although
the code runs very SLOWWW! I can actually watch the data fill each
slides and table cell-by-cell. Has anyone come across this problem
before?

It's going to be quicker if you don't select anything, and if you don't select
anything, you'll probably be able to get away with hiding PPT while you do the
work, which is quicker yet. But adding stuff to tables cell by cell is slow.
Here's hoping somebody knows a better way.