VBA Directory Referencing

Question

I'm trying to reference data from another workbook and I found that (correct me if I'm wrong) inserting the worksheet formulas directly into the cells (see code below) is the only way to do this without actually opening the workbook I'm referring to. Now my dilemma is I'm trying to make the directory portion of this code variable such that the user can do something like:

Path = Application.GetOpenFilename

Right now the path is fixed at 'S:\SN\SN\2015-S[2015SNL.xlsx]2015'

Sub Formulation()

Application.ScreenUpdating = False
Sheets("Data").Activate

    Range("A1").FormulaR1C1 = _
    "=IF(ISBLANK('S:\SN\SN\2015-S\[2015SNL.xlsx]2015'!RC), _
    ""-"",'S:\SN\SN\2015-S\[2015SNL.xlsx]2015'!RC)"

Application.ScreenUpdating = True

End Sub

It is also referencing to a specific sheet as you can see the ...2015'!

Any ideas? Thank you in advanced for your assistance

Answer 1

A form not prone to errors is

Dim dirn as String, tabn as String, refs as String
dirn = "S:\SN\SN\2015-S\"  ' Change this line
tabn = dirn & "[2015SNL.xlsx]2015"
refs = "'" & tabn &"'!" & "RC"

Dim ws as Worksheet
Set ws = ActiveWorkbook.Sheets("Data")
ws.Range("A1").FormulaR1C1 = "=IF(ISBLANK(" & refs & "),""-""," & refs & ")"

Remember to qualify your Ranges, see this.

Answer 2

To add in variables into a string in VA, you need to close the quotes, and follow with and ampersand: &.

Like so:

Range("A1").FormulaR1C1 = "=IF(ISBLANK('" & Path & "2015'!RC),""-"",'" & Path & "2015'!RC)"

Make sure to add a space between the quotes, the ampersand and the variable name.