Clear Office Clipboard from VBA?

[Guest]

I found some code on the web to manipulate the Windows clipboard using
windows API calls.

Is there a way to manipulate the MS Office clipboard?

Here's the scenario. I've got some code that parses through a Word document
and copies all the tables and images from the Word document into an Excel
Workbook. The tables all work fine, but the images are sometimes duplicated.
Instead of getting image 1 and then image 2, I get two copies of image 1.

Even though I clear the clipboard (using the Windows API calls) before I
copy the figure, sometimes I still get the duplicate.

I ran my code with the Office clipboard command bar showing and it looks
like the Office clipboard doesn't get cleared at all. To show the Office
clipboard, I'm just choosing View-->Toolbars-->Clipboard.

Is there a way to clear the Office clipboard?

Thanks in advance for the help!

(Forgive the cross-post. I'm not sure where to post the question.)

 

[Guest]

Hi theLuggage -

Check out this site. Chip Pearson provides crystal-clear instructions for
clearing the clipboard with VBA code.

http://www.cpearson.com/excel/clipboard.htm

 

[Karl E. Peterson]

That's the Windows clipboard, not the Office one, no? I'm not aware of methods to
access the Office clipboard from VBA, but would love to learn that isn't because
it's impossible!

 

[Guest]

There is more than one clipboard???

 

[Guest]

You can access the Office Clipboard Edit>OfficeClipboard in Excel. It should
show what is on it, if anything, from whatever source. You can then edit it
one item at a time or all at once. See Excel help on Clipboard.

 

[Guest]

I am using the Windows API calls exactly from the cpearson web site. That's
where I got the code to begin with. (But thanks for the pointer anyway.)

That's not solving the problem, though. As I mentioned, it appears that
Office has it's own clipboard implementation.

 

[Guest]

I meant to say this in my original post: we're using Office 2000 for
Windows. There is no Edit-->OfficeClipboard in Office 2k that I can see.

In any case, I need to do this in VBA code, not via a UI.

 

[Guest]

Also, Chip's method only works for text. I'm having problems with Images.

Thanks again for the help. I really do appreciate it.

 

[Karl E. Peterson]

Also, Chip's method only works for text. I'm having problems with Images.

EmptyClipboard truly empties the clipboard of _all_ formats -- text, images,
metafiles, younameit. But, as you found, that's the *Windows* clipboard, not this
hybrid "thing" offered by Office.

 

[Tony Jollans]

If you are using Office 2000 you do have some indirect access to the Office
Clipboard from VBA - but you do not have it in any later versions.

In Office 2000 the Office Clipboard is presented as a CommandBar and so you
can programmatically manipulate the Controls, for example ...

CommandBars("Clipboard").Controls("Clear Clipboard").Execute

In Office XP and later, the Office Clipboard is presented as a Task Pane and
the 'back door' has been shut. AFAIK, VBA access to it post-2000 is
impossible - I don't think you can even do it with SendKeys (but don't quote
me on that).

 

[NickHK]

Due to various annoying "features" of Office clipboard, I disabled it long
ago.
http://support.microsoft.com/kb/207438

That way you only have to deal with the Windows clipboard. As it can only
have a single object in each format, copying another picture <in the same
format> will clear the previous one.

And as others have pointed out, AFAIK there is no VBA access to the Office
clipboard.
The DataObject only lets you work with text.

NickHK

 

[Jonathan West]

I found some code on the web to manipulate the Windows clipboard using
windows API calls.

Is there a way to manipulate the MS Office clipboard?

Here's the scenario. I've got some code that parses through a Word
document
and copies all the tables and images from the Word document into an Excel
Workbook. The tables all work fine, but the images are sometimes
duplicated.
Instead of getting image 1 and then image 2, I get two copies of image 1.

Even though I clear the clipboard (using the Windows API calls) before I
copy the figure, sometimes I still get the duplicate.

I ran my code with the Office clipboard command bar showing and it looks
like the Office clipboard doesn't get cleared at all. To show the Office
clipboard, I'm just choosing View-->Toolbars-->Clipboard.

Is there a way to clear the Office clipboard?

Thanks in advance for the help!

(Forgive the cross-post. I'm not sure where to post the question.)

There isn't a direct way to clear the set of Office clipboards in VBA, but
what you can to is execute the button on the Clipboard toolbar that achieves
this. A bit of experimentation shows that the button's ID code is 3634, so
the following line of code will do the trick

CommandBars.FindControl(ID:=3634).Execute


--
Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Tony Jollans]

I just went back and re-read the original post and the problem really should
have nothing to do with the Office Clipboard.

Loosely, what happens is this: when you do a Copy operation the Windows
Clipboard is cleared and whatever it is you are copying is placed in the
Windows Clipboard. It may be added in several different formats (it's not
really relavant now, but some of them may go onto the clipboard itself, some
of them may be pointers for the originating application to act on later
request). When you do a Paste, the Windows Clipboard is asked to provide
whatever is on it in the format you want and, providing that format is
available, it will provide it, but still maintain whatever it holds for you
to do further Pastes if you wish. In normal circumstances you should not
need to do an explicit emptying of the clipboard yourself.

