-1

I have a presentation in PowerPoint with list of people. Every person has name, department and position.

The name stays constant but department and position changes.

I would like to have some kind of vlookup in PowerPoint that updates department and position dynamically from and Excel file.

I am using 2010 PP and Excel.

How can I do that?

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Piotr P
  • 135
  • 2
  • 12
  • What have you tried so far? Presumably you are at the point where you're managing your PPT with VBA, but the only problem you're facing is "how to get the value from Excel", is that correct? Or do you need someone to solve the entire problem? If you are familiar with Excel VBA, this should be fairly trivial, but the specifics of your problem are lacking in detail at the moment... – David Zemens Mar 24 '16 at 16:56

1 Answers1

0

How can I do that?

Get a handle on Excel application, and the specified file, and then do your VLOOKUP from that.

Dim lookupValue as String
Dim xlApp as Object, xlWB as Object, xlWS as Object, xlRange as Object

lookupValue = ActivePresentation.Slides(1).Shapes(1).TextRange.Text '## Modify as needed

Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Open("C:\your\file.xlsx") '## Modify as needed
Set xlWS = xlApp.Worksheets("your sheet name")       '## Modify as needed
Set xlRange = xlWS.Range("A1:F1000")                 '## Modify as needed, the Vlookup range

Then you can use the VLOOKUP or MATCH functions in VBA, against the xlWS.Range object where the data should be pulled from.

' print the value from column 3 in the range, etc.
Debug.Print xlApp.Vlookup(lookupValue, xlRange, 3, False)
David Zemens
  • 53,033
  • 11
  • 81
  • 130