How to create a dynamic table?

[kitkat]

I'm writing a macro for powerpoint that pulls information from two different
Excel spreadsheets and then displays the pertinent information in a table.
I've gotten up to the point where the computer can find the correct data and
display it in a message box, but I would like for the powerpoint to populate
my table (depending on the name of the person I click) automatically.
Basically, what I need to do is make my code dynamic. It would be nice if I
could make it so that when you click on a person's name, the table appears
with the correct data, and then if you click on something else, the table
will disappear. Does anyone know how to do this? I've pasted my code thus far
below (not everything--meaning, no spreadsheet stuff. Just the stuff for
powerpoint!). Thanks a LOT!!!

Sub dynamictable()

Dim oTbl As Table
Dim lRow As Long
Dim lCol As Long

' Get reference to table:

Set oTbl = ActiveWindow.Selection.ShapeRange(1).Table
With oTbl

For lRow = 1 To .Rows.Count
For lCol = 1 To .Columns.Count
With .Cell(lRow, lCol).Shape

' Do something with each cell's text
If .HasTextFrame Then .TextFrame.TextRange.Text =
headCounts
If .TextFrame.HasText Then
Debug.Print .TextFrame.TextRange.Text
End If

' set the fill color:
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 255)

End With
Next ' column
Next ' row

End With

 

[David M. Marcovitz]

Do you want this to work in Slide Show View. If so, you will have to
access the table in a way that doesn't select it. I don't have a lot of
experience coding tables, but you should be able to give the table a
name (if you are in 2007, naming is easy; if in 2003 or below, check out
Example 8.7 on my site to use code to name a shape). Then you can use:

With ActivePresentation.SlideShowWindow.View.Slide.Shapes("My
Table").Table

or someting like that to access the table.

--David

 

[kitkat]

David,

I do want the presentation to work in slide show view and I have actually
found the name of the table so that the macro specifically applies to it. I
have tested it and it works. What I'm wondering though is, what language do I
use to make the table automatically populate? In my VBA I refer to specific
cells receiving specific information, but that's not what I want...

Thanks for your prompt response!

 

[David M. Marcovitz]

Great. I'm glad the naming worked for you. PowerPoint VBA is a little
different from Word and Excel VBA because of the different modes (Normal
View vs. Slide Show View) so VBA for one doesn't always work in the
other.

Now, to your new question. As I said, I don't do a lot with tables so I
might not be able to give you specific advice. Here are some things that
I might do:

(1) If your table has a fairly specific structure (e.g., always 4 rows
by 3 columns), I think that modifying the text in specific cells is the
way to go. I'm not clear from your message why that isn't working for
you. You should be able to populate a specifc cell with whatever text
you want. I might also choose to hide and show the table (adjusting the
shape's .Visible property to True or False so the table is only showing
when you want it). A line like this should change the text in cell 2,2
to be "hello":

ActivePresentation.Slides(1).Shapes("My Table").Table.Cell(1,
1).Shape.TextFrame.TextRange.Text = "hello"

Watch out for line breaks. This code applies to a table on slide 1,
named My Table, and assumes that the table is really there and cell 1,1
is really there.

(2) If the table is changing in structure, you will need to either
recreate the table each time or just add and delete cells. This should
be doable in VBA, but harder to keep track of what you already have. For
example, this will add a column to your table:

ActivePresentation.Slides(1).Shapes("My Table").Table.Columns.Add

 

[kitkat]

Thanks a lot for your help! I've got some ideas of what to do now

One more question--I am using the same table each time, but once I run the
macro, the name of the table changes. Is there a way I can give the table I'm
using a permanent reference so that I don't have to go in and keep changing
the code every time I run the show? (The table started as "group 171" and now
it's up to "group 177"). Thanks so much.

Kat

 

[David M. Marcovitz]

Hmm. I see. You must be adding rows and/or columns. As a table is a
grouped object, when things are added to the group (like adding new
cells), it seems to make a whole new group. This might be one of those
rare instances where working with the shape number, instead of the name
makes the most sense. Usually, you should work with the name because
that is usually stable. In this case, the number appears to be more
stable. The problem is that if you add or delete other objects on the
slide or change the order of objects (which is on top and which is in
the back), the number will change. If you are just adjusting your table,
the number will stay the same. Unfortunately, I don't know of a good way
to find the number. Numbers are generally in order of when the shape was
added (counting placeholders already existing on the slide). For
example, on most slides with a title and text area, the first shape you
add will be 3, and the next one will be 4, etc. This is different from
the number after Group in the name of the shape. To use the number just
replace the name that was in quotes in the parentheses with the number:

ActivePresenation.Slides(1).Shapes(3).Table...

if you are dealing with Shape 3 on Slide 1.

--David