i have a macro that opens numerous excel spreadsheets in an array an
pastes data into sheets in a master document. after each paste th
source spreadsheet is closed, then the loop begins again by opening th
next spreadsheet in the array.

the problem i have is the message stating that "there is a large amoun
of data still on the clipboard etc.." when each source spreadsheet i
closed. This requires me to manually select "NO" at the end of ever
loop, to inform Excel that i do not need this data to be stored fo
future use (as i have already pasted it).

is there a way of disabling this, or coding something in the macro t
automatically select "NO" when prompted by the clipboard message.

any ideas welcomed

--
presuming e
-----------------------------------------------------------------------
presuming ed's Profile: http://www.excelforum.com/member.php...fo&userid=3152
View this thread: http://www.excelforum.com/showthread.php?threadid=51219

Hi,
take this:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal
lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) As
Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long

Public Sub CleanClipBoard()
OpenClipboard FindWindow("xlMain", vbNullString)
EmptyClipboard
CloseClipboard
end sub

Jens



"presuming ed" wrote:


i have a macro that opens numerous excel spreadsheets in an array and
pastes data into sheets in a master document. after each paste the
source spreadsheet is closed, then the loop begins again by opening the
next spreadsheet in the array.

the problem i have is the message stating that "there is a large amount
of data still on the clipboard etc.." when each source spreadsheet is
closed. This requires me to manually select "NO" at the end of every
loop, to inform Excel that i do not need this data to be stored for
future use (as i have already pasted it).

is there a way of disabling this, or coding something in the macro to
automatically select "NO" when prompted by the clipboard message.

any ideas welcomed!


--
presuming ed
------------------------------------------------------------------------
presuming ed's Profile: http://www.excelforum.com/member.php...o&userid=31526
View this thread: http://www.excelforum.com/showthread...hreadid=512190

Kikde,
why use api's when excel can do it itself?

application.cutcopymode = false will clear the clipboard.

note to OP:

you can avoid leaving data on the clipboard by specifying the
destination as an argument to the copy or cut function like

range("source").cut range("destination")


else after every paste or when routine finishes
or before you close the (copied) workbook use

application.cutcopymode = false



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


kikde wrote :

Hi,
take this:

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA"
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As
Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function CloseClipboard Lib "user32" () As Long

Public Sub CleanClipBoard()
OpenClipboard FindWindow("xlMain", vbNullString)
EmptyClipboard
CloseClipboard
end sub

Jens



"presuming ed" wrote:


i have a macro that opens numerous excel spreadsheets in an array
and pastes data into sheets in a master document. after each paste
the source spreadsheet is closed, then the loop begins again by
opening the next spreadsheet in the array.

the problem i have is the message stating that "there is a large
amount of data still on the clipboard etc.." when each source
spreadsheet is closed. This requires me to manually select "NO" at
the end of every loop, to inform Excel that i do not need this data
to be stored for future use (as i have already pasted it).

is there a way of disabling this, or coding something in the macro
to automatically select "NO" when prompted by the clipboard message.

any ideas welcomed!


--
presuming ed
--------------------------------------------------------------------
---- presuming ed's Profile:
http://www.excelforum.com/member.php...o&userid=31526
View this thread:
http://www.excelforum.com/showthread...hreadid=512190