How to check if a file is open and then close in power point vba?

Question

I am creating a macro in PowerPoint to replace pictures programically. I am trying to create a summery table in excel at the end of PowerPoint macro. First I want to check if the summery file exists. If yes then the code should check if it is open. If it is open the code should close it. and then finally code should delete it. So far I have succeeded in checking the file and deleting the file. But to close the file if it is open seems bit tough. Any help will be greatly appreciated. Thank you in advance.

Following is a part of my code.

'Get the active presentation name
pressName = ActivePresentation.Name
Pos = InStrRev(pressName, ".")
Pos = Pos - 1
pressName = Left(pressName, Pos)
excelFileName = pressName & "_Summery.xlsx"
'Create path from active presentation
excelFilePath = ActivePresentation.Path & "\" & excelFileName

'Check if excel exists
If Dir(excelFilePath) <> "" Then
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''Here code to check if file is open if yes then close''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SetAttr excelFilePath, vbNormal
Kill excelFilePath
End If

@mehow, this is a bit different question. Here we have PP-Excel interop and nothing like that in the question you mentioned in your comment. — Kazimierz Jawor, Aug 13 '13 at 14:14
@KazJaw what? it is exactly the same thing the OP is asking for — , Aug 13 '13 at 14:15
@mehow, in my opinion it's not the same question as important part of this one is applications interoperation. But luckily for you, now I can admit, the last answer over there could be alternative solution to one below. — Kazimierz Jawor, Aug 13 '13 at 14:19

Kazimierz Jawor · Accepted Answer · 2013-08-13T13:54:09.653

I combined the possible solution within your if statement:

If Dir(excelFilePath) <> "" Then
'--------------new section
    On Error Resume Next
    Dim xlsFile As Object
    Set xlsFile = GetObject(excelFileName)

    If Err.Number = 432 Then
        'file is not open, do nothing
    Else
        xlsFile .Close False
        'set true above is you need to save file when closing
    End If
    On Error GoTo 0
'--------------new section    
SetAttr excelFilePath, vbNormal
Kill excelFilePath
End If

This code is working for my simple test presentation. I hope it will work for you as well.

How to check if a file is open and then close in power point vba?

1 Answers1