首页 文章

Powerpoint VBA更新由其他人打开的excel文件上的链接，没有警报

提问于
浏览 次
3

我有一个powerpoint演示文稿，链接到多个大型excel spreasheets . 我想更新链接，以便更新我的演示文稿 . 但是，如果单击 Update All Link ，我的powerpoint崩溃，可能是因为链接的excel文件很大 . 所以我必须手动更新每个链接，这样它就不会崩溃 .

下面这个宏将循环每个链接对象并更新其链接，但是，如果文件当前打开，如果我想取消，打开只读或通知，它将弹出警报 . 设置为false的显示警报不会对其执行任何操作 .

Sub linkupdate()
    Application.DisplayAlerts = False 
    Dim osld As Slide
    Dim oshp As Shape
    For Each osld In ActivePresentation.Slides
        osld.Select
        For Each oshp In osld.Shapes
            If oshp.Type = msoLinkedOLEObject Then
                oshp.Select
                oshp.LinkFormat.Update
            End If
        Next oshp
    Next osld
    Application.DisplayAlerts = True
End Sub

我发现powerpoint在它下面打开一个看不见的excel aplication以更新链接，但是我无法用我的vba grab 这个打开的实例并告诉它只读取链接 .

但是，我希望如果文件被其他人打开，则不会发生这种情况，但是我希望它以只读方式自动打开文件以进行更新 .

vba powerpoint powerpoint-vba

1 回答

  • 0

    看看你的代码， I am assuming all of this is being done from within the PowerPoint VBA editor . 话虽如此，让我解释一下为什么你从Excel应用程序中获取弹出窗口 . 当你写这行代码时：

    Application.DisplayAlerts = False

    You're referring to the PowerPoint application, what you're meaning to do is to refer to the Excel application because I am assuming that the popup is happening there. 为了解决这个问题，我们需要创建对Excel应用程序的引用 . 如果我添加以下代码 we can turn off the displays for the Excel application ：

    'Declare Excel Variables
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook

'Create a new Excel Application, make it invisible, set the Excel Display alerts to False.
Set xlApp = New Excel.Application
    xlApp.Visible = False
    xlApp.DisplayAlerts = False

    所以现在我们正在修复您的链接问题 . 要解决链接问题，我们将进行 to open the Excel workbook that the link is referring to in the background . 这样更新链接的速度更快 . If we don't open the workbook, I have had an instance where the update link process can easily take over 5 minutes.

    但是，如果对象是链接图表，那么获取正确的文件名可能会有点挑战性 . We need to parse the file so that way it removes the worksheet name and the chart name. 以下是我们如何做到这一点：

    'Get the Source File of the shape.
 SourceFile = PPTShape.LinkFormat.SourceFullName

'We may need to parse the Source file because if it's linked to a chart, for example, we can get the following:
'C:\Users\NAME\ExcelBook.xlsx!Chart_One!
'We want it to look like the following:
'C:\Users\NAME\ExcelBook.xlsx

'This will parse the source file so that it only includes the file name.
Position = InStr(1, SourceFile, "!", vbTextCompare)
FileName = Left(SourceFile, Position - 1)

    接下来，我们要打开工作簿，更新链接，然后关闭工作簿 . 所以我们将添加这段代码：

    'This will open the file as read-only, and will not update the links in the Excel file.
Set xlWorkBook = xlApp.Workbooks.Open(FileName, False, True)

    'Update the link
    PPTShape.LinkFormat.Update

'Close the workbook and release it from memory.
xlWorkBook.Close
Set xlWorkBook = Nothing

    总之，这里是代码的外观，以便更新所有不同链接的OLEObject的链接 .

    Sub UpdateLink()

    'Declare PowerPoint Variables
    Dim PPTSlide As Slide
    Dim PPTShape As Shape

    'Declare Excel Variables
    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook

    'Create a new Excel Application, make it invisible, set the Excel Display alerts to False.
    Set xlApp = New Excel.Application
        xlApp.Visible = False
        xlApp.DisplayAlerts = False

    'Loop through each slide in the Presentation.
    For Each PPTSlide In ActivePresentation.Slides

        'Loop through Each Shape in the slide
        For Each PPTShape In PPTSlide.Shapes

            'If the Shape is a linked OLEObject.
            If PPTShape.Type = msoLinkedOLEObject Then

                'Get the Source File of the shape.
                SourceFile = PPTShape.LinkFormat.SourceFullName

                'We may need to parse the Source file because if it's linked to a chart, for example, we can get the following:
                'C:\Users\NAME\ExcelBook.xlsx!Chart_One!
                'We want it to look like the following:
                'C:\Users\NAME\ExcelBook.xlsx

                'This will parse the source file so that it only includes the file name.
                Position = InStr(1, SourceFile, "!", vbTextCompare)
                FileName = Left(SourceFile, Position - 1)

                'This will open the file as read-only, and will not update the links in the Excel file.
                Set xlWorkBook = xlApp.Workbooks.Open(FileName, False, True)

                    'Update the link
                    PPTShape.LinkFormat.Update

                'Close the workbook and release it from memory.
                xlWorkBook.Close
                Set xlWorkBook = Nothing

            End If

        Next PPTShape
    Next PPTSlide

    'Close the Excel App & release it from memory
    xlApp.Quit
    Set xlApp = Nothing

End Sub

    将对象粘贴到PowerPoint幻灯片时也要小心，如果您使用VBA实现此目的，有时它可能会错误地粘贴链接信息！当你去更新链接时，没有什么会改变，因为链接不正确，实际上并没有引用你想要更新的项目 .

    回复于

相关问题