PDA

View Full Version : Getting a Macro in Power Point to skip update if and excel sheet is open



ipass9
01-15-2018, 11:10 AM
I have this Macro working on a power point currently, but if one of the multiple users opens up the excel sheet its pulling from it stalls the presentation that should be constantly looping.
What do I need to add to make it miss the update if the excel sheet is open?
Thanks for any help.



Sub OnSlideShowPageChange()
Dim i As Integer
i = ActivePresentation.SlideShowWindow.View.CurrentShowPosition
If i <> 1 Then Exit Sub
'ActivePresentation.UpdateLinks
Dim osld As Slide
Dim oshp As Shape
For Each osld In ActivePresentation.Slides
For Each oshp In osld.Shapes
If oshp.Type = msoLinkedOLEObject Then
oshp.LinkFormat.Update
End If
Next oshp
Next osld


ActivePresentation.Save
' MsgBox ("HERE")
With ActivePresentation.SlideShowSettings
.ShowType = ppShowTypeKiosk
.LoopUntilStopped = msoTrue
.ShowWithNarration = msoTrue
.ShowWithAnimation = msoTrue
.RangeType = ppShowAll
.AdvanceMode = ppSlideShowUseSlideTimings
.PointerColor.RGB = RGB(Red:=255, Green:=0, Blue:=0)
.Run
End With
End Sub

yujin
01-16-2018, 02:57 AM
I think the problem is the excel sheet, not the powerpoint macro.
Is the excel sheet you linked allowed to be used by multiple users?
You should check out the link below.


About the shared workbook feature - Office Support
https://support.office.com/en-us/article/about-the-shared-workbook-feature-49b833c0-873b-48d8-8bf2-c1c59a628534

ipass9
01-16-2018, 07:46 AM
Thanks Yujin for your response.

Yes its and excel sheet that multiple people can access and when someone is updating it stalls the Power point presentation and is running.

That link did not work...

JKwan
01-16-2018, 01:21 PM
Test to see if file is open then act accordingly:


Function IsFileOpen(ByVal FileName As String)
Dim filenum As Integer, errnum As Integer
On Error Resume Next
filenum = FreeFile()

Open FileName For Input Lock Read As #filenum
Close filenum
errnum = Err
On Error GoTo 0

Select Case errnum
Case 0
IsFileOpen = False
Case 70
IsFileOpen = True
Case Else
Error errnum
End Select
End Function
Sub TestIsFileOpen()
Dim FName As String

If IsFileOpen("c:\temp\blah.pdf") Then
MsgBox "File is open"
Else
MsgBox "File is not in use!"
End If
End Sub