X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Updating A Chart Located In Ms Excel Using Code Run In Access, Any Version    
post Mar 24 2019, 10:14 AM

Posts: 1,142
Joined: 15-January 06

I am having difficulty with the chart type displayed after updating an existing MS 2016 Excel chart from MS 2016 Access. The chart type seems to change to a "subtype". The first attachment shows the chart prior to updating; there are only single blue column bars. The second attachment shows the chart after updating with the code below. The orange bars are the correct results; I don't know what the blue bars on this chart are supposed to be (perhaps the fiscal years??). If I go to Excel and right click on the chart and select "Change Chart Type", I see three possibilities for the "Clustered Column Chart" (see third attachment). The second option in the third attachment, where all of the column bars are blue, is the one I want; a single column chart, which is what the chart WAS prior to updating the data.

The code is shown below (there is an OpenExcel procedure that runs first to open the template spreadsheet and defines the module-level variables xls and xlsSheet). Since I am updating an Excel template, the name of the Excel sheet and the name of the chart on that sheet are known and are passed into the routine. myRange is something like $A$82:$A$86, $N$82:$N$86.

Under the section of the code labeled 'Now update with new data', I've tried two possibilities to add the new data. Both yield the same result.

Public Sub UpdateChart(DataSheetName As Variant, ChartName As Variant, myRange As Variant)

Dim objChart As ChartObject

Set xlsSheet = xls.Worksheets(DataSheetName)
Set objChart = xlsSheet.ChartObjects(ChartName)


'First clear the chart of data

'Now update  with new data
objChart.Chart.SeriesCollection.Add SOURCE:=xlsSheet.Range(myRange)
'objChart.Chart.SetSourceData SOURCE:=xlsSheet.Range(myRange)

Set objChart = Nothing

End Sub

I don't understand what Excel is doing. Why is it changing the chart type to, I guess a subtype? What happened to the X-axis values which were years and now are numbers. $A$82:$A$86 contains the years 2014, 2015, 2016, 2017, 2018. But Excel has 1, 2, 3, 4, 5. Yet, if I right click the chart I can select the chart displayed the way it should be (i.e. the same format as the original chart, just different data).

Thanks for any help.
This post has been edited by bobdee: Mar 24 2019, 10:25 AM
Attached File(s)
Attached File  Existing_Chart.JPG ( 36.62K )Number of downloads: 0
Attached File  Updated_Chart.JPG ( 37.54K )Number of downloads: 0
Attached File  Chart_Type_Options.JPG ( 41.36K )Number of downloads: 0
Go to the top of the page
post Mar 25 2019, 04:05 AM

Posts: 126
Joined: 11-October 18

To just update an existing series, I'd use this:

    With xlsSheet.Range(myRange)
        Dim currentSeries As Object
        Set currentSeries = objChart.Chart.SeriesCollection(1)
        If .Areas.Count > 1 Then
            currentSeries.Values = .Areas(2)
            currentSeries.XValues = .Areas(1)
            currentSeries.Values = .Columns(2)
            currentSeries.XValues = .Columns(1)
        End If
    End With

Note: remove the line that clears the contents of the chartarea.
This post has been edited by Debaser: Mar 25 2019, 04:14 AM
Go to the top of the page
post Mar 25 2019, 12:57 PM

Posts: 1,142
Joined: 15-January 06

Thanks! That code works well.
Go to the top of the page
post Mar 25 2019, 05:07 PM

Posts: 1,142
Joined: 15-January 06

As I mentioned previously, the code works well at not messing up the chart type. However, if the x values have increased in number, the new resulting chart does not take that into account. For example, if the fiscal year values on the original chart are 2014,2015,2016, and 2017 and then I want to update the series with data that has fiscal year values of 2014,2015,2016, 2017 AND 2018, the resulting chart only shows data through 2017. It does not get updated to add additional data.

Any help is appreciated. Thanks.

Go to the top of the page
post Mar 25 2019, 07:48 PM

Posts: 1,142
Joined: 15-January 06

Please ignore my previous post. The code provided by Debaser works fine.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    20th July 2019 - 07:38 PM