Parsing string on clipboard

A

Al Grant

Hi folks,

Sorta a followon from my "on focus clipboard" message.

I want to parse the data on the clipboard into textbox of a userform.

The data looks like:

Pers ID: 20456035839 Master RNS: 5476121
(Real) SMITH, MICHAEL JOHN

(Contact Address) 8 FORD ROAD, WHITTAMS, AUCKLAND CITY 1750



I had a go myself and ended up hardcoding everyting - it was pretty ugly.



lines like

RNS = Mid(clipbd_string, InStr(clipbd_string, "Master RNS: ") + Len("Master
RNS: "), 8) 'RNS max length is 8

Surname_name = Mid(clipbd_string, 52, (InStr(clipbd_string, ",") - 52))

Anyone have any better ideas?



-Al
 
P

Peter Hewett

Hi Al Grant

If you're using Word 2000 or later there are a few useful functions you can use: Replace,
Split, Join. As long as your input data is consistent you can probably get away with
simple parsing rather than resorting to much more complex token matching parsing.

You can do something simple like this:

Public Sub ParseClipboarData()
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim strId As String
Dim strRNS As String
Dim strName As String
Dim strAddress As String
Dim strWork() As String

str1 = "Pers ID: 20456035839 Master RNS: 5476121"
str2 = "(Real) SMITH, MICHAEL JOHN"
str3 = "(Contact Address) 8 FORD ROAD, WHITTAMS, AUCKLAND CITY 1750"

' Parse out the first line
strWork() = Split(str1, " ")
strId = Trim$(strWork(2))
strRNS = Trim$(strWork(5))

' Parse out the second line
strWork() = Split(str2, ")")
strName = Trim$(strWork(1))

' Reverse the name and remove the comma
strWork() = Split(strName, ",")
strName = Trim$(strWork(1)) & " " & strWork(0)

' Parse out the third line
strWork() = Split(str3, ")")
strAddress = Trim$(strWork(1))
End Sub

It's mostly declarations but is straightforward.

HTH + Cheers - Peter
 
H

Helmut Weber

Hi Al,
this is the part, which Peter took for granted:
---
Dim oDat As DataObject
Dim sAll As String
Set oDat = New DataObject
oDat.GetFromClipboard
On Error GoTo TheEnd
sAll = oDat.GetText
MsgBox sAll
Exit Sub
TheEnd:
MsgBox "No text in Clipboard"
 
P

Peter Hewett

Hi Helmut Weber

No I didn't - you'd already posted the code in the previous thread! And Al's definitely
smart enough to do the integration.

Cheers - Peter


Hi Al,
this is the part, which Peter took for granted:
---
Dim oDat As DataObject
Dim sAll As String
Set oDat = New DataObject
oDat.GetFromClipboard
On Error GoTo TheEnd
sAll = oDat.GetText
MsgBox sAll
Exit Sub
TheEnd:
MsgBox "No text in Clipboard"
---
Greetings from Bavaria, Germany
Helmut Weber
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
Click to expand...

HTH + Cheers - Peter
 
A

Al Grant

Yep, I guess I have to seperate out sALL into str1, str2, str3.

Will have a crack at it later. Thanks guys.

-Al
 
A

Al Grant

Hmmm - I cant find dataobject as a type of variable, and I get a compile
error - user-defined type not defined???

-Al
 
J

Jay Freedman

Hi Al,

In the VBA editor, go to the Tools > References dialog and check the
box next to Microsoft Forms 2.0 Object Library. That will make the
DataObject available. You'll have to repeat this in each template
project where you want to use a DataObject.
 
A

Al Grant

Hmmm, I am getting closer with:

Public Sub ParseClipboarData()
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim strId As String
Dim strRNS As String
Dim strName As String
Dim strAddress As String
Dim strWork() As String

xstart = 1
str1 = Mid(sAll, xstart, InStr(xstart, sAll, Chr(13)))
MsgBox testval1
xstart = InStr(xstart, sAll, Chr(13)) + 2
str2 = Mid(sAll, xstart, InStr(xstart, sAll, Chr(13)) - xstart)
MsgBox testval2

but I still think their is a better way to increment xstart, rather than
line by line?

Cheers

-Al
 
P

Peter Hewett

Hi Al Grant

An earlier post in this thread uses Split so why not use that:

Dim astrLines() as String

astrLines() = Split(sAll, Chr$(13))

then replace str1 with astrLines(0) , str2 with astrLines(1), etc.

HTH + Cheers - Peter
 
A

Al Grant

After getting it working on my pc at home I found out we are on office 97 at
work!!!

No split command :-(

-Al
 
P

Peter Hewett

Hi Al Grant

You need to write you own function. I don't currently have a Word 97 version of Office
installed - so I don't know whether you can write a function like this:

Public Function Spliter(ByVal ToSplit As String, _
ByVal Delimiter As String) As String()
Const ARRAY_INCREMENT As Long = 10

Dim lngPosition As Long
Dim lngLastPosition As Long
Dim lngUsed As Long
Dim astrChunks() As String

' Basic validation
If LenB(ToSplit) = 0 Then Exit Function
If LenB(Delimiter) = 0 Then Exit Function

' Allocate initial array
ReDim astrChunks(0 To ARRAY_INCREMENT - 1)

' Find the first delimiter
lngPosition = InStr(1, ToSplit, Delimiter)
lngLastPosition = 1

' Chop up the input string using the delimiter and put each chunk into the array
Do While lngPosition > 0

' Make sure there is enough space in the array for the next element
MakeSpace astrChunks, lngUsed, ARRAY_INCREMENT

' Add string to array
astrChunks(lngUsed) = Mid$(ToSplit, lngLastPosition, _
lngPosition - lngLastPosition)

' Reset string index for next interation
lngLastPosition = lngPosition + 1

' Find next delimiter (if any)
lngPosition = InStr(lngLastPosition, ToSplit, Delimiter)

' Increment counter for next iteration
lngUsed = lngUsed + 1
Loop

' Add last (or only chunk to the array)
MakeSpace astrChunks, lngUsed, ARRAY_INCREMENT
astrChunks(lngUsed) = Mid$(ToSplit, lngLastPosition)

' Trim array to correct size and return it to the user
ReDim Preserve astrChunks(0 To lngUsed)
Spliter = astrChunks
End Function

Private Function MakeSpace(ByRef astrArray() As String, _
ByVal lngUsed As Long, _
ByVal lngIncremnt As Long)

' Make sure there is enough space in the array for the next element
If lngUsed > UBound(astrArray) Then

' Increase array size
ReDim Preserve astrArray(0 To UBound(astrArray) + lngIncremnt - 1)
End If
End Function


You can in Office 2000, because it supports functions that return arrays. Try the above
and if it's no go well hack it into a Sub that returns an array (as the MakeSpace Sub
does).

HTH + Cheers - Peter
 
Top