VBA Help - error

[superfb]

Hi guys,

the below macro opens a list of workbooks in a specific file directory... however when it gets to the last file, it opens it but crashes....

Sub CheckFileExistsV2_5()
'
    Dim Cel                     As Range
    Dim FileExistsErrorString   As String
    Dim FileExistsValidString   As String
'
    FileExistsErrorString = "Files that did not open:" & vbCrLf                         ' Initiate string that saves files that didn't open
    FileExistsValidString = "Files that opened successfully:" & vbCrLf                  ' Initiate string that saves files that opened
'
    For Each Cel In Range("Q2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)               ' For every cell in the range of files to be opened ...
       If Dir(Cel.Value) = "" Then                                                       '   If File doesn't exist ...
             MsgBox "All the workbooks are now open"                                   '       Append the FileName to the FileExistsErrorString
        Else                                                                            '   If File exists ...
            Workbooks.Open Cel.Value                                                    '       Open the file
            FileExistsValidString = FileExistsValidString & Cel.Value & vbCrLf          '       Append the FileName to the FileExistsValidString
        End If
CheckNext:
    Next
'
    MsgBox FileExistsValidString & vbCrLf & vbCrLf & FileExistsErrorString              ' Display message box of files that opened and didn't open
'
    ThisWorkbook.Sheets("Main Menu").Activate                                                     ' Return to This workbook
    Exit Sub
'
ErrorHandler:
    On Error GoTo -1                                                                    ' Clear the error flag
    On Error GoTo ErrorHandler                                                          ' Reset error handler
    FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf                  ' Append the FileName to the FileExistsErrorString
    GoTo CheckNext                                                                      ' Return to code
End Sub

specifically crashes at If Dir(Cel.Value) = "" Then ' If File doesn't exist ...

any ideas how i can rectify this?

 

[6StringJazzer]

Your title says "error" but your description says "crashes". Please explain exactly what happens when the failure occurs. If Excel raises an error, give the error number and description. If Excel crashes, please explain what happens--does it freeze and become unresponsive? Does it close?

What is the value of Cel.Value at the point of failure? (You will get a runtime error if is not a valid path string.)

It is not a good practice to have a GoTo back into a For loop. In fact any GoTo is not a good practice.
You do not have an On Error statement to set up handling in ErrorHandler, so you will never reach that code anyway.

 

[superfb]

Your title says "error" but your description says "crashes". Please explain exactly what happens when the failure occurs. If Excel raises an error, give the error number and description. If Excel crashes, please explain what happens--does it freeze and become unresponsive? Does it close?

What is the value of Cel.Value at the point of failure? (You will get a runtime error if is not a valid path string.)

It is not a good practice to have a GoTo back into a For loop. In fact any GoTo is not a good practice.
You do not have an On Error statement to set up handling in ErrorHandler, so you will never reach that code anyway.

Apologies a run time error occurs

 

[6StringJazzer]

Please reply to my other questions:

If Excel raises an error, give the error number and description.

What is the value of Cel.Value at the point of failure? (You will get a runtime error if is not a valid path string.)

 

[superfb]

Please reply to my other questions:

The value is blank, as it opens all the workbooks in the specified list but instead of the message box appearing "MsgBox "All the workbooks are now open" " A runtime error message 13 appears....

 

[6StringJazzer]

You may be getting an error because you are calling Dir with a null string as the file string. I don't have any way to run your code since it is specific to your environment. Also, there is no guarantee that just because you have one file that is not found that all workbooks are now open, but I am not familiar enough with what you want to do to offer a fix for that.

    For Each Cel In Range("Q2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)               ' For every cell in the range of files to be opened ...
       If Cel.Value <> "" Then
          If Dir(Cel.Value) = "" Then                                                       '   If File doesn't exist ...
                MsgBox "All the workbooks are now open"                                   '       Append the FileName to the FileExistsErrorString
           Else                                                                            '   If File exists ...
               Workbooks.Open Cel.Value                                                    '       Open the file
               FileExistsValidString = FileExistsValidString & Cel.Value & vbCrLf          '       Append the FileName to the FileExistsValidString
           End If
       End if
CheckNext:
    Next

 

[superfb]

You may be getting an error because you are calling Dir with a null string as the file string. I don't have any way to run your code since it is specific to your environment. Also, there is no guarantee that just because you have one file that is not found that all workbooks are now open, but I am not familiar enough with what you want to do to offer a fix for that.

    For Each Cel In Range("Q2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)               ' For every cell in the range of files to be opened ...
       If Cel.Value <> "" Then
          If Dir(Cel.Value) = "" Then                                                       '   If File doesn't exist ...
                MsgBox "All the workbooks are now open"                                   '       Append the FileName to the FileExistsErrorString
           Else                                                                            '   If File exists ...
               Workbooks.Open Cel.Value                                                    '       Open the file
               FileExistsValidString = FileExistsValidString & Cel.Value & vbCrLf          '       Append the FileName to the FileExistsValidString
           End If
       End if
CheckNext:
    Next

Thank you for the reply, looking at it, ideally i wanted to open all the workbooks to open that have been listed, but as you mention a workbook may not open due to other reasons, i guess is there a way to adjust this code, so if it opens on by one, if its unable to open one file it skips to the next, but at the end off it i have a message box telling me what workbooks didnt open? alternatively if all workbooks opened just a simple message saying all workbooks opened successfully?

 

[superfb]

You may be getting an error because you are calling Dir with a null string as the file string. I don't have any way to run your code since it is specific to your environment. Also, there is no guarantee that just because you have one file that is not found that all workbooks are now open, but I am not familiar enough with what you want to do to offer a fix for that.

    For Each Cel In Range("Q2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)               ' For every cell in the range of files to be opened ...
       If Cel.Value <> "" Then
          If Dir(Cel.Value) = "" Then                                                       '   If File doesn't exist ...
                MsgBox "All the workbooks are now open"                                   '       Append the FileName to the FileExistsErrorString
           Else                                                                            '   If File exists ...
               Workbooks.Open Cel.Value                                                    '       Open the file
               FileExistsValidString = FileExistsValidString & Cel.Value & vbCrLf          '       Append the FileName to the FileExistsValidString
           End If
       End if
CheckNext:
    Next

Hiya, was eager to know if you had time to read my message above? thanks

 

[johnnyL]

@superfb see if the following does what you are wanting:

Sub CheckFileExistsV2_5()
'
    Dim ErrorCount              As Long
    Dim Cel                     As Range
    Dim FileExistsErrorString   As String
    Dim FileExistsValidString   As String
'
    FileExistsErrorString = "Files that did not open:" & vbCrLf                         ' Initiate string that saves files that didn't open
    FileExistsValidString = "Files that opened successfully:" & vbCrLf                  ' Initiate string that saves files that opened
'
    For Each Cel In Range("Q2:Q" & Range("Q" & Rows.Count).End(xlUp).Row)               ' For every cell in the range of files to be opened ...
        On Error GoTo ErrorHandler                                                      '   If file doesn't exist, go handle the resulting error
'
        If Dir(Cel.Value) <> "" Then                                                    '   If File exists then ...
            Workbooks.Open Cel.Value                                                    '       Open the file
            FileExistsValidString = FileExistsValidString & Cel.Value & vbCrLf          '       Append the FileName to the FileExistsValidString
        End If
CheckNext:
    Next
'
    If ErrorCount > 0 Then
        MsgBox FileExistsValidString & vbCrLf & vbCrLf & FileExistsErrorString          ' Display message box of files that opened and didn't open
    Else
        MsgBox "All the workbooks are now open"
    End If
'
    ThisWorkbook.Sheets("Main Menu").Activate                                           ' Return to This workbook
    Exit Sub
'
ErrorHandler:
    ErrorCount = ErrorCount + 1
    FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf                  ' Append the FileName to the FileExistsErrorString
    Resume CheckNext                                                                    ' Return to code
End Sub