Hello all. I am having trouble with an excel API I am using called GemBox. I am trying to create the following formula that will sum the cell from multiple worksheets. The issue is the formula won't parse and keeps throwing a "Not expected ' " on the single quote. I also tried using Chr(39) but it still throws an error. Any ideas what I can try? Here is the line:
sheet1.Cells(rowindex, j).Formula = "=ROUND(SUM('" & firstsheetname & ":" & lastsheetname & "'!$" & colLetter & "$" & rowindex & "),0)"
So it comes out to "=ROUND(SUM('SHEET1:SHEET4'!$A$100),0)
The single quotes are needed as I may have a space in the worksheet name I am going across. This works when plugged directly into excel.