Excel Chart Disappears when updating formula via VSTO

Posted on 2008-11-04
Medium Priority
Last Modified: 2013-11-10
I am trying to update the range of a chart using VSTO. In order to do so, I get the start and end date desired by the user, lookup the range in column a (always the date) and change the chart formula to correspond to the new values.  However, when updating a chart that has a secondary axis, the chart will disappear from the sheet after it is updated. An interesting note is that this appears to happen immediately after the file is opened, and the graph is not on the screen. If I select the chart, the run the code to update the range, everything works fine.

Any ideas on what is causing the charts to disappear?
For c As Integer = 1 To chtSeries.Count
            Dim ser As Excel.Series = chtSeries.Item(c)
            Dim form As String
                form = ser.Formula
            Catch ex As Exception
                If MsgBox("Could not access the formula for the Series Name: " & ser.Name & " on the chart: " & cht.ChartTitle.Text & _
                          ". Do you want to continue?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                    Continue For
                    Throw New Exception("Stopped processing Graphs because of a charting error")
                End If
            End Try
            'Pull out the formula information
            Dim paramsMatch As RegularExpressions.Match = Regex.Match(form, "\((.*)\)")
            If paramsMatch.Success Then
                Dim params As Collections.Generic.List(Of String) = paramsMatch.Groups(1).Value.Split(",").ToList
                Dim x As String = params(1)
                Dim y As String = params(2)
                'Get the x portion of the formula
                'Could add some additional checking on the matches
                Dim xMatch As RegularExpressions.Match = Regex.Match(x, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)")
                If (xMatch.Success) Then
                    Dim xSheet As String = xMatch.Groups(1).Value.Replace("'", "")
                    'Look for secondary workbook, This gets tricky when people link to other sheets for data,
                    '  We cannot just guess where we are based on this sheet
                    Dim xWorkbook As String = ""
                    Dim xWorkMatch As RegularExpressions.Match = Regex.Match(xSheet, "\[(.+?)\](.+)")
                    If xWorkMatch.Success Then
                        xSheet = xWorkMatch.Groups(2).Value
                        xWorkbook = xWorkMatch.Groups(1).Value
                    End If
                    Dim xStartCol As String = xMatch.Groups(2).Value
                    Dim xStartRow As Integer = CType(xMatch.Groups(3).Value, Integer)
                    Dim xEndCol As String = xMatch.Groups(4).Value
                    Dim xEndRow As Integer = CType(xMatch.Groups(5).Value, Integer)
                    'Make sure we are dealing with the date column
                    If Not xStartCol = "A" Then
                        Continue For
                    End If
                    'Lookup date X
                    Dim xStartRowLookup As Excel.Range
                        If xWorkbook.Length > 0 Then
                            xStartRowLookup = App.Workbooks(xWorkbook).Worksheets(xSheet).range("A:A")
                            xStartRowLookup = App.Worksheets(xSheet).Range("A:A")
                        End If
                    Catch ex As Exception
                        'If we can't find the sheet, we can't know if we are using date (or row A)
                        ErrStr &= "Graphing Error:The sheetname is quite odd: " & xSheet & " for data in graph: " & cht.ChartTitle.Text & vbNewLine
                        Continue For
                    End Try
                    Dim xStartRowNumber As Integer
                        xStartRowNumber = App.WorksheetFunction.Match(App.WorksheetFunction.EDate(startDate.Date, 0), xStartRowLookup, 0)
                    Catch ex As Exception
                        xStartRowNumber = common.EstimateRangeFromRawData(startDate, WorkBook:=xWorkbook)
                    End Try
                    'Lookup end date X
                    Dim xEndRowLookup As Excel.Range
                    If xWorkbook.Length > 0 Then
                        xEndRowLookup = App.Workbooks(xWorkbook).Worksheets(xSheet).range("A:A")
                        xEndRowLookup = App.Worksheets(xSheet).Range("A:A")
                    End If
                    Dim xEndRowNumber As Integer
                        xEndRowNumber = App.WorksheetFunction.Match(App.WorksheetFunction.EDate(endDate.Date, 0), xEndRowLookup, 0)
                    Catch ex As Exception
                        'Lets guess where it should be
                        xEndRowNumber = common.EstimateRangeFromRawData(endDate, WorkBook:=xWorkbook)
                    End Try
                    Dim nFormula As String = ser.Formula
                    nFormula = Regex.Replace(nFormula, "\$" & xStartRow & ":", "$" & xStartRowNumber.ToString & ":")
                    nFormula = Regex.Replace(nFormula, "\$" & xEndRow & ",", "$" & xEndRowNumber.ToString & ",")
                    'nFormula = Regex.Replace(ser.Formula, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)", nFormula)
                    ser.Formula = nFormula
                    'Set the x series
                    'Dim xRange As Excel.Range
                    'xRange = App.Worksheets(xSheet).Range(xStartCol & xStartRowNumber, xEndCol & xEndRowNumber)
                    'cht.Chart.SetSourceData(xRange, Excel.XlRowCol.xlColumns)
                End If
                'Create new Range
                'Add new series range
            End If

Open in new window

Question by:crymsan
1 Comment

Accepted Solution

crymsan earned 0 total points
ID: 22885647
The only workaround I have found is to not edit the formula directly, and update the values in the graph by using the XValues and Values property of the series.
                    Dim yMatch As RegularExpressions.Match = Regex.Match(y, "(.+?)!\$(.+?)\$(.+?):\$(.+?)\$(.+)")
                    If yMatch.Success Then
                        If xWorkbook.Length > 0 Then
                            ser.XValues = App.Workbooks(xWorkbook).Worksheets(xSheet).Range("A" & xStartRowNumber & ":A" & xEndRowNumber)
                            ser.Values = App.Worksheets(xSheet).Range(yMatch.Groups(2).Value & xStartRowNumber & ":" & yMatch.Groups(4).Value & xEndRowNumber)
                            ser.XValues = App.Worksheets(xSheet).Range("A" & xStartRowNumber & ":A" & xEndRowNumber)
                            ser.Values = App.Worksheets(xSheet).Range(yMatch.Groups(2).Value & xStartRowNumber & ":" & yMatch.Groups(4).Value & xEndRowNumber)
                        End If
                        ErrStr &= "Cannot get the Y Axis Columns"
                    End If

Open in new window


Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

601 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