Solved: Check the clipboard for Data before paste?

[JeffT]

Hi

I wonder if there is anyway to check if the clipboard contains data before attempting to Paste.

The data will be copied from a .txt file then pasted into Excel to do a lot of modifications. I'm using a manual copy from the text file then using code to paste so I can carry out some modifications before the data is seen. If the Clipboard contains data then all works OK, if not I get an error "Paste method of worksheet class failed".

I could use an On Error statement to catch the error but by then I've created a new sheet and would have to delete it again. All is possible but I'd rather know if there is nothing to paste before the Sub gets too far.

Also is it possible to determine how many lines of text there are in the clipboard data as in some instances it may be that there will be more than 65536 so the spreadsheet would run out of room (xl2003) when the data is pasted. I was going to try it out to see if an error came up or if the paste was truncated then decide how to solve it, but it'd be good if I could use the same check to do both.

Hope you can help

Regards

Jeff T

[GTO]

...The data will be copied from a .txt file then pasted into Excel to do a lot of modifications. I'm using a manual copy from the text file then using code to paste so I can carry out some modifications before the data is seen.

Not to divert from your question, and maybe I'm missing something, but it would seem to me that you need to paste the data somewhere (seen or not) to modify it. Could you zip a sample workbook and textfile that shows your current code and explain what mods need done?

Mark

[mdmackillop]

Sub CheckClipboard()
'Requires reference to Microsoft Forms 2.0 Object Library
Dim MyData
Dim a As String
Set MyData = New DataObject
MyData.GetFromClipboard
a = MyData.GetText(1)

MsgBox a

MsgBox "Records = " & UBound(Split(a, Chr(13) & Chr(10)))

End Sub

[JeffT]

Thanks MD

There was an error if there was nothing in the Clipboard, but looking in help I worked out the code below using GetFormat(1) instead of GetText(1).

Sub CheckClipboard()
'Requires reference to Microsoft Forms 2.0 Object Library

Dim MyData, Number As Integer
Dim a As String

Set MyData = New DataObject
MyData.GetFromClipboard

a = MyData.GetText(1)
Number = UBound(Split(a, Chr(13) & Chr(10)))

If MyData.GetFormat(1) = False Then 'No Data gives False

MsgBox " No Data has been copied"
ElseIf Number + 1 > 65536 Then

MsgBox "Too much Data for Excel 2003"
End If
End Sub


Seems to work so thanks again for your help. I'll change it slightly to give an option to continue if someone is using a later version of xl.

Regards

JeffT