VBA Error 1004 - CopyPicture method of Range class failed

[szita2000]

Hi all.

I am trying to run the following code one after another for three different ranges.
The macro extracts the ranges and save them as pictures.

 Sub ExportBRRangeToPNG()  '--exports selected range to png file
  '  jpg is not appropriate format for this output
  '  default filename is address of selected range
  '  based on code example posted at:
  '     http://www.emoticode.net/visual-basic/vba-export-excel-range-as-image-and-save-as-file.html
  '  modified at
  '     http://www.mrexcel.com/forum/excel-questions/751500-export-range-cells-jpg.html#post3691601
  '  further refined by Jon Peltier, Peltier Technical Services, Inc.


  Dim vBRFilePath As String
  Dim rBRSelection As Range
  Dim sBRDefaultName As String
  Dim t As Variant


  If TypeName(Selection) <> "Range" Then
    MsgBox "Selection is not a range of cells."
    Exit Sub
  End If


  Set rBRSelection = Worksheets("DirMail").Range("A60:K65")
  
'  t = Now + TimeValue("00:00:01")
  
  


'  sBRDefaultName = Replace(rBRSelection.Address(0, 0, xlA1, 1), ":", "to") & ".png"
    sBRDefaultName = "Oralcare.png"
  vBRFilePath = "G:\ASTER\11 Production\DDS\Aster DDS output files\BR.png"
  '--exit if cancelled by user
'  If vBRFilePath = False Then Exit Sub


  '-- copy selected range as picture (not as bitmap)
  Application.CutCopyMode = False
[B][COLOR=#b22222]  rBRSelection.CopyPicture Appearance:=xlScreen, Format:=xlPicture[/COLOR][/B]


  '--Create an empty chart, slightly larger than exact size of range copied
  With ActiveSheet.ChartObjects.Add( _
      Left:=rBRSelection.Left, Top:=rBRSelection.Top, _
      Width:=rBRSelection.Width + 0.1, Height:=rBRSelection.Height + 0.1)


    With .Chart
      ' clean up chart
      .ChartArea.Format.Line.Visible = msoFalse
      .ChartArea.Select
      ' paste and position picture
      .Paste
      
      
'      Do While t > Now
'        DoEvents
'      Loop
        
      
'      With .Pictures(1)
'        .Left = .Left
'        .Top = .Top
'      End With
      
      ' export
      .Export CStr(vBRFilePath)
    End With
    
    ' remove no-longer-needed chart
    .Delete
  End With
End Sub

I am calling the same macro three times, each time extracting a different range.

Sub ExporttoJpg()




Call ExportOCRangeToPNG
Call ExportBRRangeToPNG
Call ExportSummaryRangeToPNG


End Sub

On the line that is red I receive random 1004 - CopyPicture method of Range class failed errors.

I can run the three macros a number of time then the error pops up. - But not at the same subroutine ?!?

I clear the error and it runs again.
The line it craps out, is always the .copypicture one but in different subs.

Any idea of what am I doing wrong?

 

[JLGWhiz]

I am calling the same macro three times, each time extracting a different range.

The statment does not correspond with this part of your post:

Sub ExporttoJpg()
Call ExportOCRangeToPNG
Call ExportBRRangeToPNG
Call ExportSummaryRangeToPNG
End Sub

It looks like you are calling three separate macros from a control macro. It is possible that residual memory is building up and causing problems. You could try setting your Object variables to Nothing before the end of each of the three called macros, which would dump any stored memory for that variable. Those would normally be dumped when the macro ends, but since they are called within a control macro, they might no be dumped, so setting them to Nothing will cover that scenario. Errors in multiple copy and paste in Excel is not uncommon. I have seen several posts about that issue, but I do not recall ever seeing one that really tied down the cause of it.

 

[szita2000]

Hi JLGWhiz.

Thanks for the reply.
I meant that I am calling the "same" macro three times, that I am calling three different macro (didn't wanted to copy the long code three times).
I think you are right about the memory, as when I tested the macro (and ran the control macro) sometimes it ran O.K., but when I repeated the control macro a bit quicker, then it crapped out.

How would I set the objects to nothing?

-Would it go in between the three calls in the control macro or the end of each subroutine?
-Which variables would I dump?, would an Application.CutCopyMode = False be enough?

Thanks

Tom

 

[JLGWhiz]

In the macro you posted, just before the 'End Sub' line put:

Set rBRSelection = Nothing

That frees the reserved memory for that Object variable. You only need to do it for Object variables, not for the variables that represent text or values. Do that for the object variable you set in each of your called macros.