1

I have a template presentation on powerpoint. I would like to replace automatically generic placeholders with the value I input in an linked excel worksheet.

I started looking at VBA at the begining of the week, I am not really yet familiar with the syntax, but I am optimistic I will find a way to understand it.

I have put "[ ]" everywhere I want a value input: enter image description here

the excel sheet present itself like this: enter image description here

I would like to have some pointers (first, to know if it is indeed possible in powerpoint) to get me started in the right direction.

Does it need to be a PublicSub()? Am I writing the macro on Powerpoint or in Excel?

Can I use a fonction similar to LookupV to get the values to replace the placeholders?

I would imagine something like :

(side question : can I use regex inside of VBA?)

for each "\[.\]" '(or characterString.startsWith("[")
lookup characterString in excel, return column 3
end each

Would this be possible?

Thank you in advance, I really appreciate the help.

Amandine FAURILLOU
  • 528
  • 1
  • 8
  • 23
  • No need for a Public Sub, you can work in Excel or PowerPoint regardless (just add the references). Is the first image the powerpoint template? If you want to use macro recorder in PPT, download an old version (2003 or maybe even 2007 but I'm not sure macro recorder wasn't unabled there yet). For regex in VBA, see the 2 top answers : http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops And for the way to replace text, if it is just replacing the place holder, you could use the syntax of the manual search in Excel as a start. – R3uK Sep 30 '15 at 14:13
  • Have you tried Bookmarks? so if you was to add a bookmark to where you want the value in powerpoint, then in excel add something like `.Bookmarks("Bookmark1").Range.Text = "Whatever" `.. you would need to declare the location of the powerpoint for this though. – Calum Sep 30 '15 at 15:07
  • @R3uK, yes the first image is the ppt template, there are more pages like that but not with the same placeholders as the rest may or may not be the same values. Manual search + ctrl H? – Amandine FAURILLOU Oct 01 '15 at 06:24
  • 1
    @Calum, no I haven't tried, but I am going to go look at it right now. Thank you – Amandine FAURILLOU Oct 01 '15 at 06:24
  • try www.pptxbuilder.com – Boosted_d16 Nov 07 '18 at 18:00

1 Answers1

2

Found an approximate solution, but simpler than code writing.

  • Open Excel,
  • fill the values, and copy them onto the powerpoint presentation : use the special paste option between the cell (or cells/cell range) you want.
  • Choose the "paste the link" option together with object worksheet Excel, select Ok.

Both files will be linked. You can change the values in Excel, and they will change also in powerpoint. The formating is done on excel.

In order to update all the values in the powerpoint after changing them on excel, powerpoint needs to be re-run, and at the restart of the application, accept the updating box prompt.

I find this simpler than the other solutions.

Amandine FAURILLOU
  • 528
  • 1
  • 8
  • 23
  • Indeed it is way simpler, but be careful with links between Office documents! I made a lot of reports with Word (and a bit with PowerPoint) but **these links are highly unstable in my experience/opinion**. I can't count the number of times I had to redo the whole templates because the links inexplicably crashed... So **be careful with that (always check after refresh) and when it is done, save the output as a copy somewhere else and then Break all the links and re-save.** – R3uK Oct 01 '15 at 08:21
  • If the location of both file doesnt change, there shouldn't be any problems, or less, right? – Amandine FAURILLOU Oct 01 '15 at 08:50
  • Better, but I wouldn't bet on any issue... Maybe PowerPoint is more stable than Word for that... I hope so! – R3uK Oct 01 '15 at 15:56
  • I don't have broken links yet, which is good, but when I update values in Excel, I have to update the links on powerpoint so that the updated values appear on Ppt. Do you know of any process that would make it automatic? – Amandine FAURILLOU Oct 02 '15 at 07:34
  • You could add code in `Worksheet_Change`event to update the links eveytime you make an update, but that might be ressource consuming. So maybe in `Workbook_BeforeSave`, you could prompt a MsgBox to choose if you want to update the value into the templates, I think this might be the best solution! ;) – R3uK Oct 02 '15 at 07:38
  • so I do need to write a macro after all. I am going to go look that up. thanks. (Do I put it in excel or powerpoint? I would go towards ppt, but the doc is for excel) – Amandine FAURILLOU Oct 02 '15 at 08:38
  • 1
    You'll have to put the code in Excel as you'll use the events happening in the workbook, go in `ThisWorkbook` module and look for the trigger that will be the best for you and you can simply `Call` a procedure that you store in a regular module in which you'll do the actual operations (open the ppt, refresh all links, save, close ppt, quit ppt app). If you want it to be done automatically, yup you'll have to code. But if it is to be updated 5 times a month, maybe that isn't really necessary but that only you can know! – R3uK Oct 02 '15 at 09:08
  • 1
    @R3uK When I open the powerpoint after having modified the excel sheet, ppt asks me if I want to update the links. I think that is what Im looking for. In any case, thank you so much for your help and input. It is appreciated. – Amandine FAURILLOU Oct 02 '15 at 12:36