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

 

[johnnyL]

@superfb I think you left out some info needed.

Do you actually want to open all of the files that are able to be opened, or do you just want a list of files that exist and a list of files not found?

 

[superfb]

@superfb I think you left out some info needed.

Do you actually want to open all of the files that are able to be opened, or do you just want a list of files that exist and a list of files not found?

Hi,

Apologise if i was vague.

I would like to open all the files in the list thanks

 

[johnnyL]

How many files on average?

 

[superfb]

How many files on average?

It would be up to 40 but sometimes it can change so anything between 25-40. Thanks

 

[superfb]

P.s would also be good if the macro could go back to the original file where the macro was executed.

 

[johnnyL]

You might be pushing the character limit of a message box, you will know if this happens because your message box will be cut off at the end, ie won't display the entire message.

Anyways, try the following:

Sub CheckFileExists()
'
    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("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
    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
End Sub

 

[superfb]

You might be pushing the character limit of a message box, you will know if this happens because your message box will be cut off at the end, ie won't display the entire message.

Anyways, try the following:

Sub CheckFileExists()
'
    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("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
    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
End Sub

Hi Thank you very much for taking the time out to write me this code!

 

[superfb]

You might be pushing the character limit of a message box, you will know if this happens because your message box will be cut off at the end, ie won't display the entire message.

Anyways, try the following:

Sub CheckFileExists()
'
    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("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
    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
End Sub

It seems to be working fine, however, if that file doesnt exist it crashes the VBA code, would on error go to be implemented? not sure how to compute that into this code

 

[johnnyL]

It seems to be working fine, however, if that file doesnt exist it crashes the VBA code, would on error go to be implemented? not sure how to compute that into this code

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