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

Posted on 2008-11-07
Last Modified: 2013-11-05
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.
Question by:sbornstein2
    LVL 4

    Accepted 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


    Author Comment

    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: '

    Author Closing Comment

    this is an issue with this software.  thanks for your help

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
    Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now