I’ve been looking into a solution for more easily generating PowerPoints based on an Excel-file. The current solution using Macros in Excel seems to me to be really inefficient and cumbersome (although I’m no expert on Excel Macros).
The typical scenario is that the Excel sheet contains a bunch of rows of data. Then the macro basically moves one row to another place, which is the data set for generating a series of graphs etc within the Excel, then takes the content and places it into a pre-defined PowerPoint. Once done, it goes to the next row of the Excel data copies it to the area the charts etc are based on, refreshes them and creates a new PowerPoint based on this - and so on until it’s done.
I don’t expect anyone to have a full solution in mind here, but would appreciate if someone had any experience with something similar and could point me in a direction of some setup to look into. Then I might come back with some more detailed questions.
At a base level, from what is described, it sounds fine. If I understand correctly, you have a set of data that consists of multiple rows. Each row is sequentially used to generate a chart, and from that you are either producing a new PowerPoint file containing a single slide with the chart on it, or updating an existing PowerPoint with at least one new slide with the updated chart; the latter difference isn’t clear from the original description.
In terms of inefficiency and being cumbersome, I guess much of that will depend upon the size and complexity of the data & charts and the speed of the computer generating them. But the algorithm/approach itself seems reasonable to me for what’s been set-out.
There are other options such as having all charts generate at once simply by the data being present (but you’ll potentially have a much bigger spreadsheet and it wouldn’t really work for a variable number of rows), having the charts embedded in a PowerPoints and refreshing them to pick up the latest embed when shown (relies on your file structure and distribution may be an issue for this approach), etc. There’s essentially some dependency around scale, around how the PowerPoint decks will be used, etc. in terms of what’s appropriate.
If you want to improve efficiencies, then the breadth of what you are looking at might also be worth looking into.
Where does the source data come from and how does that get into the spreadsheet?
Do you have a machine that could process the file on a schedule - you can accomplish a lot of Excel and PowerPoint magic with a bit of VBScript on a Windows PC (/virtual machine) for example. I have a really old version on my Mac, so I don’t know how the AppleScript support might be these days - but it may offer something similar.
Could a Google App solution drive a similar result? This too can be scheduled and scripted (JavaScript) and requires no personal device to run it).
Does the final format have to be a PowerPoint slide (deck), or are there other output options … which then might bring on board a number of other services and apps as automation options.
Remember, if the result is just there waiting for you when you need it (with no manual trigger, no waiting), small inefficiencies are simply never observed and are therefore often negated.
Don’t get me wrong though. Excel (on the PC at least) is undoubtedly one of my favourite apps. The power it can bing to bear on things still impresses me almost daily when and I simply couldn’t do my job without the macros, and formulas it enables me with. It may be the perfect fit for your use case, but without a wider understanding of what the end goal might be, it’s hard to pick a direction.
I should have added more information about this. Sorry about that. We’re talking about hundreds of rows of data in many cases, each row containing the needed data to generate multiple charts and calculated values that need to be put into specific areas of a multi-slide PowerPoint “template”.
And this varies from time to time, i.e. a new PowerPoint Template is created to fit a given data set, and currently the VBA/macro in Excel is updated to correspond with the updated data set and power point template. Then it’s set to run and generate the PowerPoint-files.
One of the main issues with this process is that… well, the VBA/macro process has a tendency to just stop now and then in some cases. And it needs to be restarted after checking a bit to make sure it’s still at least doing the right thing. And the runtime can often be 8+ hours.
It’s running in a Windows environment, so my hope is that I can find a better way to do this which is easier to maintain/update for new datasets and templates, not as error prone, and quicker… running on macOS.
Other than solving an actual problem, it would give me yet another argument for getting a colleague to switch to Mac. So needless to say, a lot is at stake here!
It’s delivered from another system that gathers it, but it’s not viable to do anything about the delivery. So it’s the handling of the data in a better way I need to solve.
The issue is both that it’s typically something that needs to get done “quickly” when it’s ready and that the VBA has a tendency to break/stop and need to get restarted.
I tried looking into this, but it needs to be done locally. So complicates things.
Sometimes the same process is used to generate PDFs instead, which I guess is also a requirement to allow for, but I figure that if I can solve this for PowerPoint, then I should be able to easily make something that results in PDFs instead.
