Sumproduct with Variables

I'm having a bit of an issue getting a sumproduct to function properly in VBA. I have it so that the code opens a workbook from a network folder and the workbook it opens is dependant on the date selected in a calendar on the main workbook. It appears to be a syntax issue between the variable and where the sumproduct looks for sheet1.

Here is my code :

Code:

---

Dim Loc As String
Loc = "J:\CUSTOMER SERVICE\Group Service Center\!!!GCS Management\Monthly Roll-Up Reports\Lethbridge\1 - Statistics\Telephonic Stats\telephonic Errors " & Format(Sheet10.Calendar1.Value, "YYYY") & ".xls"
Workbooks.Open Filename:=Loc
Range("e116").FormulaR1C1 = "=SUMPRODUCT(--('" & Loc & "Sheet1 '!R2C1:R3000C1=RC[-2])*('" & Loc & "Sheet1'!R2C5:R3000C5=RC[-3]),'" & Loc & "Sheet1'!R2C3:R3000C3)"

---

When I run this, the formula that appears in Cell E116 is :

Code:

---

=SUMPRODUCT(--('J:\CUSTOMER SERVICE\Group Service Center\!!!GCS Management\Monthly Roll-Up Reports\Lethbridge\1 - Statistics\Telephonic Stats\[Telephonic Errors 2012.xlsSheet1]Telephonic Errors 2012'!$A$2:$A$3000=C116)*('J:\CUSTOMER SERVICE\Group Service Center\!!!GCS Management\Monthly Roll-Up Reports\Lethbridge\1 - Statistics\Telephonic Stats\[Telephonic Errors 2012.xlsSheet1]Telephonic Errors 2012'!$E$2:$E$3000=B116),'J:\CUSTOMER SERVICE\Group Service Center\!!!GCS Management\Monthly Roll-Up Reports\Lethbridge\1 - Statistics\Telephonic Stats\[Telephonic Errors 2012.xlsSheet1]Telephonic Errors 2012'!$C$2:$C$3000)

---

I cant figure out the correct Syntax to have the close bracket around .xls rather than Sheet1 because right now excel is looking for the workbook called Telephonic Errors 2012.xlsSheet1 which obviously doesnt exist.

I have tried putting brackets around \[telephonic Errors " & Format(Sheet10.Calendar1.Value, "YYYY") & ".xls]" which causes a runtime error '1004'

Any help would be greatly appreciated.

Thanks.

I've got it figured out now.

I did end up having to add open brackets but I separated the path and the filename with &. it seems to work just fine now.

Code:

---

Dim Loc As String
Loc = "J:\CUSTOMER SERVICE\Group Service Center\!!!GCS Management\Monthly Roll-Up Reports\Lethbridge\1 - Statistics\Telephonic Stats\" & "[telephonic Errors " & Format(Sheet10.Calendar1.Value, "YYYY") & ".xls]"
Selection.offset(0, 3).FormulaR1C1 = "=SUMPRODUCT(--('" & Loc & "Sheet1'!R2C1:R3000C1=RC[-2])*('" & Loc & "Sheet1'!R2C5:R3000C5=RC[-3]),'" & Loc & "Sheet1'!R2C2:R3000C2)"
Selection.offset(0, 4).FormulaR1C1 = "=SUMPRODUCT(--('" & Loc & "Sheet1'!R2C1:R3000C1=RC[-3])*('" & Loc & "Sheet1'!R2C5:R3000C5=RC[-4]),'" & Loc & "Sheet1'!R2C3:R3000C3)"
Selection.offset(0, 5).FormulaR1C1 = "=SUMPRODUCT(--('" & Loc & "Sheet1'!R2C1:R3000C1=RC[-4])*('" & Loc & "Sheet1'!R2C5:R3000C5=RC[-5]),'" & Loc & "Sheet1'!R2C4:R3000C4)"

---