0

I would have thought this one would be asked to death so cannot see a solution - looking for a way to live link PowerPoint to Excel data, only for a word within an otherwise manually typed sentence.

I am not asking how to live link a chart or a table, I am asking how to have a live field within otherwise static text.

E.g. In a text box, there's the sentence "Revenue increased by 10% over the period, an improvement from the 7% increase over the prior period" and have only the '10%' and the '7%' be linked to two Excel cells.

I have seen that this is possible in the following pieces of software:

Doesn't seem like it's do-able in VBA though I'm comfortable in .NET too and have not been able to work out how this works, so any suggestion in either most welcome.

There are multiple suggestions to the effect of copy a cell and then paste-special - this does not allow you to embed the number in the sentence, it only allows you to past the cell in, which you would then have to type around. In the two links above, it is properly embedded and this is the type of solution I am after.

JakeyG
  • 100
  • 11

1 Answers1

-1

I think this is very do-able in VBA. The program on the website is of course very sophisticated, but you could very easily replicate some of the functionality.

For example by using tokens. You could enter something like "Best beer in city: #beerBrand#". Then you would iterate through your columngs and just search and replace the tokens.

If I get the program functionality right, what they do is ask the user to enter a prefix and sufix for each variable. That makes it even easier because you have the three parts of the sentence separate and you can just alsways replace the variable in the middle.

Would one of the approaches work in your case?

Franksta
  • 129
  • 9
  • Main distinctions are (1) that solution requires running code to update the values, it's not automatic and live as per e.g. embedding a chart and (2) you can only do it once - if you replace the #...# and the numbers change, how do you then update it again? – JakeyG Jul 18 '18 at 10:09
  • 1) The solution you showed with the two links may run code too, we cannot see whats happening 2) You can write the tokens and values to mapping tables and find the values you wrote onto the tokens. – Franksta Jul 18 '18 at 11:26
  • Would you store these mapping tables in memory? This tool will be rolled out to an overall team of 300, working in groups of 3 to 5 in which the whole group dips in and out of the report and the spreadsheets. So, storing the mapping in a local spreadsheet wouldn't seem to work (the product in the YouTube link pitched to us - they are way too expensive... :-) - and it doesn't seem that there's works this way as it is completely fluid across users and machines). Agree though it could be code driven with a short refresh rate or whatever. – JakeyG Jul 18 '18 at 11:52
  • I was not aware of the roll out scale you had in mind. I am not sure if my ideas would work with that scale. I thought about having a file with the mapping table on a shared drive. But that would only work if you have a low probability of collisions. Im my job we are using something similar. We have some standard tables which we all use and they are filled from a single file from the server which we all update. But that only works because people usually only update something once a week and therefore, we don't get collisions. – Franksta Jul 18 '18 at 12:51