• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Clipboard tab delimited string to Column

A

arishy

Member
Code: 
Sub GetClipBoardText()
   Dim DataObj As MSForms.DataObject

   'Dim r As Range  'this is just a trial, it did not work
   'Set r = Range("b1:b???") ' unkown number of elements

   Set DataObj = New MSForms.DataObject

   On Error GoTo InvalidData

   '~~> Get data from the clipboard.
   DataObj.GetFromClipboard

   '~~> Get clipboard contents
   MyString = DataObj.GetText(1)
   MsgBox MyString   ' see attached file
      Exit Sub
InvalidData:
   If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub

The message box displays the image attached
Then ...It is Gurus time.....How I get these values to a column in excel sheet like this:
column A
2500
3521
etc

The number of elements unkown
 

Attachments

  • IRtiming.jpg
    IRtiming.jpg
    20.1 KB · Views: 1
If you have an idea of what's in the clipboard (perhaps it comes from a browser Ctrl+C operation) you may not need all that code, I recorded this and it worked:
Code: 
    Range("F2").Select
    ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False
Otherwise you could split mystring with Split and a delimiter, which puts it into an array, you could then further split the members with another Split operation. But it is heavily dependent on where the data comes from, how it might be delimited etc. etc. Just too many unknowns to give you a definitive answer.
 
To start with your first remark, it did not only work, but seperated each element into a seperate column, and by transposing it, problem solved. I love simple solutions , it is "hard" to come by.

As for your second approach, this will give me the driving seat, and I love to do it this way but I need some hand holding.

I know EXACTLY what is in the clipboard; a bunch of numbers beween 100 and 5000, but I do not know the number of elements; they vary from 40 to 150

If I use split, how to identify the tab as the seperatotor ?
And once I have the array , how to move it to a certain column ??

My gut feeling the moving is not for the faint of hearts !!!
 
To determine what the delimiter(s) are, in your code, after:
MsgBox myString
you could have:
Code: 
Stop
For f = 1 To Len(mystring)
  Debug.Print Asc(Mid(mystring, f, 1)), Mid(mystring, f, 1)
Next f
(Stop so that you can continue stepping through the loop with F8)
When I copied a portion of this very web page to clipboard I got this:
99 c
111 o
108 l
117 u
109 m
110 n
32
65 A
13

10

50 2
53 5

where you can see that I get 13 followed by 10, which from a page such as http://www.theasciicode.com.ar/ascii-control-characters/line-feed-ascii-code-10.html you can see they're carriage return and linefeed characters.

So a line such as:
x = Split(myString, vbCrLf)
would create an array of individual rows, you'll have to check for yourself what splits the values horizontally, then you might:
Code: 
For i = LBound(x) To UBound(x)
'Z = Split(x(i), "????")
'or if you're lucky:
Z = Split(x(i), vbTab)
'this is where you could assign values to a 2 dimensional array (size determined by ubounds and lbounds of x and z).
Next i
Once you've got an array, putting it on the sheet is something like:
Code: 
Range("C3").Resize(UBound(x), UBound(Z)).Value = myNewArray 'or some such.
 
Last edited:
I know what seperate the numbers, it is tab (value 9).
So is it "vbTab" the seperator in the split function ??
 
If I do this:
MyString = "111 222 333" ' using tab as seperator
x =Split(MyString,vbTab)

I created an error and the error routine kicks in with message "Date on clip board is not text....." the one I made in the error routine
 
arishy said:
Code: 
 x = Split(MyString, vbTab)

Did not work x is Empty, I must define it as an array first right ?
Click to expand...
No need to define as an array first. If you do vtab split first you may not get an array you like as it will include row separators. You should split into columns first and to do that you should determine for yourself what is at the end of each row of data.

If you are copying something from the web, tell me where to find it and I'll try myself.
 
arishy said:
If I do this:
MyString = "111 222 333" ' using tab as seperator
x =Split(MyString,vbTab)

I created an error and the error routine kicks in with message "Date on clip board is not text....." the one I made in the error routine
Click to expand...
vbTab is chr(9), ascii code 9; is this the result when you did the
Debug.Print Asc(Mid(mystring, f, 1)), Mid(mystring, f, 1)
thing?

