vba ppt 2003 - table value lookup

[Tom Conrad]

I'm using ppt 2003 and I am a vba advanced beginner.
I need a powerpoint vba equivalent to Excel's vlookup or hlookup functions.

I need code to search a table or an embedded spreadsheet to find the data
found in a cell at a row/col intersection.

The user uses spin/scroll/option buttons to enter values that I use to
determine the correct row and col numbers.

I need to place the data table on the slide vice within the code. I looked
at establishing an array variable but I cannot create an array variable in
the vba code for proprietary reasons. If the data appears as a
table/spreadsheet within the presentation there is less likely hood that the
proprietary data will hide as meta data within the presentation.

I thought about using a group of label objects to create the table but this
seems painful.

Any help would be appreciated.

Happy New Year to all MVPs and user group correspondents.

 

[Steve Rindsberg]

I'm using ppt 2003 and I am a vba advanced beginner.
I need a powerpoint vba equivalent to Excel's vlookup or hlookup functions.

I need code to search a table or an embedded spreadsheet to find the data
found in a cell at a row/col intersection.

Is the table a PPT table or one created in Word or Excel and pasted in?
The code would be quite different for each.

 

[Tom Conrad]

Steve,

Thanks for the reply.
I believe that the best format for the table would be a ppt table. Although,
I could use whatever format uses the simplest code.
The data is positive/negative numeric of 1-3 digits in length.

My intent is to take the user entered value and then locate the cell.
The cell value will be used to rotate/draw a line.

 

[Steve Rindsberg]

Hi Tom,

I believe that the best format for the table would be a ppt table. Although,
I could use whatever format uses the simplest code.
The data is positive/negative numeric of 1-3 digits in length.

Ah ... you have the luxury of choosing your own table format. That makes it
simpler.

Have a look here for sample code for working with PPT tables:

Working with PowerPoint tables
http://www.pptfaq.com/FAQ00790.htm

None of the examples do quite what you're after but a bit of a mod to the first
example might get you there. Something like:

With ActivePresentation.Slides(1).Shapes("MyTable").Table
With .Cell(3,2).Shape
With .TextFrame.TextRange
MsgBox .Text
End With
End With
End With

 

[Brian Reilly, MVP]

Tom,
Thanks for the New Year greeting.
Nothing changes this year, Rindsberg and I continue to disagree on
approaches.(g)
I'd probably use an embedded XL workbook probably with two worksheets.
I'd probably make one xlVeryHidden to store the Propriety stuff. I'm
not really sure what you are trying to do but I avoid PPT Tables like
the plague. You could probably do this in PPT tables but Steve will
have to help you. I can't.

Brian Reilly, MVP

 

[Tom Conrad]

Not that I want to create a flame war, between you and Steve, but in your
opinion, why is your approach better? I ask because I have the flexibility
to use spreadsheet or ppt format. I am also asking because I expect to learn
something new.

I'm pretty certain that Word format would be way too painful.

 

[Steve Rindsberg]

Not that I want to create a flame war, between you and Steve,

<g> No worries, Tom. Brian and I are the best of friends, business partners,
drinking buddies and we love to flame one another. I was thinking of asking
him the same question myself. <g>

I figure in part, using an embedded Excel sheet would better enable you to keep
the table out of sight of the users, prevent them from editing it. That may or
may not be an issue in your situation.

 

[Brian Reilly, MVP]

Tom,
Steve and I just like to disagree with each other (rofl). Watch, he'll
disagree with that.

My preferences for using Excel vs. PPT tables comes from two sources:
1. I was already doing this in XL before PPT tables were almost worth
talking about, so there is a bit of the effect of inertia here.
2. PPT tables don't have all the functionality of Excel tables and
there are a lot fewer people who code in PPT tables so Google searches
turns up far fewer examples to modify.

Actually, Word tables sometimes are preferred if using decimal
alignment, but we have existing code for XL that does this too.

More on #2. PPT tables cannot do any calculations or conditional
formatting and XL can. It also has formulas and range names etc. which
make life easier to manage, leaving more time to harass Steve (g).

As an example of this, I am currently doing a charting project in PPT
for a client that programmatically creates literally thousands of
charts from Excel data. If I went with PPT tables, I'd probably get
messed up when the client changed the column order of the reports but
I dynamically create range names at workbook.open and as long as they
don't change the label of a column, I don't care what column it is in.
And they have these wierd charts, originally done in MS Grump that
have textboxes with text and a number variable in them placed right on
the bar. I would have quit that job if I had to create all these guys
in Grump every month. In Excel, I can concatenate text and a number,
e.g. N=42 and link it by formula to a range name and I haven't had to
write any code yet.

Get the point? (g)

Brian Reilly, MVP

Brian Reilly, MVP

 

[Steve Rindsberg]

Tom,
Steve and I just like to disagree with each other (rofl). Watch, he'll
disagree with that.

No I won't. (would you call that a meta-disagreement or what? disagreeing over
whether we can agree to disagree or not? SO twisted ...)

My preferences for using Excel vs. PPT tables comes from two sources:
1. I was already doing this in XL before PPT tables were almost worth
talking about, so there is a bit of the effect of inertia here.
2. PPT tables don't have all the functionality of Excel tables and
there are a lot fewer people who code in PPT tables so Google searches
turns up far fewer examples to modify.

No argument with either point.

But I get the impression that Tom just needs to store data in a table, to be
retrieved as/when needed by his code. If that's correct, there'd be no need for
Excel's extra goodness and power, CONSIDERABLE though that is.

As to the data being visible to the user ... we could get around that, even with
PPT tables. It could go either way.

If PPT's tables supply all the needed functions (some rows and columns to store
data and very little more) then there'd be little reason to add the complexity
of another app. But if ya want the tables to DO something more, then Excel may
well be the best bet.

Actually, Word tables sometimes are preferred if using decimal
alignment, but we have existing code for XL that does this too.

More on #2. PPT tables cannot do any calculations or conditional
formatting and XL can. It also has formulas and range names etc. which
make life easier to manage, leaving more time to harass Steve (g).

You already have FAR too much time on your hands for that.

But as above: that's beyond argument. It's just a question of whether Tom
needs it or not.

 

[Tom Conrad]

I'm a little behind in my postings (amazing how a honey-do-list can derail a
project). Anyway, I appreciate your response, your time and your humor.

Steve is correct I just need to store the values in a table, and retrieve
them for calculations. As I said in the original post, I am restricted from
putting the values in the coding. If I delete the table I remove the
proprietary data, or I can subsitute dummy values into the table, to retain
the codes functionality.

I will fumble around with your suggestions and if necessary get back to y'all.

Thanks again,
Tom

 

[Steve Rindsberg]

I'm a little behind in my postings (amazing how a honey-do-list can derail a
project).

Oh boy, do I ever know THAT song. It just amazes me, though, how many verses it

Any time. Give us a shout if you run into any probems

 

[Brian Reilly, MVP]

Steve,
So if you know that song, just sail up the Hudson River from Manhattan
and you will get the repetitous bard of that song on the East side of
the Hudson. A wee bit above Yonkers.

For the non-naviational folks and non-NY'ers, that would be Sing Sing,
one of the serious Prisons in the NY State Correctional system.

I know Steve knows where it is. His cats are probably all from there
(Purrrrrfect, Purrrrfect).

Brian Reilly, MVP

 

[Steve Rindsberg]

For the non-naviational folks and non-NY'ers, that would be Sing Sing,
one of the serious Prisons in the NY State Correctional system.

I know Steve knows where it is. His cats are probably all from there
(Purrrrrfect, Purrrrfect).

SingSing? Dude, they're from Al CAT Traz