asked on

Excel VBA - ActiveChart.Parent.Delete causing error

Hi Guys,

I'm trying to export a range of cells in excel as an image using the code below. The export is working fine however the image is displaying a column chart underneath the image which seems to be created when I add the chart. Even If I don't select a range to copy and just add the chart it defaults to a column chart using some of the data on the sheet (very frustrating) The image I copied doesn't cover the whole chart screen so part of the column chart below is being displayed in the output.
I tried recording a macro by adding the chart manually then deleting the column chart that is appearing. I noticed a ActiveChart.Parent.Delete code is being used. When I attempt to use this code in my VBA below It throws an error
Any ideas on how I can ensure the chart is clear before I copy my image into it would be very appreaciated

DimDim oRange As Range
Dim oCht As Chart

Set oRange = Range("E3:N13")
Set oCht = Charts.Add

ActiveChart.Parent.Delete ' This is throwing an error (ie doesn't support)

oRange.CopyPicture xlScreen, xlPicture

oCht.Paste

oCht.Export Filename:="C:\Temp\test.png", Filtername:="PNG"

Thanks

Gavin

Anastasia D. Gavanas

what about adding a line before referencing the new chart as ActiveChart to activate it?
oCht.Activate

Anastasia D. Gavanas

OK i think I get your problem
If you delete the problematic line does it still cause a problem.
I tried this:

Sub xx()
Dim oRange As Range
Dim oCht As Chart
Set oRange = Range("E3:N13")
Set oCht = Charts.Add
oCht.Activate
oRange.CopyPicture xlScreen, xlPicture
oCht.Paste
oCht.Export Filename:="C:\Temp\test.png", Filtername:="PNG"
End Sub

Open in new window

and I got a chart sheet with an image of the cells you call on the top right corner.

If this doesnt work could you please post a sample of the excel file and what you expect the outcome to be?

victoriaharry

ASKER

Without the activechart.parent.delete line the code will run however I get a image which contains the cells I want plus a chart that was created when I first added the chart sticking out from underneath it. I don't know why when I add a blank chart either manually or by code it draws a chart using some data from one of the sheets

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anastasia D. Gavanas

Can you post some sample data or file so i can recreated what you are saying...

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial