Sometimes you have to do what you hate to do

Being engineer/programmer is generally a blessing, as most of the time one does what one likes (loves) most. I can perform even some tedious tasks if they’re related to a field I like, or a technology I appreciate. But sometimes I have to work with things which evoke a sense of almost disgust.

Member of my team has to work with PowerPoint presentations. These presentations have charts which in turn take their data from embedded (or externally linked) Excel files. One of the repetitive tasks is updating charts based on changes to Excel files (usually caused by translation). It’s not very nice thing to do, especially if you have a lot of these presentations.

You already know the first piece of technology I hate, PowerPoint. Now let’s meet another one, VBA (Visual Basic for Applications). I wanted to help my colleague by creating some automation for mentioned task. At first I wanted to use Office Interop, and however I’ve finally figured out how to do it (I think), it would be a lot of work and I could never be sure if I’ve done it right. Maybe I’ll write about it one day. Anyway, my next idea was to use a macro. I have some experience writing Excel macros, although I hate doing it. So, I’ve googled quite a bit (or rather DuckDuckGo’ed?) and was able to find a partial solution.

It wasn’t a full automation though, as you would’ve to copy/paste this macro to every PPTX, launch it, remove it and then save the presentation. But it was a good start. First problem I’ve encountered was a warning about broken link, because some of linked Excel files aren’t accessible to me, nor to my colleague. I wanted to find a way to avoid this warning being displayed, or at least to click OK button automatically, but I’ve failed. I’ve read a bit and found this. You can check if chart is linked to external file. So, I’ve started skipping charts which were externally linked. And it’s worked.

Another issue was Dir function I’ve used to get files to be processed. It doesn’t work with files named using non-ASCII characters, and as I work in localisation, it was a no go. But fortunately, there’s a FileSystemObject with its GetFolder method which did the trick. Now, I’ve just needed to skip my macro.pptm file (and a temporary version of this file created upon opening) and voila, I was able to process all PPTX files placed in the folder with my macro file. Am I happy with this solution, well it works. Could it be done better, probably. But it solves a problem and I’m sharing it here because it took me a considerable amount of time to find information and develop this solution (my basic knowledge of VBA wasn’t helpful), so I hope I’ll make someone’s life a little bit easier. Please find the full code below (remember, you’re using it at your own risk).

It updates only charts linked to embedded Excel files, if you want to update all linked charts simply remove If (pptChartData.IsLinked = False) Then and corresponding End If.

Sub UpdateLinks()
    Dim pptChart As Chart
    Dim pptChartData As ChartData
    Dim pptWorkbook As Object
    Dim sld As Slide
    Dim shp As Shape
    Dim Path As String
    Dim Pres As Presentation

    Path = ActivePresentation.Path
    Path = Path & "\"
    Set oFso = CreateObject("Scripting.FileSystemObject")
    Set oFdr = oFso.GetFolder(Path)

    For Each oFle In oFdr.Files
        If (oFle.Name = "macro.pptm" Or oFle.Name = "~$macro.pptm") Then
            GoTo NextIteration
        End If

        Set Pres = Presentations.Open(oFle.Path)

        For Each sld In Pres.Slides
            For Each shp In sld.Shapes
                On Error Resume Next
                If shp.HasChart Then
                    Set pptChart = shp.Chart
                    Set pptChartData = pptChart.ChartData
                    pptChartData.Activate
                    If (pptChartData.IsLinked = False) Then
                        Set pptWorkbook = pptChartData.Workbook
                        pptWorkbook.UpdateLink pptWorkbook.LinkSources(1)
                        pptWorkbook.Close
                    End If
                End If
            Next
        Next

        Pres.Save
        Pres.Close
NextIteration:
    Next

    Set pptWorkbook = Nothing
    Set pptChartData = Nothing
End Sub

Final thoughts. In general, I’m happy whenever I can learn, even a tiny, new thing. But this time I would’ve been happier if I would never had learnt the above. I hate PowerPoint and VBA, and less I work with any of them the happier I am. But sometimes you have to do what you hav(t)e to do.

It’s now trendy, so I’ve fed this code to ChatGPT 3.5 and asked it to improve it. And it’s broken it:) And has made level of indentation even higher. So, if AI will take over the world, we would still need people to write macros:)