PasteSpecial method of Range class failed Error 1004

[Susan]

I've reviewed quite a few posts on this topic but I wonder if my particulars are causing the problem. I did find some something about this breaking when pasting between worksheets.

One computer uses WinXp, Excel 2003, .xls spreadsheet
Other computer uses Win7, Excel 2010, compatibility mode .xls spreadsheet

On the XP computer, this coding (which copies material from a Word document and pastes it into Excel) breaks occasionally, but on the Win7 computer it breaks frequently, almost continuously.

This is the section that is breaking:

ws1.Activate
Set rNewWord = ws1.Range("L1")
rNewWord.PasteSpecial (xlPasteValues) <----ALWAYS THIS LINE

The curious thing is that it is not a true break - if I hit Debug and then Run, it continues merrily along until it breaks again - sometimes on the next loop (more in Win7), sometimes in 5000 loops (more in WinXP).

Everything is dimmed and set.

Because this is a large macro extracting >100k strings, I have this set up to clear the clipboard (copy/paste without using the clipboard didn't seem possible between applications).

If lLoop = 100 Then
Call ClearClipboard
lLoop = 0
End If

I don't think this is the problem; I'm just letting you know in case anybody thinks it might be because the clipboard is full.

Any thoughts would be appreciated.

Thank you!
Susan

 

[GS]

It might be better, easier, and faster if you load the strings into an
array and then dump the 'values' into your worksheet. Since you're
assigning 'values' then there should be no need to use Copy/Paste (or
PasteSpecial). This eliminates the additional overhead associated with
using the Clipboard because you can assign the values directly to the
ranges from the array.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

On the XP computer, this coding (which copies material from
a Word document and pastes it into Excel) breaks occasionally,
but on the Win7 computer it breaks frequently, almost continuously.
This is the section that is breaking:
ws1.Activate
Set rNewWord = ws1.Range("L1")
rNewWord.PasteSpecial (xlPasteValues) <----ALWAYS THIS LINE
The curious thing is that it is not a true break - if I hit Debug
and then Run, it continues merrily along until it breaks again [....]
Because this is a large macro extracting >100k strings, I have
this set up to clear the clipboard [....]
If lLoop = 100 Then
Call ClearClipboard
lLoop = 0
End If

I have little idea what is going wrong. I suspect the problem is with the
copy operation, not the paste operation.

But your comment about things working when you continue from the break
sounds like it might be a timing issue. Try the following:

ws1.Activate
DoEvents ' <---- ADD THIS
Set rNewWord = ws1.Range("L1")
rNewWord.PasteSpecial (xlPasteValues)

and

If lLoop = 100 Then
Call ClearClipboard
DoEvents ' <---- ADD THIS
lLoop = 0
End If

I do not have high hopes that will fix anything. I'm not sure how effective
DoEvents on a computer with multiple CPUs and multiple cores. But it won't
hurt -- other than adding some extra time to what must be a long-running
loop already. :-(

To minimize the performance hit, you might try the following alternative:

Dim myErr as Long
ws1.Activate
Set rNewWord = ws1.Range("L1")
On Error Resume Next
rNewWord.PasteSpecial (xlPasteValues)
myErr = Err
On Error GoTo 0
If myErr <> 0 Then
DoEvents
rNewWord.PasteSpecial (xlPasteValues)
End if

Of course, you might put that in a loop; but I would cap the number of
iterations.

And you might consider replacing DoEvents with:

Application.Wait Now() + #00:00:01#

Caveat: That is deceptive. It might wait almost no time at all, even less
than 15.625 msec.

 

[Susan]

Thank you, Joe and Gary, for your thoughts and ideas.

I don't have any experience using arrays, but since I taught myself VBA, I know I can find enough information on the Internet (and in this group) to learn how to use it.

But I will probably try the Do Events first, just because that's obviously easier than learning a new trick. Ha ha.

It's strange that it breaks so easily on Win7 but runs quite a bit longer on WinXP.

Susan

 

[GS]

I agree with Joe that the issue lies more likely with 'Copy' since
Paste or PasteSpecial will always throw an exception if the Clipboard
is empty.

As for using arrays in VB[A].., wel it can get rather wieldy if you're
not careful. I don't use Word and so don't know enough about its
objects/methods to explain how to pull text from a document into an
array, but I'm sure you'll find lots about that in word groups. Once
the text is loaded it's a trivial task to assing values to ranges. In
Excel we put a range into a Variant type variable and it results in a
2D, one-based array that we can iterate for values based on row/col
index within the array.

If, as you say, you have 100Ks of strings to process then acessing the
worksheet one by one will take a monumental length of time compared to
working with an array and 'dumping' the results into the worksheet<g>.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Oops.., some typos...

I agree with Joe that the issue lies more likely with 'Copy' since
Paste or PasteSpecial will always throw an exception if the Clipboard
is empty.

As for using arrays in VB[A].., well it can get rather wieldy if

you're not careful. I don't use Word and so don't know enough about
its objects/methods to explain how to pull text from a document into
an array, but I'm sure you'll find lots about that in word groups.

Once the text is loaded it's a trivial task to assign values to

ranges. In Excel we put a range into a Variant type variable and it
results in a 2D, one-based array that we can iterate for values based
on row/col index within the array.

If, as you say, you have 100Ks of strings to process then acessing
the worksheet one by one will take a monumental length of time
compared to working with an array and 'dumping' the results into the
worksheet<g>.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Susan]

Well, I don't know what the problem was exactly - maybe it *was* the clipboard not being cleared despite my programming - but adding the two simple DoEvent lines stopped the problem completely on the Win7 computer. I assumeit will be the same on the WinXP computer, too, since Win7 gave me the most trouble.

And I may look into arrays anyway, as the program does take quite a long time to run - up to six hours. :/ It just ties up a fairly unused desktop computer (the WinXP) so right now I leave it running overnight, but shorter is always better!

Thanks again for your help, gentlemen!

Susan

 

[GS]

Well, I don't know what the problem was exactly - maybe it *was* the

clipboard not being cleared despite my programming - but adding the
two simple Do Event lines stopped the problem completely on the Win7
computer. I assume it will be the same on the WinXP computer, too,
since Win7 gave me the most trouble.

And I may look into arrays anyway, as the program does take quite a
long time to run - up to six hours. :/ It just ties up a fairly
unused desktop computer (the WinXP) so right now I leave it running
overnight, but shorter is always better!

Thanks again for your help, gentlemen!

Susan

Well.., given the number of strings, ..no surprise it take hours the
way you're going about it now. You'll appreciate, then, getting this
task done in relatively few minutes once you start processing text in
memory<g>!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

Well, I don't know what the problem was exactly - maybe it
*was* the clipboard not being cleared despite my programming
- but adding the two simple Do Event lines stopped the problem
completely on the Win7 computer.

It might be coincidence. To be "sure" (never really), try commenting out
the DoEvent calls, and see if the problem returns.

My theory is based on the fact that some operations return before they are
completed. Perhaps that is the case with Copy and/or clearing the
clipboard. I don't know.

I suspect the problem is more likely on the Win7 computer because of
differences in the two computers. For example, perhaps the Win7 computer
has multiple multi-core CPUs, and the WinXP system does not.