Runtime 1004 error

[HERMONES]

Dear All
I have written this code which runs through a series of Excel files in a directory, opens them one by one, change the language to English and saves and closes the file, this works fine.
However one of the files is corrupted and its giving me a RunTime Error 1004, ideally I want it to ignore the error not bother opening the file and continue onto opening the next file. I would also like it to log the issue in the ErrorLog.txt

Can anyone tell me where I am going wrong and how I can ignore the Runtime Error? Thanks

Sub ChangeLanguage()
Application.ScreenUpdating = False
'Folderpath is where the XLCRM databases will eb saved and opened from
Dim folderPath As String
'Filename is the name of the XLCRM database that will be opened
Dim filename As String
'Workbook is the XLCRM database
Dim wb As Workbook
Dim writelog As String
Dim writelogfilename As String
folderPath = "V:\XLCRM\B Test\"
writelog = "V:\XLCRM\B Test\Test\Errors\"
writelogfilename = "ErrorLog.txt"
    
If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
filename = Dir(folderPath & "*CRM.xlsm")
    Do While filename <> ""
      Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & filename)
        
Application.Run "'" & filename & "'!English"
        
On Error Resume Next
If Not Err.Number = 0 Then
    Open writelog & writelogfilename For Append As #1
    Print #1, Now & " " & Err.Number & ":" & Err.Description
    Close #1
End If
On Error GoTo 0

filename = Dir
    SaveChanges = True
    Application.EnableEvents = False
    ActiveWindow.Close SaveChanges:=True
    Application.DisplayAlerts = False
    Loop
  Application.ScreenUpdating = True
  
    
  MsgBox "Language Change to English Complete"
  
End Sub

 

[Domski]

Not tested but try this:

Sub ChangeLanguage()

Application.ScreenUpdating = False
'Folderpath is where the XLCRM databases will eb saved and opened from
Dim folderPath As String
'Filename is the name of the XLCRM database that will be opened
Dim filename As String
'Workbook is the XLCRM database
Dim wb As Workbook
Dim writelog As String
Dim writelogfilename As String
folderPath = "V:\XLCRM\B Test\"
writelog = "V:\XLCRM\B Test\Test\Errors\"
writelogfilename = "ErrorLog.txt"
    
If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
       
filename = Dir(folderPath & "*CRM.xlsm")


On Error GoTo Error_Handler


Do While filename <> ""
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(folderPath & filename)
    Application.Run "'" & filename & "'!English"
    Application.EnableEvents = False
    ActiveWindow.Close SaveChanges:=True
    Application.DisplayAlerts = False
Skip_File:
    filename = Dir
Loop


On Error GoTo 0


Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
    
  MsgBox "Language Change to English Complete"


Exit Sub


Error_Handler:
If Not Err.Number = 0 Then
    Open writelog & writelogfilename For Append As #1
    Print #1, Now & " " & Err.Number & ":" & Err.Description
    Close #1
End If
Resume Skip_File


End Sub

Dom

 

[HERMONES]

It worked!
You are such a star.

If you have time can you tell me what I was doing wrong?

Im so happy! Thanks

 

[Domski]

Basically your error handling code was in the wrong place. It was going to encounter the error before you had told it what to do if it did. On error resume next wasn't the right thing to use either.

This could be worth a read: Error Handling In VBA

Dom

 

[HERMONES]

Thanks!
I have saved the error handling as a favourite and will take a read.

 

[marts2003]

Just wanted to say a big thank you to both of you, as I had a similar objective and was experiencing a similar issue - but now my error handling code works, so the macro can concentrate on the files it can open - and then I can inspect the few 'corrupt' files that are lurking throughout the directory.

Happy days!