Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default CopyPicture method of range class failed using RangeName

All,

I have a worksheet where I am cycling through some pivot tables and
copying a range defined by a range name to an output sheet. The range
being copied includes a chart and a few rows of cells. I keep getting
a
Run-Time Error '1004':
CopyPicture method of range class failed

I believe the syntax is correct because if I use error code and use
resume it will continue after 0 to <15 tries?

Anyone have any experience with this issue?

I have tried both options below:
Range(strRangeName).CopyPicture Appearance:=xlScreen,
Format:=xlPicture

Worksheets(SourceSheetName).Range(strRangeName).Co pyPicture
Appearance:=xlScreen, Format:=xlPicture
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default CopyPicture method of range class failed using RangeName

For debugging in the VBE first i would add a watch for strRangeName.
Sounds like that name sometimes isn't correct.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default CopyPicture method of range class failed using RangeName

I assume you are using Excel 2007. CopyPicture with the Format:=xlPicture
argument fails with Range and Chart objects. I filed this as a bug quite a
while ago together with a workaround, namely use Format:=xlBitmap

It seems there's no chance of it being fixed.

Regards,
Peter T

"CG" wrote in message
...
All,

I have a worksheet where I am cycling through some pivot tables and
copying a range defined by a range name to an output sheet. The range
being copied includes a chart and a few rows of cells. I keep getting
a
Run-Time Error '1004':
CopyPicture method of range class failed

I believe the syntax is correct because if I use error code and use
resume it will continue after 0 to <15 tries?

Anyone have any experience with this issue?

I have tried both options below:
Range(strRangeName).CopyPicture Appearance:=xlScreen,
Format:=xlPicture

Worksheets(SourceSheetName).Range(strRangeName).Co pyPicture
Appearance:=xlScreen, Format:=xlPicture



  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 9
Default CopyPicture method of range class failed using RangeName

On Jan 13, 2:28*pm, "Peter T" <peter_t@discussions wrote:
I assume you are using Excel 2007. CopyPicture with the Format:=xlPicture
argument fails with Range and Chart objects. I filed this as a bug quite a
while ago together with a workaround, namely use Format:=xlBitmap

It seems there's no chance of it being fixed.

Regards,
Peter T

"CG" wrote in message

...

All,


I have a worksheet where I am cycling through some pivot tables and
copying a range defined by a range name to an output sheet. *The range
being copied includes a chart and a few rows of cells. *I keep getting
a
Run-Time Error '1004':
CopyPicture method of range class failed


I believe the syntax is correct because if I use error code and use
resume it will continue after 0 to <15 tries?


Anyone have any experience with this issue?


I have tried both options below:
Range(strRangeName).CopyPicture Appearance:=xlScreen,
Format:=xlPicture


Worksheets(SourceSheetName).Range(strRangeName).Co pyPicture
Appearance:=xlScreen, Format:=xlPicture


Peter,

I am using XL 2003. I believe the problem was I had a subroutine that
used some small arrays of objects and I was not setting them to
Nothing at the end of the routine. They were called several times and
the more the code ran the bigger the problem got. Does that make
sense to anyone?

CG
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default CopyPicture method of range class failed using RangeName

Both versions of the code you originally posted should work fine in Excel
2003 or earlier, assuming you are returning a valid range object in the
first part before you get to .CopyPicture

If your object variable arrays are declared at procedure level there is no
need to explicitly destroy them, they will loose scope get cleaned up very
efficiently with VBA's garbage collection.

It's a good idea to destroy module level objects when not needed with Set
myObjVar = Nothing, however using them repeatedly should not cause any
problems.

Regards,
Peter T


"CG" wrote in message
Peter,


I am using XL 2003. I believe the problem was I had a subroutine that
used some small arrays of objects and I was not setting them to
Nothing at the end of the routine. They were called several times and
the more the code ran the bigger the problem got. Does that make
sense to anyone?


CG





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default CopyPicture method of range class failed using RangeName

Here is a workaround that I found by 'accident'.

