Excel VBA - Excel to PowerPoint - turn off PowerPoint Screen Updating

[Harry Flashman]

I would like to be able to send data from Excel to PowerPoint with VBA. For example, I might like to populate a table in PowerPoint using data from Excel. I have code that does this, but it can be very slow. The trouble is PowerPoint seems to refresh with every change. In Excel it is possible to turn off screen updating until the routine has completed. But there is no built-in equivalent of Application.ScreenUpdating = False for PowerPoint.

This is my version of Excel:
Microsoft Excel for Office 365 MSO(16.0.11929.20234) 64 Bit


I have sound some code that is supposed to be able to turn off PowerPoint screenupdating and I am experimenting with it but I am running into trouble straight away.

This is where I found the code:

http://skp.mvps.org/ppt00033.htm

The first problem is the declarations are for 32 bit office and I am using 64 bit. But I think I have managed to alter the declarations to make them 64 bit friendly.

I followed the guide here:
https://www.jkp-ads.com/articles/apideclarations.asp

There were not instructions for 64 bit declaring LockWindowUpdate so I took an educated guess.

For my initial experiments I am running the code from PowerPoint first. But ultimately I would like to control PowerPoint from Excel.

My initial experiments running the code have been unsuccessful.

When I run the sample routing I immediately get a Type Mismatch error on this line. Can anyone where I might be going wrong?

hwnd = FindWindow("PP97FrameClass", 0&)

Here is the full code that I found at Shyam Pillai's site:

Option Explicit




' --------------------------------------------------------------------------------
' Copyright ©1999-2018, Shyam Pillai, All Rights Reserved.
' --------------------------------------------------------------------------------
' You are free to use this code within your own applications, add-ins,
' documents etc but you are expressly forbidden from selling or
' otherwise distributing this source code without prior consent.
' This includes both posting free demo projects made from this
' code as well as reproducing the code in text or html format.
' --------------------------------------------------------------------------------
' UserDefined Error codes
Const ERR_NO_WINDOW_HANDLE As Long = 1000
Const ERR_WINDOW_LOCK_FAIL As Long = 1001
Const ERR_VERSION_NOT_SUPPORTED As Long = 1002


' API declarations for FindWindow() & LockWindowUpdate()
' Use FindWindow API to locate the PowerPoint handle.
Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr


' Use LockWindowUpdate to prevent/enable window refresh
Declare PtrSafe Function LockWindowUpdate Lib "user32" _
      (ByVal hwndLock As Long) As Long
' Use UpdateWindow to force a refresh of the PowerPoint window
Declare PtrSafe Function UpdateWindow Lib "user32" (ByVal hwnd As Long) As LongPtr


Property Let ScreenUpdating(State As Boolean)
      Static hwnd As Long
      Dim VersionNo As String
      ' Get Version Number
      If State = False Then


        VersionNo = Left(Application.Version, _
                    InStr(1, Application.Version, ".") - 1)
 
' Get handle to the main application window using ClassName
        Select Case VersionNo
        Case "8"  ' For PPT97:
            hwnd = FindWindow("PP97FrameClass", 0&)
        Case "9"  ' For PPT2K:
            hwnd = FindWindow("PP9FrameClass", 0&)
        Case "10" ' For XP:
            hwnd = FindWindow("PP10FrameClass", 0&)
        Case "11" ' For 2003:
            hwnd = FindWindow("PP11FrameClass", 0&)
        Case "12" ' For 2007:
            hwnd = FindWindow("PP12FrameClass", 0&)
        Case "14" ' For 2010:
            hwnd = FindWindow("PPTFrameClass", 0&)
        Case "15" ' For 2013:
            hwnd = FindWindow("PPTFrameClass", 0&)
        Case Else
            Err.Raise Number:=vbObjectError + ERR_VERSION_NOT_SUPPORTED, _
            Description:="Newer version."
            Exit Property
        End Select


        If hwnd = 0 Then
            Err.Raise Number:=vbObjectError + ERR_NO_WINDOW_HANDLE, _
            Description:="Unable to get the PowerPoint Window handle"
            Exit Property
        End If


        If LockWindowUpdate(hwnd) = 0 Then
            Err.Raise Number:=vbObjectError + ERR_WINDOW_LOCK_FAIL, _
            Description:="Unable to set a PowerPoint window lock"
            Exit Property
        End If


      Else
        ' Unlock the Window to refresh
        LockWindowUpdate (0&)
        UpdateWindow (hwnd)
        hwnd = 0


      End If
End Property
 
'Sample Usage:
Sub LongProcessingSub()
' Lock screen redraw
ScreenUpdating = False
' --- Long time consuming code
' Redraw screen again
ScreenUpdating = True
' Also see below article for another example of usage of the code
End Sub

 

[Harry Flashman]

Okay I am idiot. Of course the code that turns off updating needs to go into a class module. I also added a line for Office 2016 (Select Case VersionNo).

When I step through the sample code however it doesn't seem to go to the code in the class module. What do I need to do?

'Sample Usage:
Sub LongProcessingSub()
' Lock screen redraw
ScreenUpdating = False
' --- Long time consuming code
' Redraw screen again
ScreenUpdating = True
' Also see below article for another example of usage of the code
End Sub

 

[Harry Flashman]

Actually I am not sure sure that this code does belong in a class module. I have put it back into a regular module and made a few adjustments to the declarations to make them 64 bit friendly. Now I can step through the code but I get an error message due to hwnd = 0. "Unable to get the PowerPoint Window handle".

Not sure what this means.