VBA Error 1004 : Method "Close" of Chart.ChartData.Workbook object Failed

0
votes

I have a macro that updates a ppt presentation charts from a monthly updated Excel file. It has been working like a charme for a couple of months. Since a few days ago, I have a problem to close the charts Workbook after updating their numbers and I get a 1004 runtime error.

Here is the code : 

    With .Slides(1).Shapes("gmbDiscoveryRateChart").Chart.ChartData
    .Activate
    .Workbook.Sheets(1).Range("B2").value = currentGmbDiscoveryRate
    .Workbook.Sheets(1).Range("B3").value = 1 - currentGmbDiscoveryRate
    .Workbook.Close '<-- Error here
    End With
2
excelvbapowerpoint
Does the error occur if you step through the code? – John Korchok
Yes, I have the same error while debugging step by step the code – toumz
This happends because in the With-Block you reference the ChartData. Try to close the Workbook outside the with-block. Delete the line and after the End Withwrite Thisworkbook.Close – MGP

2 Answers

0
votes

It seems that there is a bug in PowerPoint or Excel. I had the same issue and I disabled automated updates in Office 365 (File -> Account -> Office Updates) and reverted it to version 16.0.11629.20214 (June 2019).

CMD as admin:

cd %programfiles%\Common Files\Microsoft Shared\ClickToRun\
OfficeC2RClient.exe /update user updatetoversion=16.0.11629.20214

I hope it helps.

0
votes

Try Workbook.Application.Quit It might help.