Cannot Empty the Clipboard

Re: Cannot Empty the Clipboard

Hi Kate,

Assuming you are doing your copy/paste via code, in which case the following will empty the clipboard. Perhaps put this at the start or end of your macro.

' clear clipboard
Application.CutCopyMode = False

Re: Cannot Empty the Clipboard

Just for fun this coule be useful for testing if teh clipboard is really empty -
A little API goes a long way - as they say

juik[vba]Option Explicit

Public Declare Function CountClipboardFormats Lib "user32" () As Long

Function IsClipboardEmpty() As Boolean

IsClipboardEmpty = (CountClipboardFormats() = 0)

End Function[/vba]

Re: Cannot Empty the Clipboard

Hi,

Does the clipboard get filled when ChartObjects are deleted ?

i think the computer develops some kind of memory problem after i have tested my new project a couple of times. the project just hangs up. in this project i delete all old charts, 5 charts each in 6 workbooks, ie 30 charts and then replace those charts. the charts are first made in the file where the datd for the charts are present. then they are moved to one of the 6 actual destination workbooks where they are presented as embedded charts.

i intend to add more destination workbooks as and my project starts working perfectly. the total destination workbooks may then be about 15, in which case i will be dealing with 15x5=75 charts atleast. if required, i would want to increase 1 chart more per destination workbook.

thanks for any help.

geethaGupta

Re: Cannot Empty the Clipboard

bump - jiuk

Re: Cannot Empty the Clipboard

Sorry lost the post the so had to bump.
My API skills are very weak i just wrote this so it is tested and shold work i hope try and see if this does as You want, then You can add to Your work and call the empty clipbaod as required copy all these into a new standard module as teh API must be at the top, do not add the code(s) to existing work

jiuk
[vba]
Option Explicit

Public Declare Function CountClipboardFormats Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Function IsClipboardEmpty() As Boolean
IsClipboardEmpty = (CountClipboardFormats() = 0)
End Function
[/vba]
[vba]
Sub MyClipBoardtestA()
msgbox IsClipboardEmpty
End Sub
[/vba]
[vba]
Function JR_ForceEmpty()
OpenClipboard 0&
EmptyClipboard
End Function
[/vba]
[vba]
Sub JR_KillClipBoardt()
JR_ForceEmpty
End Sub
[/vba]

Re: Cannot Empty the Clipboard

Hi,

Not sure whether its a clipboard problem. I know when I copy and paste a large quanity of charts eventually XL gives up. Only way I have found around this is to save and close the workbook. Sometimes I even have to close xl.

Re: Cannot Empty the Clipboard

Andy
Duno? been on this one for a bit i get more VBE runs out of memory at the same time if i copy and paste in Excel it is fine - beat that? I am sure if You add my code into Your work for each copy / paste and then 'kill' it will help - remember Excel manages her own memory and Windows a lots a chuck, and when its gone = pop = it is gone. What do You think? Maybe test it?

If Your working manual them of cause this is of not help what-so-ever ho hum!

jiuk

Re: Cannot Empty the Clipboard

When I get a chance to test I will post back and let you know.

Re: Cannot Empty the Clipboard

Ok no problems - post back here and we can cary on - have a feeling if VBA should work or much better Excel will and can stop thou it is the way the Kernal work in Windows and how Excel speaks to Windows, Windows hands over power and steps in getting stroppy - i have never quite got this in my mind to why and how to fix it full time - the codes should be good maybe non-api will be better is slower not that that should matter

jiuk

Re: Cannot Empty the Clipboard

Hi Andy,

As suggested by you, I have rewritten my project so that only one destination workbook is opened at a time. After all the charts are done in it, it is saved and closed and then the next one is opened.

i am not really sure how much it has helped at present since the project is still in testing stage and there are several reruns but i think once the project is ready, this will be a very convenient feature of this project. The work will be done in an uncluttered fashion.

Also, if this is not a clipboard problem, as you have indicated, what could it be? is it simply Xl giving up due to being overworked?

thanks

geethagupta

Re: Cannot Empty the Clipboard

Hi Jack,

APIs go above my head. Got to sit and learn them before i can use your codes. :?

But knowing how to check if clipboard is empty is a very basic need while developing a project. So, where do i paste your codes? In a separate module in the existing project ? and which sub should be called first - MyClipBoardtestA ? if clipboard is empty, fine, else call JR_KillClipBoardt?

Can this be done anywhere in the project and any number of times?

thanks

geethagupta

Re: Cannot Empty the Clipboard

Hi buddy thanks for getting back – ok the idea was that the way I code does as I have found cause problems to a few members understanding exactly what I am trying to do so is a bit complex to use at times, not a problem.

My first bit of advice is simple forget learning API codes they are hard – I do not know that much only able to get them to work (some times) all the code I post are ‘fully’ tested and safe I assure You or by now I would be in very hot water long ago.

I use a lot of functions rather than loner procedures this is my style and could be classed as fragmentation which is poor programming but as You (I hope) will se the codes I have they are very usable with ease this is how.

copy the code below in one chuck into a separate standard module ‘away from your own codes’ This will be easier order, the API ‘must be first’ ie at the top of the module
[vba]
Option Explicit

Public Declare Function CountClipboardFormats Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hWnd As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long

Function IsClipboardEmpty() As Boolean
IsClipboardEmpty = (CountClipboardFormats() = 0)
End Function

Sub MyClipBoardtestA()
msgbox IsClipboardEmpty
End Sub

Function JR_ForceEmpty()
OpenClipboard 0&
EmptyClipboard
End Function

Sub JR_KillClipBoard()
JR_ForceEmpty
End Sub
[/vba]

Now use only JR_KillClipBoard this will ‘force’ Windows via Excel to dump the clipboard contents and be ‘in empty state’ which is what You want

So to use in You code can be as often as You like ie in a loop or after a paste has been executed, the code will run as needs be ie when called to do so

The MyClipBoardtestA as the text says is a test of the state and return as Boolean, so can be used to test before killing the clipboard, this way reduces the API codes usage to only be when needed, so the codes are advanced in that can be tested and added to Your requirements.

If You stuck copy over my code as said to a different module and paste up Your code I will add in the ‘calls’ for You and post back the procedure so You can try.

jiuk

Re: Cannot Empty the Clipboard

Hi Jack,

As instructed, i put your code in one module and my sample code in another.
just to show if i have used you functions correctly.

Sub CopyTenTimes()


Dim i As Integer


For i = 1 To 10


Range("B3").Select
Range(ActiveCell.Address & ":" & ActiveCell.End(xlDown).Address).Copy ActiveCell.End(xlDown).Offset(1, 0)
i = i + 1


Next i


MyClipBoardtestA
JR_KillClipBoard
MyClipBoardtestA


End Sub

MyClipBoardtestA evaluate true the second time too. why?

thanks
geethagupta

Re: Cannot Empty the Clipboard

True means empty

jiuk

Re: Cannot Empty the Clipboard

Think I've got a problem like this, I'm using a macro to open workbooks 1 at a time and then copy their contents+formatting into a new excel sheet, therefore creating 1 giant workbook. I keep on getting out of memory warnings and the paste operation just gives up. Tried this code out but doesn't seem to solve the problem. Using a ninja PC btw

Re: Cannot Empty the Clipboard

Bobbywhacker
Start a new thead / question and post the code that You have so we can test / look / try for You

jiuk

Re: Cannot Empty the Clipboard

A little late I know, and might be off topic

I found a few years ago that copying many charts overloaded Excel. I solved my problem by deselecting the font autosize checkbox for each chart area as soon as I created each chart.

The autosize feature significantly increases the storage required for charts it seems.