Manipulating Strings

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PPL - 26 Aug 2006 15:19 GMT

I am trying to take a long string (that is split up with 2 tab characters
and contained on the clipboad) and place it
into 3 variables.

From http://www.word.mvps.org/FAQs/MacrosVBA/ManipulateClipboard.htm
I have used the following code to place data from the clipboard into a
variable:

Dim MyData As DataObject
Dim strClip As String
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText

How would I manipulate strClip to place its contents into 3 variables, as
defined by the tab separation?

Any help would be appreciated.

TIA

Reply to this Message

Doug Robbins - Word MVP - 26 Aug 2006 15:50 GMT

Use the Split() function

Dim MyData As DataObject
Dim strClip As String
Dim strSplit As Variant
Dim var1 As String, var2 As String, var3 As String
Set MyData = New DataObject
MyData.GetFromClipboard
strClip = MyData.GetText
strSplit = Split(strClip, vbTab)
var1 = strSplit(0)
var2 = strSplit(1)
var3 = strSplit(2)
MsgBox var1
MsgBox var2
MsgBox var3

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

>I am trying to take a long string (that is split up with 2 tab characters
>and contained on the clipboad) and place it
[quoted text clipped - 16 lines]
>
> TIA

Reply to this Message

Jean-Guy Marcil - 26 Aug 2006 16:05 GMT

PPL was telling us:
PPL nous racontait que :

> I am trying to take a long string (that is split up with 2 tab
> characters and contained on the clipboad) and place it
[quoted text clipped - 12 lines]
> How would I manipulate strClip to place its contents into 3
> variables, as defined by the tab separation?

Something like this would work. There probably is a faster way..

'_______________________________________
Dim myString As String
Dim myVariant As Variant
Dim str1 As String
Dim str2 As String
Dim str3 As String

myString = "1111" & vbTab & "2222" & vbTab & "3333"

myVariant = Split(myString, vbTab)
str1 = myVariant(0)
str2 = myVariant(1)
str3 = myVariant(2)
'_______________________________________

Of course, this code relies on the fact that you are certain that the
initial string does contain 2 tabs. If it does not, it will fail.
You may want to add some error checking, something like:

'_______________________________________
Dim myString As String
Dim myVariant As Variant
Dim str1 As String
Dim str2 As String
Dim str3 As String

myString = "1111" & vbTab & "2222" & vbTab & "3333"

myVariant = Split(myString, vbTab)

If UBound(myVariant) = 2 Then
str1 = myVariant(0)
str2 = myVariant(1)
str3 = myVariant(2)
Else
MsgBox "The string does not contain two tabs."
End If
'_______________________________________

Or you may want to check the contents of the string before splitting it,
even better:

'_______________________________________
Dim myString As String
Dim myVariant As Variant
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim i As Long
Dim lngTabCount As Long

i = 0
lngTabCount = 0

myString = "1111" & vbTab & "2222" & vbTab & "3333"

Do
i = InStr(i + 1, myString, vbTab)
If i = 0 Then Exit Do
lngTabCount = lngTabCount + 1
Loop While i <= Len(myString)

If lngTabCount = 2 Then
myVariant = Split(myString, vbTab)
str1 = myVariant(0)
str2 = myVariant(1)
str3 = myVariant(2)
Else
MsgBox "The string does not contain two tabs."
End If
'_______________________________________

Again, there probably is a simple way of doing all that error checking, but
in the mean time, this works!

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message

PPL - 26 Aug 2006 17:31 GMT

Thanks Doug & Jean-Guy for your replies.
Very helpful.
I wonder if the Split function works across all Office apps. I hope to use
it to port text from a Word doc to a Visio drawing.
I'll giver it a try on Monday.

Thanks again
PPL

>I am trying to take a long string (that is split up with 2 tab characters
>and contained on the clipboad) and place it
[quoted text clipped - 16 lines]
>
> TIA

Reply to this Message

Jean-Guy Marcil - 26 Aug 2006 17:37 GMT

PPL was telling us:
PPL nous racontait que :

> Thanks Doug & Jean-Guy for your replies.
> Very helpful.
> I wonder if the Split function works across all Office apps. I hope
> to use it to port text from a Word doc to a Visio drawing.
> I'll giver it a try on Monday.

If I remember correctly, all the VB functions like Replace, Split, etc. were
only available to Office 2000 VBA and later versions.

Signature

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Reply to this Message