Find out what the delimiters are/is and use the likes of (if it's 11):
Split(MyString,chr(11))
 
I used the loop routine you gave me, and the debug printed 9 to confirm that it is actually a tab seperator.
As for the source of data , it is NOT from the web. It is instrument reading copied to the clipboard. That is why I said there is no guessing what the data will look like. I know the data and I displayed it with the msgbox

If you just create a string with say 3 elements of data seperated with tab as I did and try to use the split function it will not work. Let us investigate this VBA problem first
 
this produces a 3 member array in x:
Code: 
Sub blah()
mystring = "111" & Chr(9) & "222" & Chr(9) & "333"
x = Split(mystring, vbTab)
End Sub
 
very intresting ....now this
Code: 
Sub blah()
MyString = "2500    3542    1708    438"
  x = Split(MyString, vbTab)
  MsgBox x(0)
End Sub

I get one string with all the data
Now if I put x(1) I get error type mismatch ( it is actually OUT OF RANGE )
 
So vbTab is not the separator..

More completely:
Code: 
Sub blah()
mystring = "111" & Chr(9) & "222" & Chr(9) & "333"
mystring = mystring & Chr(13) & Chr(10) & "444" & Chr(9) & "555" & Chr(9) & "666"
mystring = mystring & Chr(13) & Chr(10) & "777" & Chr(9) & "888" & Chr(9) & "999"
mystring = mystring & Chr(13) & Chr(10) & "aaa" & Chr(9) & "bbb" & Chr(9) & "ccc"
MsgBox mystring

Dim myResults(0 To 3, 0 To 2)' sure, I knew how big it needed to be!
x = Split(mystring, vbCrLf)
For i = LBound(x) To UBound(x)
  Z = Split(x(i), vbTab)
  For j = LBound(Z) To UBound(Z)
    myResults(i, j) = Z(j)
  Next j
Next i
Range("c3").Resize(4, 3).Value = myResults
End Sub
Can you upload a text file with just myString in?

It will help in the vbe if you have the Locals pane visible. Alt+V then s. Or View drop down menu, then Locals window.
 
I used notepad++ so I can SEE the tabs.
First I pasted the clipboard and the tab shown very clrealy.
Then I opened the file I just sent you. AGAIN Tab was EXACTLY the SAME in BOTH FILES.

SO, VBA DOES NOT SEE THE TAB.
The mistry continue.... If you use the loop you wrote , VBA display 9 when it hits a tab...........Conclusion the split function has a BUG
 
The file you sent does indeed have tabs, but if you paste this directly in the code or you type in the vbe using the tab key, those get converted to spaces.

I opened your file in notepad, copied everything in it to the clipboard then ran this:
Code: 
Sub ffff()
Dim DataObj As MSForms.DataObject
Set DataObj = New MSForms.DataObject
On Error GoTo InvalidData
'~~> Get data from the clipboard.
DataObj.GetFromClipboard
'~~> Get clipboard contents
mystring = DataObj.GetText(1)
MsgBox mystring  ' see attached file

Stop
Z = Split(mystring, vbTab)
Range("C10").Resize(, UBound(Z)).Value = Z
Exit Sub
InvalidData:
If Err <> 0 Then MsgBox "Data on clipboard is not text or is empty"
End Sub
First thing I noticed is that there are no row separators in the file, it's just one long tab delimited row. The msgbox wordwraps, so it looks like a grid, but it isn't.

The line just before Exit Sub wrote some 100 values to the active sheet in the range c10:cy10
 
or if you wanted to write to a grid you could:
Code: 
Z = Split(mystring, vbTab)
For i = LBound(Z) To UBound(Z)
  Range("C10:G31").Cells(i + 1) = Z(i) 'make sure the range is bigger than your results.
Next i
 
Good show...You did it.
It has been a pleasure working with you on this project of mine.
Your simple approach and perseverance led to success. Now I can persue the rest of this project having automated this part.

If you are curious about the project I will be more than happy to fill you in.
Best regards
 
One thing to be aware of, your file contained several values but happened to finished with a tab character; is this always the case? If not, and it happened to finish with a value, that final value might not be transferred to the sheet.
The line:
Range("C10").Resize(, UBound(Z)).Value = Z
would miss the last value. It should be changed to:
Range("C10").Resize(, UBound(Z)+1).Value = Z
and since you said you wanted it in a column it should really be:
Range("C10").Resize(UBound(Z) + 1).Value = Application.Transpose(Z)
That way you'll never lose the last value, value present or not.
 
Your last point is critical and I will spend sometime to cleanup and report back.
As for the project, these numbers are timing data from your IR Remote Control.
Yes, we all have several of these laying around and I want to cosolidate all into one , idealy my smart phone. If you wonder how these cheap IR RC do not interfer with each other ( switching on the wrong device etc) , the secret lies in these timing data. We are talking micro seconds.
Luckly, the hardware involved is very cheap, and I will not bore you (unless you ask); but the software is rather interesting and excel is used to really make sense of these timings.
In a nutshell if you can record these timings for each/device and the buttons that you most likely use; then playthem back; you do not need all these RC(s).

Since you mentioned <little curious>, I will stop here waiting for your response.
 
You must log in or register to reply here.
Back
Top