1

I have a spreadsheet that users can interact with to specify the file path to 4 different files needed to be opened to run some macros. The code includes a check to see if the file path they have entered is valid or not (works excellently). However, what I want to do is have a message box appear if anything doesn't work and then also tell the user which one didn't work.

My code does do that perfectly (albeit in I think a quite convoluted way) however as the array is set to have 4 values it means if the final file isn't present, it starts the text 4 lines down in the message box instead of at the top.

What I want to do, I believe, is ReDim the array to only the amount of files missing so that the MsgBox isn't 3 empty lines below the first sentence. I've kinda figured that bit out but I just could not get it working properly and now I am stumped.

Sub Open_month_0()

On Error GoTo ErrHand

ThisWorkbook.ActiveSheet.Calculate

    Dim i As String
    Dim j As String
    Dim k As String
    Dim l As String
    Dim m As String
    Dim n As String
    Dim o As String
    Dim p As String
    Dim arr(4) As Variant
    Dim File_Missing As Integer

    'Used as a counter to prompt either an error or successful result
    File_Missing = 0

        i = Range("LUX_Full_file_path")
        j = Range("LUX_Full_file_name")

        k = Range("JUP_Full_file_path_M")
        l = Range("JUP_Full_file_name_M")

        m = Range("JUP_Full_file_path_Q")
        n = Range("JUP_Full_file_name_Q")

        o = Range("JUP_Full_file_path_A")
        p = Range("JUP_Full_file_name_A")

        'The if not's check to see if the file path is valid. If it isn't, gets added to array and File_missing begins
        If Not Dir(i, vbDirectory) = vbNullString Then
            Workbooks.Open (i)
            Windows(j).Visible = False
        Else
            arr(1) = "Lux file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(k, vbDirectory) = vbNullString Then
            Workbooks.Open (k)
            Windows(l).Visible = False
        Else
            arr(2) = "Monthly file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(m, vbDirectory) = vbNullString Then
            Workbooks.Open (m)
            Windows(n).Visible = False
        Else
            arr(3) = "Quarterly file"
            File_Missing = File_Missing + 1
        End If

        If Not Dir(o, vbDirectory) = vbNullString Then
            Workbooks.Open (o)
            Windows(p).Visible = False
        Else
            arr(4) = "Annual file"
            File_Missing = File_Missing + 1
        End If

        'Basic error handling procedure that retains function.
        If File_Missing > 0 Then
            MsgBox ("The following files could not be found. Please check the file paths and try again" & vbCrLf & Join(arr, vbCrLf))
        Else
            MsgBox "Files opened successfully."
        End If

Exit Sub

ErrHand: MsgBox "There has been a critical error with opening the chosen workbooks. If the problem persists, please contact your administrator for assistance."

End Sub

Edit with pictures:

A screenshot of the message box current output
A screenshot of the message box current output

How I'd like the message box to look
How I'd like the message box to look

3
  • Have you tried Redim Preserve arr(FileMissing) ? Commented Feb 26, 2019 at 16:43
  • Secondly, edit your post to show the issues with the MsgBox. Great hint is to use Win+Shift+S to capture the area, and click the Add Image button in your post, and just paste! Commented Feb 26, 2019 at 16:44
  • 1
    Thanks for the tip Badja - have added screenshots now! Didn't realise I could do that. Will give you're suggestion a try now too Commented Feb 26, 2019 at 16:52

1 Answer 1

1

Since you just use that array to Join it later you could also just use a String variable MyMissingFiles instead of that array and append the file name.

You even don't need to count the files in File_Missing if this number is not of your interest.

Dim MyMissingFiles As String

If Not Dir(i, vbDirectory) = vbNullString Then
    Workbooks.Open (i)
    Windows(j).Visible = False
Else
    MyMissingFiles = MyMissingFiles & vbCrLf & "Lux file"
End If

' … all the others accordingly here …

If MyMissingFiles <> vbNullString Then
    MsgBox ("The following files could not be found. Please check the file paths and try again" & MyMissingFiles)
Else
    MsgBox "Files opened successfully."
End If
Sign up to request clarification or add additional context in comments.

1 Comment

This worked perfectly! Thank you SO much. Looks like I was just overcomplicating things once I had an idea in my head and honed in on it!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.