• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1408
  • Last Modified:

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
0
victoriaharry
Asked:
victoriaharry
  • 3
  • 2
2 Solutions
 
xtermieCommented:
what about adding a line before referencing the new chart as ActiveChart to activate it?
oCht.Activate
0
 
xtermieCommented:
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?
0
 
victoriaharryAuthor Commented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rory ArchibaldCommented:
You could also use an embedded chart

Set oCht = Activesheet.Shapes.AddChart.Chart

oCht.Chartarea.clearcontents

Open in new window

0
 
xtermieCommented:
Can you post some sample data or file so i can recreated what you are saying...
0
 
Rory ArchibaldCommented:
If you have data cells selected Excel will try and be helpful by charting them - that's why you need to clear the chart area. (by using Charts.Add you create a chart sheet and the parent of a chart sheet is a Workbook which has no Delete method)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now