Error 4605 clipboard is empty or not valid: bug?

[philpinder]

Hi

What I want to do is to transfer an Excel cell of text (including its formatting) into Word, then parse the text, then send it back to Excel. About 35,000 times. (Why? because processing formatting is about 8 times faster in Word.)

The only way I can see of getting the cell and its text formatting from Excel to Word is to copy/paste it.

My VBA works fine, except that sometimes I get the awful "error 4605 clipboard is empty or not valid" message. Where, when, and how many times this happens appears completely random. I've tried using DoEvents, and Application.CutCopyMode = False, but this makes no difference.

Also, if I break out of the VBA when this occurs, I can manually "Ctrl+V" the offending cell (has marching ants around it) into another Excel cell, but in NotePad or Word nothing gets pasted. Very strange.

Two questions:
1. Is this a known bug in the Windows clipboard? I have Office 2007 and Windows XP. I've noticed that a lot of other people have this problem, but I can't find a solution (like a patch).

2. Is there another way of transferring via VBA the text in an Excel cell to Word, AND keep the text formatting?

Many thanks

Phil

[macropod]

The most obvious question is why you're copying/pasting "About 35,000 times." Is there a good reason you can't do it all with one copy/paste into Word, then another of the parsed data back to Excel?

[philpinder]

Because each cell that I copy/paste is part of a help topic in Excel, of which there are over 700. Some of these cells are referenced too. But even if I copy/paste only a handful of times, if I cannot trust the copy/paste mechanism 100%, then I'm in trouble! So my original questions remain:
Is this a known Windows clipboard bug and does it have a workaround?
Is there another way to transferring text from Excel to Word but preserving any text attributes?

Cheers
Phil

[macropod]

That still doesn't explain why you're copying each one individually. You could, for example, copy up to 63 columns of data at a time and paste them into Word as a table, all in one go.

Ultimately, your copy/paste problems are probably because the clipboard is empty when the error occurs. Try adding a 'DoEvents' call before/after each copy/paste cycle.

There are alternatives (eg LINK fields in Word), but they aren't really suitable for high-volume processing, and you'd still have to copy/paste the data to get it back from Word to Excel.