JackieMaw
asked on
VBA Clipboard Unstable
The following code (Excel 2003) is unstable. After numerous iterations it crashes with the following errors:
DataObject:PutInClipboard OpenClipboard Failed
Paste Method of Worksheet class failed
Out of Memory
This differs per machine, all running the same build of Excel, and is quite temperamental.
Sometimes it will run 1000 iterations, sometimes only 20.
Any ideas how i can make it more stable?
Thanks, Jackie
Sub ClipPaste(PasteText As String, PasteRange As Range)
Dim doClip As DataObject
Dim Wbk As Workbook
Dim WKS As Worksheet
Dim Rng As Range
Dim WbkCur As Workbook
Dim WksCur As Worksheet
Dim RngCur As Range
Set WbkCur = Application.ActiveWorkbook
Set WksCur = Application.ActiveSheet
Set doClip = New DataObject
doClip.SetText PasteText
doClip.PutInClipboard
Set doClip = Nothing
Set Wbk = Workbooks(PasteRange.Paren
Set WKS = Wbk.Worksheets(PasteRange.
Set Rng = WKS.Cells(PasteRange.Row, PasteRange.Column)
Wbk.Activate
WKS.Select
Rng.Select
ActiveSheet.Paste
WbkCur.Activate
WksCur.Select
Application.CutCopyMode = False
End Sub
DataObject:PutInClipboard OpenClipboard Failed
Paste Method of Worksheet class failed
Out of Memory
This differs per machine, all running the same build of Excel, and is quite temperamental.
Sometimes it will run 1000 iterations, sometimes only 20.
Any ideas how i can make it more stable?
Thanks, Jackie
Sub ClipPaste(PasteText As String, PasteRange As Range)
Dim doClip As DataObject
Dim Wbk As Workbook
Dim WKS As Worksheet
Dim Rng As Range
Dim WbkCur As Workbook
Dim WksCur As Worksheet
Dim RngCur As Range
Set WbkCur = Application.ActiveWorkbook
Set WksCur = Application.ActiveSheet
Set doClip = New DataObject
doClip.SetText PasteText
doClip.PutInClipboard
Set doClip = Nothing
Set Wbk = Workbooks(PasteRange.Paren
Set WKS = Wbk.Worksheets(PasteRange.
Set Rng = WKS.Cells(PasteRange.Row, PasteRange.Column)
Wbk.Activate
WKS.Select
Rng.Select
ActiveSheet.Paste
WbkCur.Activate
WksCur.Select
Application.CutCopyMode = False
End Sub
The above code produces the same result as your code. If you posted a demonstration of the problem versus the actual code causing the problem then please post your real code.
Kevin
Kevin
Hi,
What is the point of this routine? As far as I can see from a quick look, it just puts some text into a cell and you don't need the clipboard for that. Am I missing something?
Regards,
Rory
PS It looks to me like equivalent code would be:
Sub ClipPaste(PasteText As String, PasteRange As Range)
PasteRange.Value = PasteText
End Sub
What is the point of this routine? As far as I can see from a quick look, it just puts some text into a cell and you don't need the clipboard for that. Am I missing something?
Regards,
Rory
PS It looks to me like equivalent code would be:
Sub ClipPaste(PasteText As String, PasteRange As Range)
PasteRange.Value = PasteText
End Sub
Guess I should have refreshed! :)
ASKER
Hi Everybody
Thanks for the response - unfortunately that approach will not work.
The PasteString that I am pasting contains vbTab and vbCrLf characters, which when using the clipboard, will paste the string over multiple rows and columns. So it's pasting an entire range of data, but this data is NOT coming from cells, but from the string which is built up in memory.
Can I attach a file that demo's what i am doing? <i have not used experts exchange before, how do i do this?>
Thanks, Jackie
Thanks for the response - unfortunately that approach will not work.
The PasteString that I am pasting contains vbTab and vbCrLf characters, which when using the clipboard, will paste the string over multiple rows and columns. So it's pasting an entire range of data, but this data is NOT coming from cells, but from the string which is built up in memory.
Can I attach a file that demo's what i am doing? <i have not used experts exchange before, how do i do this?>
Thanks, Jackie
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Jackie,
You can log into www.ee-stuff.com (using the same ID and password as you use here). You will need to zip the file and you will need the URL for this question from the address bar of your browser (if the URL ends in #nnnnnnnnnn, strip that bit off so it ends with .html) Once uploaded you will be given two links to your file - copy the address of the shortcut and post it here and we will take a look.
Rory
PS If you have any problems with that, you can email it to me and I will upload it for you. My address is in my profile.
You can log into www.ee-stuff.com (using the same ID and password as you use here). You will need to zip the file and you will need the URL for this question from the address bar of your browser (if the URL ends in #nnnnnnnnnn, strip that bit off so it ends with .html) Once uploaded you will be given two links to your file - copy the address of the shortcut and post it here and we will take a look.
Rory
PS If you have any problems with that, you can email it to me and I will upload it for you. My address is in my profile.
ASKER
thanks zorvak, that looks great! I have implemented it and it seems to run faster than using the clipboard
I will test it first thing tomoro morning on my client's "unstable" machine, but I am sure that this will solve my problem :-)
I will test it first thing tomoro morning on my client's "unstable" machine, but I am sure that this will solve my problem :-)
ASKER
BRILLIANT!!! It's wonderfully stable and faster, thanks a LOT!
Sub ClipPaste(PasteText As String, PasteRange As Range)
PasteRange.Cells(1, 1) = PasteText
End Sub
Kevin