Charts.Add Alternative

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
936
This code allows me to copy/paste an image onto a blank chart object and then save the image to my computer. One thing really annoying is it must add a sheet for the blank chart object and then paste the blank chart object onto the main sheet and then delete the chart sheet. Can someone help me rewrite this code. Thanks

I tried this but I getting an error..
Set OHLCChart = Wks.ChartObjects.Add(0, 0, 600, 300)



VBA Code:
Sub ExportMyPicture2()
Dim MyChart As String, MyPicture As String, FName As String
Dim PicWidth As Long, PicHeight As Long

Application.ScreenUpdating = False
MyPicture = ActiveCell.Value

Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
MyChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)

With ActiveSheet
    With .Shapes(MyChart)
          .Width = 600
          .Height = 300
    End With

    .Shapes(MyPicture).Copy

    With ActiveChart
          .ChartArea.Select
          .Paste
    End With
    
    'FName = VBA.Environ("TEMP") & Application.PathSeparator & "MyPic.jpg"
    '.ChartObjects(1).Chart.Export FName
    .Shapes(MyChart).Delete
    '.Shapes(MyPicture).Delete
     'Kill FName
End With
End Sub
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What error?
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
936
When I try replacing this part of the code..

VBA Code:
Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
MyChart = Selection.Name & " " & Split(ActiveChart.Name, " ")(2)

With ActiveSheet
    With .Shapes(MyChart)
          .Width = 600
          .Height = 300
    End With

With

VBA Code:
Set OHLCChart = Wks.ChartObjects.Add(0, 0, 600, 300)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Again, what error? ;)
 

Av8tordude

Well-known Member
Joined
Oct 13, 2007
Messages
936

ADVERTISEMENT

1603109108492.png


The error occurs Set MyChart = ActiveSheet.ChartObjects.Add(0, 0, 600, 300)

VBA Code:
Sub ExportPic()
Dim MyChart As Object, MyPicture As String, FName As String
Dim PicWidth As Long, PicHeight As Long

Application.ScreenUpdating = False
MyPicture = ActiveCell.Value

Set MyChart = ActiveSheet.ChartObjects.Add(0, 0, 600, 300)

With ActiveSheet

    .Shapes(MyPicture).Copy

    With ActiveChart
          .ChartArea.Select
          .Paste
    End With
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the active sheet protected?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Which version of Excel?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,649
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I can't replicate the error in 2016.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,895
Messages
5,544,901
Members
410,643
Latest member
sng
Top