Clipboard
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 |
Clipboard
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 |
Clipboard
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 |
All times are GMT +1. The time now is 11:02 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com