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

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

--
David M. Marcovitz
Microsoft PowerPoint MVP
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

=?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
news:E6383F69-480A-428C-A4C7-(E-Mail Removed):

> 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,

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" wrote:

> 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
>
> --
> David M. Marcovitz
> Microsoft PowerPoint MVP
> Author of _Powerful PowerPoint for Educators_
> http://www.PowerfulPowerPoint.com/
>
> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
> news:E6383F69-480A-428C-A4C7-(E-Mail Removed):
>
> > 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
> >
> >
> >
> >
> >
>
>

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


--
David M. Marcovitz
Microsoft PowerPoint MVP
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

=?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
news:266377BF-39EC-4741-B779-(E-Mail Removed):

> 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" wrote:
>
>> 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
>>
>> --
>> David M. Marcovitz
>> Microsoft PowerPoint MVP
>> Author of _Powerful PowerPoint for Educators_
>> http://www.PowerfulPowerPoint.com/
>>
>> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
>> news:E6383F69-480A-428C-A4C7-(E-Mail Removed):
>>
>> > 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
>> >
>> >
>> >
>> >
>> >
>>
>>
>

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" wrote:

> 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
>
>
> --
> David M. Marcovitz
> Microsoft PowerPoint MVP
> Author of _Powerful PowerPoint for Educators_
> http://www.PowerfulPowerPoint.com/
>
> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
> news:266377BF-39EC-4741-B779-(E-Mail Removed):
>
> > 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" wrote:
> >
> >> 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
> >>
> >> --
> >> David M. Marcovitz
> >> Microsoft PowerPoint MVP
> >> Author of _Powerful PowerPoint for Educators_
> >> http://www.PowerfulPowerPoint.com/
> >>
> >> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
> >> news:E6383F69-480A-428C-A4C7-(E-Mail Removed):
> >>
> >> > 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
> >> >
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >
>
>

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

--
David M. Marcovitz
Microsoft PowerPoint MVP
Author of _Powerful PowerPoint for Educators_
http://www.PowerfulPowerPoint.com/

=?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
news:07868E07-00C2-4B53-B81F-(E-Mail Removed):

> 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" wrote:
>
>> 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
>>
>>
>> --
>> David M. Marcovitz
>> Microsoft PowerPoint MVP
>> Author of _Powerful PowerPoint for Educators_
>> http://www.PowerfulPowerPoint.com/
>>
>> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
>> news:266377BF-39EC-4741-B779-(E-Mail Removed):
>>
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >> David M. Marcovitz
>> >> Microsoft PowerPoint MVP
>> >> Author of _Powerful PowerPoint for Educators_
>> >> http://www.PowerfulPowerPoint.com/
>> >>
>> >> =?Utf-8?B?a2l0a2F0?= <(E-Mail Removed)> wrote in
>> >> news:E6383F69-480A-428C-A4C7-(E-Mail Removed):
>> >>
>> >> > 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
>> >> >
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >
>>
>>
>