PowerPoint VBA - 定期从Excel更新链接图 [英] PowerPoint VBA - Update linked graph from Excel at regular intervals

问题描述

我正在尝试构建这个系统，在那里我有一个外部Excel文件的链接图PowerPoint演示文稿。我看到我可以在PowerPoint中右键单击该图，然后单击更新链接，图形将自动更新。

但是，如果我想要这个自动化？如果这样做可以不创建一个很好的加载项。那么PowerPoint中有哪些事件处理程序？我认为有一个事件为SlideChanged或什么？我可以让演示文稿无限循环，并更新每个新的幻灯片开关的链接？可能有大量的图表。并为每个图的一个幻灯片。或者，其他任何明智的想法？
我正在尝试构建的系统基本上是收集数据并以任何可能需要的形式显示它的框架。数据自动从经济软件导入数据库。所以我创建了一个命令行应用程序，它基本上打开一个Excel文件并运行一个宏（收集新数据并将其复制到工作表中）。此命令行应用程序设置为通过计划任务运行为特定时间。这是我想要自动显示图形的数据。

我实际上做了很大一部分：）

这是VB.NET应用程序的代码可以用作命令行应用程序）

 导入Microsoft.Office.Interop 
公共类Form1 
 
 Dim oPPTApp 
 
 Sub updatePPTGraph（）
对于每个oSlide在oPPTApp.ActivePresentation.Slides 
对于每个oShape在oSlide.shapes 
如果oShape.Type = 10然后
 oShape.LinkFormat.Update（）
结束如果
 
下一个
下一个
 End Sub 
 
 Private Sub Form1_Load （ByVal sender As System.Object，ByVal e As System.EventArgs）处理MyBase.Load 
 oPPTApp =新的PowerPoint.Application 
 oPPTApp.visible = True 
 Dim oPresentation As PowerPoint.Presentation 
 
 oPresentation = oPPTApp.Presentations.Open（C：\Users\kenny\Documents\Chartte st.pptx）
 updatePPTGraph（）
 End Sub 
结束类
  

这显然是一个开始。我需要弄清楚，如果幻灯片显示也可以完成。但我认为应该是可能的。当我设法得到值得一提的东西时，会更新：）

解决方案

更改底层幻灯片（即从外部Excel应用程序）更新跑步秀;运行中的当前幻灯片通常不会显示更新，除非您强制重新绘制，但是当再次显示该节目时，该更新应该可见。

I'm trying to build this system where I have this PowerPoint presentation with a linked graph from an external Excel file. I've seen that I can right click this graph in PowerPoint and click "Update link" and the graph is automatically updated.

But what if I want this automated? If this can be done without creating an Add-in that would have been great. So which event handlers are there in PowerPoint? I reckon there's an event for SlideChanged or something? Could I possibly have the presentation go in an endless loop and update the link at each new slide switch? There are possibly huge amounts of graphs. And one slide for each section of graphs.

Or, any other bright ideas? The system I'm trying to build is basically a framework for collecting data and displaying it in whatever form that might be wanted. Data is automatically imported from an economy software and into a database. So I've created a command line application that basically opens an Excel file and runs a macro (collecting the fresh data and copy it into the worksheet). This command line application is set to run as specific times via Scheduled Tasks. And it's this data that I want to show graphs from, automatically.

I actually did a large bit of it myself :)

Here's the code for VB.NET application (Can be used as command line application)

Imports Microsoft.Office.Interop
Public Class Form1

    Dim oPPTApp

    Sub updatePPTGraph()    
        For Each oSlide In oPPTApp.ActivePresentation.Slides
            For Each oShape In oSlide.shapes    
                If oShape.Type = 10 Then
                    oShape.LinkFormat.Update()
                End If

            Next
        Next
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        oPPTApp = New PowerPoint.Application
        oPPTApp.visible = True
        Dim oPresentation As PowerPoint.Presentation

        oPresentation = oPPTApp.Presentations.Open("C:\Users\kenny\Documents\Charttest.pptx")
        updatePPTGraph()
    End Sub
End Class

This is clearly a start. I need to figure out if it can be done while a slideshow is showing as well. But I think it should be possible. Will update when I manage to get something worth mentioning :)

解决方案

Changing the underlying slides (ie from an external Excel app) should update the running show; the current slide in the running show won't usually show the update unless you force a redraw, but when it comes around in the show again, the update should be visible.

All told, it'd be easier to automate PPT from Excel than to let it control things and suck the updates in on demand ... at least it would unless you want to include an add-in to handle events in PPT.

这篇关于PowerPoint VBA - 定期从Excel更新链接图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！