Macro to open two XLSM files from Names and Filepaths in cell

[tonywatsonhelp]

Hi Everyone,
I want to be able to open two XLSM doc from a folder.

The Two File Names are in sheet "Welcome" Cells "B11" and "B12" but don't or might not have the .xlsm ending.

the filepath is in cell B10 and will look like this "C:\Users\Tonypc\Downloads\New folder"

I would like to first check the 3 cells have data and if not Messagebox "Please fill in Filepath" (if B10 empty) "Please fill in "EMP Report Name" if B11 Empty

"Please fill in "AMP Report Name" if B12 Empty.

Then check if folder exists if not messagebox
Then each file again if not messagebox.
if all are there open the two files.

please help if you can.

thanks

Tony​

 

[Steve_]

Hi Everyone,
I want to be able to open two XLSM doc from a folder.

The Two File Names are in sheet "Welcome" Cells "B11" and "B12" but don't or might not have the .xlsm ending.

the filepath is in cell B10 and will look like this "C:\Users\Tonypc\Downloads\New folder"

I would like to first check the 3 cells have data and if not Messagebox "Please fill in Filepath" (if B10 empty) "Please fill in "EMP Report Name" if B11 Empty

"Please fill in "AMP Report Name" if B12 Empty.

Then check if folder exists if not messagebox
Then each file again if not messagebox.
if all are there open the two files.

please help if you can.

thanks

Tony​

This should get you started, Tony.

Sub GoBabyGo()
    If Not IsEmpty(Sheets("Sheet1").Range("B10")) Then
        shDir = Sheets("Sheet1").Range("B10")
        shDir = Append_Suffix(shDir, "\")
        If Dir(shDir, vbDirectory) = vbNullString Then
            errMsg = errMsg & vbCrLf & "Invalid Directory Path (B10)"
        End If
    Else
        errMsg = errMsg & vbCrLf & "Please fill in file path! (B10)"
    End If
    If Not IsEmpty(Sheets("Sheet1").Range("B11")) Then
        shOne = Sheets("Sheet1").Range("B11")
        shOne = Append_Suffix(shOne, ".xlsm")
        If Dir(shDir & shOne, vbDirectory) = vbNullString Then
            errMsg = errMsg & vbCrLf & "'EMP Report' not found! (B11)"
        End If
    Else
        errMsg = errMsg & vbCrLf & "Please fill in 'EMP Report Name' (B11)"
    End If
    If Not IsEmpty(Sheets("Sheet1").Range("B12")) Then
        shTwo = Sheets("Sheet1").Range("B12")
        shTwo = Append_Suffix(shTwo, ".xlsm")
        If Dir(shDir & shTwo, vbDirectory) = vbNullString Then
            errMsg = errMsg & vbCrLf & "'AMP Report' not found! (B11)"
        End If
    Else
        errMsg = errMsg & vbCrLf & "Please fill in 'AMP Report Name' (B12)"
    End If
    If Not IsEmpty(errMsg) Then
        MsgBox errMsg, vbOKOnly + vbCritical, "Missing Data"
        Exit Sub
    Else
        Workbooks.Open Filename:=shDir & shOne
        Workbooks.Open Filename:=shDir & shTwo
    End If
End Sub
Function Append_Suffix(sData, sSuffix)
    If Right(sData, Len(sSuffix)) <> sSuffix Then
        Append_Suffix = sData & sSuffix
    Else
        Append_Suffix = sData
    End If
End Function

 

[tonywatsonhelp]

Thank you Steve,
I think this is all i need, I'll take it from here!
Tony