There is a timing error with the CopyPicture function during run-time execution in Excel 2007.

To see if you have this error, after the program faults, go to the debugger screen (VBA Editor screen) and press 'F8', wait a couple seconds, 'F8' again, wait a couple seconds, etc.

If after several tries (with a wait between each) the program advances properly, then you need to add some Application.Wait commands prior to your CopyPicture command.

In my case, the solution required TWO separate Wait commands. The first was after a file close command about 5 lines up (a 5 second wait), then the second wait was just after the Set= command which sets the Range to be copied (a one second wait). The point being that I had to test several different Wait commands in different locations until I found the combination which worked.

Sure wish MS was a little more careful with their pointer/subsequent command management....

On Wednesday, January 13, 2010 12:22 PM CG wrote:


All,

I have a worksheet where I am cycling through some pivot tables and
copying a range defined by a range name to an output sheet. The range
being copied includes a chart and a few rows of cells. I keep getting
a
Run-Time Error '1004':
CopyPicture method of range class failed

I believe the syntax is correct because if I use error code and use
resume it will continue after 0 to <15 tries?

Anyone have any experience with this issue?

I have tried both options below:
Range(strRangeName).CopyPicture Appearance:=xlScreen,
Format:=xlPicture

Worksheets(SourceSheetName).Range(strRangeName).Co pyPicture
Appearance:=xlScreen, Format:=xlPicture



On Wednesday, January 13, 2010 12:22 PM minimaster wrote:


For debugging in the VBE first i would add a watch for strRangeName.
Sounds like that name sometimes is not correct.



On Wednesday, January 13, 2010 3:28 PM Peter T wrote:


I assume you are using Excel 2007. CopyPicture with the Format:=xlPicture
argument fails with Range and Chart objects. I filed this as a bug quite a
while ago together with a workaround, namely use Format:=xlBitmap

It seems there is no chance of it being fixed.

Regards,
Peter T



On Friday, January 22, 2010 8:28 AM CG wrote:


re
a
e
g

Peter,

I am using XL 2003. I believe the problem was I had a subroutine that
used some small arrays of objects and I was not setting them to
Nothing at the end of the routine. They were called several times and
the more the code ran the bigger the problem got. Does that make
sense to anyone?

CG



On Friday, January 22, 2010 11:17 AM Peter T wrote:


Both versions of the code you originally posted should work fine in Excel
2003 or earlier, assuming you are returning a valid range object in the
first part before you get to .CopyPicture

If your object variable arrays are declared at procedure level there is no
need to explicitly destroy them, they will loose scope get cleaned up very
efficiently with VBA's garbage collection.

it is a good idea to destroy module level objects when not needed with Set
myObjVar = Nothing, however using them repeatedly should not cause any
problems.

Regards,
Peter T



Submitted via EggHeadCafe - Software Developer Portal of Choice
PowerPoint Presentation from SharePoint Content via VSTO
http://www.eggheadcafe.com/tutorials...-via-vsto.aspx

  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default CopyPicture method of range class failed using RangeName

I was struggling with the very same issue than you and I think is nothing to do with our VBA code because after getting the error message I clicked DEBUG and pressed F8 to continue executing the code step by step, then I was able to skip the error.

Well, I did this and it worked for me:

I added this sentence:

rgToPic.Copy

immediately before this sentence:

rgToPic.CopyPicture Appearance:=xlScreen, Format:=xlBitmap

and I have never had the error in CopyPicture method again.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
CopyPicture method (# 1004) failed Dreiding Excel Programming 0 February 4th 08 11:27 PM
PasteSpecial method for range class failed Naga Kiran Excel Programming 1 October 17th 07 08:32 PM
Select method of range class failed sa02000[_5_] Excel Programming 5 October 5th 05 09:10 AM
select method of range class failed Joseph[_40_] Excel Programming 0 September 28th 04 04:08 PM
Copypicture method failed Kamal[_5_] Excel Programming 2 April 22nd 04 11:31 AM


All times are GMT +1. The time now is 09:14 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"