Excel 2010 Changing Pivot Chart Line Format On Workbook.SaveAs

Question

I've written a bunch of VBA for Excel 2010 to retrieve data from an Access database, place the pivot table into a new workbook and create a pivot chart based on that pivot table with two data series. One series is a bar and the second series is a line on which I have set the .MarkerStyle to xlMarkerStyleNone. The macro also saves the new workbook.

When I open that saved workbook, I find that the line now has markers on it.

In trying to find out what's going on here, I have hit [CTRL]-[BREAK] to stop the macro after the chart is formatted and before it's saved, so I can see what's in the chart format. I can see that the marker style has been set to none when I right-click the series and see the properties, and also by verifying that by seeing that .MarkerStyle on the relevant series is still set to xlMarkerStyleNone.

When I go to save the new workbook manually with [CTRL]-[S], it saves just fine and loads up without the line markers. But when I save the file with save-as ([ALT]-[F], [A]), I see the markers appear after the file is saved.

So I don't think it's code that's doing this, but the Workbook.SaveAs method. But I'll show relevant code just in case.

I have used both xlExcel12 and 50 as the FileFormat when saving as a .xlsb file, and both xlOpenXMLWorkbook and 51 when saving as a regular .xlsx file. This has made no difference, either.

Update: I have also tried the Workbook.Save method, which just saves the new workbook I create as Book1.xlsx, and it has the same problem.

    ' Chart Formatting
    reportSheet.Shapes(10).Chart.SeriesCollection(2).ChartType = xlLine
    reportSheet.Shapes(10).Chart.SeriesCollection(2).Format.Line.Visible = msoTrue
    reportSheet.Shapes(10).Chart.SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(186, 7, 67)
    reportSheet.Shapes(10).Chart.SeriesCollection(2).Format.Line.Transparency = 0
    reportSheet.Shapes(10).Chart.SeriesCollection(2).Format.Line.Weight = 1.5
    reportSheet.Shapes(10).Chart.SeriesCollection(2).MarkerStyle = xlMarkerStyleNone

    ' Saving the chart
    reportbook.SaveAs fileName:=saveFileSpec, FileFormat:=xlOpenXMLWorkbook

I'm expecting the workbook to save without any changes being made in the save process when using the Workbook.SaveAs method.

It's not uncommon for pivot charts to change formatting when the data is refreshed. Specifically, custom formatting is likely to be lost. If SeriesCollection(1) is xlLineMarkers (i.e., if the whole chart is xlLineMarkers), then when you openi the workbook and the pivot table is refreshed, the customized SeriesCollection(2) reverts to the default xlLineMarkers. If this is the case, you could try making the chart xlLine, and changing SeriesCollection(1) to xlLineMarkers. — Jon Peltier

Allan Lewis Allan Lewis · Accepted Answer · 2019-05-01T06:45:56

OK, this is silly, but I've gotten around it by drum roll formatting-and-saving twice. This will do for now, but I'll try Jon Peltier's suggestions at some point and report back.

Excel 2010 Changing Pivot Chart Line Format On Workbook.SaveAs

1 Answers