PDA

View Full Version : The paste method of the _chart object failed



Romulo Avila
05-05-2017, 03:35 PM
Good evening,
I am using this code that I found on the internet, however when running in Excel2013 the following message appears
"Run-time error 1004
The paste method of the _chart object failed.
How can I solve.

Thank you

Private Sub Etapa2GIF()
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
TempFile = VBA.Environ$("temp") & "\"
Application.ScreenUpdating = False
Set rng = Plan1.Range("B44:P46")
rng.CopyPicture xlScreen, xlPicture
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width, rng.Height)
cht.Chart.Paste
cht.Chart.Export TempFile & "Etapa2.gif"
cht.Delete
ExitProc:
Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub

mana
05-06-2017, 06:01 AM
try this


DoEvents
DoEvents
DoEvents
cht.Chart.Paste

Romulo Avila
05-08-2017, 09:50 AM
Mana,

Good afternoon,

I tried as you requested and even then the problem continues.

mdmackillop
05-09-2017, 02:37 AM
https://msdn.microsoft.com/en-us/library/office/ff198129.aspx


Private Sub Etapa2GIF()
Dim TempFile As String
TempFile = VBA.Environ$("temp") & "\" & "Etapa2.gif"
ActiveSheet.ChartObjects(1).Chart.Export Filename:=TempFile, FilterName:="GIF"
End Sub

Romulo Avila
05-09-2017, 06:31 AM
Mdmackillop,

Good Morning,


I am new to VBA, I tried to run from the sub that you put and gives the following error message.


"Application definition or object definition error"


How should I proceed to adjust your SUB to my macro?


Thank you very much for your support.

mdmackillop
05-09-2017, 07:23 AM
Can you post your workbook? Use Go Advance/Manage Attachments.

Romulo Avila
05-09-2017, 07:40 AM
Good Morning,


Attached file.

mdmackillop
05-09-2017, 08:44 AM
Your project code is locked and I see no chart nor data to create one

Romulo Avila
05-09-2017, 08:53 AM
mdmackillop

Good afternoon,
Sorry for the carelessness.
It does not really have graphics, it saves in GIF the defined area.

mdmackillop
05-09-2017, 10:08 AM
Your original code basically uses the same method as this but looks a lot simpler. This seems to work.

Option Explicit
Sub Test()
Dim sht As Worksheet, shS As Worksheet
Dim TempFile As String

Application.ScreenUpdating = False
Set shS = ActiveSheet
Set sht = Sheets.Add(After:=shS)


shS.Range("E44:L46").Copy
sht.Pictures.Paste

TempFile = VBA.Environ$("temp") & "\" & "Etapa2.gif"
Call ExportPic(sht, TempFile)
Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Sub ExportPic(sht As Worksheet, fname As String)
'Based upon https://stackoverflow.com/questions/25333558/export-pictures-excel-vba


Dim MyChart As Chart
If InStr(sht.Shapes(1).Name, "Picture") > 0 Then
'create chart as a canvas for saving this picture
Set MyChart = Charts.Add
MyChart.Name = "TemporaryPictureChart"
'move chart to the sheet where the picture is
Set MyChart = MyChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)


'resize chart to picture size
MyChart.ChartArea.Width = sht.Shapes(1).Width
MyChart.ChartArea.Height = sht.Shapes(1).Height
MyChart.Parent.Border.LineStyle = 0 'remove shape container border


'copy picture
sht.Shapes(1).Copy


'paste picture into chart
MyChart.ChartArea.Select
MyChart.Paste


'save chart as jpg
MyChart.Export fname, "gif"


'delete chart
sht.Cells(1, 1).Activate
sht.ChartObjects(sht.ChartObjects.Count).Delete
End If
End Sub

mdmackillop
05-09-2017, 10:15 AM
Now I've seen the principle, try this

Private Sub Etapa2GIF()
Dim rng As Excel.Range
Dim cht As Excel.ChartObject
TempFile = VBA.Environ$("temp") & "\"
Application.ScreenUpdating = False
Set rng = Plan1.Range("B44:P46")
rng.CopyPicture xlScreen, xlPicture
Set cht = ActiveSheet.ChartObjects.Add(0, 0, rng.Width, rng.Height)
cht.Select
cht.Chart.Paste
cht.Chart.Export TempFile & "Etapa2.gif"
cht.Delete
ExitProc:
Application.ScreenUpdating = True
Set cht = Nothing
Set rng = Nothing
End Sub