Separate to all this there is, again (very) loosely, a Windows event fired
when something is added to the Windows Clipboard which allows Office to know
that it has happened and take a copy for the Office Clipboard. Entirely
separate from the Windows Clipboard, the Office Clipboard maintains its own
copies of up to 12 (Office 2000) or 24 (later versions) items. Office
provides some UI facilities for manipulating the copies it holds but they
are not directly available from code and no Paste operation, other than
explicitly from the Office Clipboard, will use them. If you do do an
explicit Paste from the Office Clipboard (via the UI) what actually happens
is that a (Windows) Copy operation is triggered to copy from the Office
Clipboard to the Windows Clipboard and a (Windows) Paste operation is then
triggered to paste from the Windows Clipboard to the specified destination.

I confess myself at a total loss to explain how something that has been
removed from the Windows Clipboard can later be Pasted; it can only happen
if it has be re-placed (i.e. re-copied) on to the Windows Clipboard again -
whether explicitly or implicitly as part of some other operation. So the
immediate questions must be: can you verify that your copies have worked?
can you verify that your clearing of the Windows clipboard has worked? and
what do you do between copying and pasting?

 

[NickHK]

Tony's is probably one of the most coherent you will find on the inner
workings of the Office clipboard.

There is the Windows app "Clipboard Viewer", on my Win2K at
C:\WINNT\system32\clipbrd.exe, that you can use see what is actually being
copied to the windows clipboard.
See if your .Copy's are what you expect.

NickHK

 

[Guest]

Thank you Karl, Tony, Nick and Jonathan.

I'll give your various suggestions a try at let you know the results.

 

[Guest]

Hi Tony,

I've tried clearing this Office clipboard using the code that Jonathat gave
me, but the problem persists.

If I step through my code, it always works perfectly. So debugging is next
to impossible.

Anyway, here's the answers to your questions:

"can you verify that your copies have worked?" I have verified it as best I
can. As I said, when I step through my code it always works perfectly. To
copy the image I use this code:
Selection.Paragraphs(1).Range.Copy

I've also tried it with:
Selection.Paragraphs(1).Range.Selection
Selection.Copy

Using the select first, then copy approach, I can see that the correct image
is always being selected.


Next questions: "Can you verify that your clearing of the Windows clipboard
has worked?" Yes, using the C:\winnt\system32\clipbrd.exe application that
one of the posters mentioned, I can see that the clipboard does get cleared.

And your last question: "What do you do between copying and pasting?" I
don't do anything other than switch to Excel, move to a particular range, and
then paste. Here's the exact code:
' Up to this point, I am moving the cursor one paragraph at a time
down the
' document until I find an image. When I've got to one I do this
ClearClipboard ' This is the Windows API call code that I found on
Chris Pearson's web site (at least I *think* that is his name)
CommandBars.FindControl(ID:=3634).Execute

' Get the figure itself
Selection.Paragraphs(1).Range.Copy

mobjExcelApp.ActiveCell.Offset(1, 0).Activate
mobjCurrentSheet.Paste

ClearClipboard


The code is running within MS Word, so mobjExcelApp and mobjCurrentSheet are
references to the Excel application and the current worksheet.

Thanks for the help with this (extremely bizarre) problem!

 

[Karl E. Peterson]

That way you only have to deal with the Windows clipboard. As it can only
have a single object in each format, copying another picture <in the same
format> will clear the previous one.

Afaik, the entire contents of the clipboard (all formats) are cleared whenever a
Copy operation occurs. At least that's generally considered "proper" coding, in
nearly every circumstance. Certainly, if there's a need to do otherwise, it should
be transparent to the user. Have you ever seen items *added* to the clipboard
collection via a straigt-up Copy operation?

 

[Tony Jollans]

I've tried clearing this Office clipboard using the code that Jonathat

gave
me, but the problem persists.

As I tried to explain in one of my posts, I wouldn't expect it to make any
difference.

If I step through my code, it always works perfectly. So debugging is
next
to impossible.

In this case it is probably a timing (or perhaps a locking) problem. It is
worth trying to put one or more DoEvents statements in the code to allow the
system to catch up with itself. Precise logic behind this is often
impossible to explain but trial and error can sometimes lead to a
resolution.

Chris Pearson's web site (at least I *think* that is his name)

It's Chip Pearson but I don't think he'll mind

You said originally that the problem was with images yet you are copying a
paragraph range - does it contain images? And, if so, are they linked?

 

[NickHK]

Karl,
Yes I agree with you, but perhaps my wording was not that clear.
If an app puts a bmp and a emf on the Windows clipboard, then you copy from
an app that only supports bmp, you will lose the jpg from before and only
have the new bmp.
I was trying to emphasize the difference to the Office clipboard where you
can have more than one object of the same format at the same time.

NickHK

 

[Karl E. Peterson]

Yes I agree with you, but perhaps my wording was not that clear.

Yeah, could be I'm misunderstanding.

If an app puts a bmp and a emf on the Windows clipboard, then you copy from
an app that only supports bmp, you will lose the jpg from before and only
have the new bmp.
JPG?

I was trying to emphasize the difference to the Office clipboard where you
can have more than one object of the same format at the same time.

I'm not very familiar with this Office clipboard, so that could be the cause of
confusion. But I don't see any issue with multiple formats on the Windows
clipboard. It's exceedingly common, in fact.