VBA Help

[superfb]

Hi all,

I was wondering if someone could kindly help me with a code to open a listen of files listed in a column. Let's say A5 and downwards.

Number of files can change, however once the macro opens all the files I have a message box at the end telling me what files opened.

Equally if that file doesn't exists, can the macro carry on, but at the end give me a list of filenames that did open and didn't?

Thanks

 

[superfb]

I'm not sure how that could happen because the code does a check to see if the file exists, if it doesn't exist, it doesn't try to open it.

Here is another version you can try that would handle files that do exist but cause an error:

Sub CheckFileExistsV2()
'
    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
'
    On Error GoTo ErrorHandler
'
    For Each Cel In Range("A5:A" & Range("A" & 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 ...
            FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf          '       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(1).Activate                                                     ' Return to This workbook
    Exit Sub
'
ErrorHandler:
    Err.Clear
    FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf                  ' Append the FileName to the FileExistsErrorString
    GoTo CheckNext
End Sub

Apoligise in the delay in the reply

i just gave this macro a run, and i got a type 13 error message, i believe for some weird reason when it opens the last file, it causes an error? looking at the code this shouldnt really happen?

 

[johnnyL]

What line does the error occur on?

 

[superfb]

What line does the error occur on?

  If Dir(Cel.Value) = "" Then

on this bit?

 

[johnnyL]

Try moving the on error goto line to below the For each cel line

 

[superfb]

Try moving the on error goto line to below the For each cel line

it crashed on the same line...

 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 Dir(Cel.Value) = "" Then

I think after all the Spreadsheets have opened, and the next row is empty...it crashes

whereas the code says if the row is empty then a message box appears?

 

[johnnyL]

I have no idea how you can get an error to pop up when there is error handling code.

Can you post what your column Q looks like?

 

[Fluff]

I have no idea how you can get an error to pop up when there is error handling code.

Possibly because there was a previous error, so the error handler is still active as it has not been reset On Error WTF?

 

[johnnyL]

Thank you @Fluff. There was a misunderstanding on my part. I thought Err.Clear cleared the error flag, upon further review, it just clears the text.

Now that the misconception has been cleared up ... here is the revised code:

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 ...
            FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf          '       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(1).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

 

[Fluff]

Personally I would use

ErrorHandler:
    FileExistsErrorString = FileExistsErrorString & Cel.Value & vbCrLf                  ' Append the FileName to the FileExistsErrorString
    Resume CheckNext                                                                      ' Return to code

 

[johnnyL]

I would have also, if I knew such an option existed. With the use of Resume, it clears the error flag also. Nice!
Thank you once again @Fluff.