Validating clipboard data

[ljo]

Hi,

Hoping someone can help with this issue. Basically, I have a prog that requires the user to copy some data then use the macro to paste into the main project workbook. I want to check that the copied data doesn't have too many columns. My code works except when the last columns in the copied range are blank. Then it doesn't count them and they are pasted in over top of columns I want preserved.

What I've done is load the clipboard into a string, split into an array with newline as the delimiter, then split into array with tab as the delimiter. The ubound of the array should then be the col count.

Any thoughts? Thanks!

Example code (data goes from A1 to E20 - if cols up to G are copied, I want to count 7 cols but this code returns 5):

Sub CountCopiedCols()
Dim DataObj As New MSForms.DataObject
Dim s As String
Dim clipArr() As String
Dim rowArr() As String
Dim i As Integer

Range("A1:G20").Copy

DataObj.GetFromClipboard
s = DataObj.GetText

clipArr = Split(s, Chr(10))

s = clipArr(0)
rowArr = Split(s, Chr(9))

MsgBox UBound(rowArr) + 1
End Sub

 

[ljo]

Anyone have any thoughts on this? Or can somebody suggest another way of counting columns in data on the clipboard? It seems to me that Excel loses some information .GetText.