Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 379
  • Last Modified:

ASP.Net/VB.Net - EXCEL Question for a Formula Issue

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.
  • 2
1 Solution
Please try the following:
sheet1.Cells(rowindex, j).Formula = String.Format("=ROUND(SUM('{0}:{1}'!${2}${3}),0)", firstsheetname , lastsheetname, colLetter, rowindex)

Open in new window

sbornstein2Author Commented:
unfortunately same issue, no idea why its throwing this:

Additional information: Failed to parse: =ROUND(SUM('AMOCO:ZAC DU PARC'!$G$289),0). Error: Not expected: '
sbornstein2Author Commented:
this is an issue with this software.  thanks for your help

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now