Is there any way to modify the categories data range of a chart using a macro?
In the object of the chart I can see the category data sequence in oChartDoc.DataSequences, but not in DataSeries.DataSequences, and I was not able to find a way to change oChartDoc.DataSequences.
I can change the chart.Ranges, StartRow and EndRow properties, but this then resets the selection of data ranges used by each series of the chart to a default order of the data for the chart.
I can also change oChartDoc.DataSourceLabelsInFirstColumn to false, this changes the categories to a data series, then I replace the data sequence of each series and set back to true. This also resets the selection of data ranges used by each series.
Trying to create a macro that will update all the charts in a spreadsheet document, after additional data has been added after scraping data from a website. I want the user to have freedom to create charts which will then be updated by the macro after new data is added. I want to avoid creating macro to recreate every chart type the user might want.
Attached example which includes my previous macro attempt. DataExtract.ods