Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CopyPicture method (# 1004) failed | Excel Programming | |||
PasteSpecial method for range class failed | Excel Programming | |||
Select method of range class failed | Excel Programming | |||
select method of range class failed | Excel Programming | |||
Copypicture method failed | Excel Programming |