[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel VBA - ActiveChart.Parent.Delete causing error

Posted on 2012-09-13
6
Medium Priority
?
1,349 Views
Last Modified: 2012-09-13
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
Comment
Question by:victoriaharry
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:xtermie
ID: 38394281
what about adding a line before referencing the new chart as ActiveChart to activate it?
oCht.Activate
0
 
LVL 18

Expert Comment

by:xtermie
ID: 38394295
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
 

Author Comment

by:victoriaharry
ID: 38394324
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 38394327
You could also use an embedded chart

Set oCht = Activesheet.Shapes.AddChart.Chart

oCht.Chartarea.clearcontents

Open in new window

0
 
LVL 18

Expert Comment

by:xtermie
ID: 38394746
Can you post some sample data or file so i can recreated what you are saying...
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 38394807
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question