External files cause errors, how do I skip them?

[gregersdk]

Hello Excel experts!

I am working on a PowerPoint file lister, but some of these pptx files are bad. They cannot be opened, and therefore cause errors in my code. I cannot seem to find a method to say "I this line errors, just skip further down to here". All error handling solution I can see are general for the Sub and is not specialised for errors in a single line. I am looking for the VBA equivalent of IFERROR().

The line in my code that sometimes errors is:

Set myPresentation = PowerPointApp.Presentations.Open(oFile, ReadOnly:=msoTrue, WithWindow:=msoFalse) 'Problem is here

I have checked the files when it happens, and these files cannot be opened. My code is otherwise working well.

Here is my full Sub for reference:

Sub Files_with_Unicode_Chars()
Dim FileSysObj As Object      'Scripting.FileSystemObject
Dim oFolder As Object      'Scripting.Folder
Dim oFile As Object
Dim vCurrDir As String, vCrit As String

Dim DestinationPPT As String
Dim PowerPointApp As PowerPoint.Application
Dim myPresentation As PowerPoint.Presentation
Dim Slidecount As Long

    Set FileSysObj = CreateObject("Scripting.FileSystemObject")
    
    Set PowerPointApp = CreateObject("PowerPoint.Application")

    vCurrDir = "C:\Users\b017646\Downloads\Alle ppt filer"
    vCrit = ".pptx"

    Set oFolder = FileSysObj.GetFolder(vCurrDir)
    For Each oFile In oFolder.Files
        With oFile
            Select Case Mid(.Name, InStrRev(.Name, "."))
            Case vCrit
                                
                Set myPresentation = PowerPointApp.Presentations.Open(oFile, ReadOnly:=msoTrue, WithWindow:=msoFalse) 'Problem is here

Rem If file is bad, it causes and error. Would like to get around this somehow

                'Ark1.Cells(Row, 1).Value = oFile
                'Ark1.Cells(Row, 2).Value = myPresentation.Slides.Count
                'Ark1.Cells(Row, 3).Value = oFile.DateLastModified
                
                Debug.Print oFile; myPresentation.Slides.Count; oFile.DateLastModified
    
    End Select: End With: Next

End Sub

I hope You can help


GregersDK

[Greg M]

Hi there,

This is untested, but perhaps modifying your code as follows might help:

    For Each oFile In oFolder.Files
        With oFile
            Select Case Mid(.Name, InStrRev(.Name, "."))
            Case vCrit

                On Error Resume Next
                    Set myPresentation = Nothing
                    Set myPresentation = PowerPointApp.Presentations.Open(oFile, ReadOnly:=msoTrue, WithWindow:=msoFalse)
                On Error GoTo 0

                If Not myPresentation Is Nothing Then

                    Ark1.Cells(Row, 1).Value = oFile
                    Ark1.Cells(Row, 2).Value = myPresentation.Slides.Count
                    Ark1.Cells(Row, 3).Value = oFile.DateLastModified
                
                    Debug.Print oFile; myPresentation.Slides.Count; oFile.DateLastModified

                End If

    End Select: End With: Next

Hope this helps - please let me know how you get on.

Regards,

Greg M

[gregersdk]

That totally worked, thank you!

I don't totally understand it, but I have a general gap in my VBA knowledge when it comes to errors.

[Greg M]

Hi again,

Many thanks for your feedback - glad I was able to help.

Ok, we accept the fact that the following statement may fail occasionally (e.g. if the target file is corrupted),

Set myPresentation = PowerPointApp.Presentations.Open(oFile, ReadOnly:=msoTrue, WithWindow:=msoFalse)

so we temporarily suspend Excel's normal error handling (i.e. Excel won't bother to generate an error message when it "discovers" that it can't open the target file.)

When Excel can't open the target file it won't be able to assign any value to the object variable "myPresentation", so its value will remain as the "Nothing" set in the preceding statement.

We immediately re-enable Excel's normal error handling (On Error GoTo 0) so that any further errors will be handled in the normal way.

We continue with processing only if the target file was opened successfully, i.e. if the object variable "myPresentation" does NOT have a value of Nothing.

Regards,

Greg M