VBA Help

superfb

Active Member
Joined
Oct 5, 2011
Messages
251
Office Version
  1. 2007
Platform
  1. Windows
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
 
You're welcome.
I learnt all I know about error handling from Rory's site that I linked to.
 
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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:

VBA 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
Hiya,

thanks for adjusting - unfortunately at the same part it still errors even with changing the code? I really dont understand why its crashing when it opens the last file....
 
Last edited:
Upvote 0
Is there any chance you can upload the workbook and provide a link to it? I realize we won't have your files, but we could see if there is anything else wrong with it.
 
Upvote 0
Is there any chance you can upload the workbook and provide a link to it? I realize we won't have your files, but we could see if there is anything else wrong with it.
Hiya

Apologise for the late reply.

How would i upload the s/s? or a share a link to it?
 
Upvote 0
You can upload to a share site such as OneDrive, DropBox, GoogleDrive then mark for sharing & post the link you are given to the thread.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top