Creating table from data in Clipboard

  • Thread starter Thread starter mario
  • Start date Start date
M

mario

I have following data copied from an application into clipboard memory:

Name/Semester |Grade
Mario-Fall01 | 3.5
Mario-Fall02 | 3.6
Mario-Fall03 | 2.3
Mary-Fall01 | 1.5
Mary-Fall02 |4.6
Mary-Fall03 | 3.3

I want the above data in clipboard memory to be converted into a table like
this:

Name | Semester | MaxGrade
Mario | Fall02 | 3.6
Mary | Fall02 | 4.6

Can some one help me with the necessary VB code or references to do the
above thanks.
 
Hi Mario,

For Office 2007, here is a good starting place... much of this applies to
the office 2003 clipboard as well.
http://office.microsoft.com/en-us/access/CH010411351033.aspx

If you want to get down and dirty with the windows system clipboard - it's
kind of gruesome but Terry Kreft's VBA is a good place to start if you want
to indulge in some hard core shell programming.

http://www.mvps.org/access/api/api0049.htm

If you use Terry's code, then using your example, you could approach it
something like this - note I am assuming that your clipboard content rows
are terminated by a CR and LF combination. (vbCrLf is the built-in constant
equivilent). You would need to substitute your table for the name MYTABLE in
the DAO openrecordset This is air code and completely depends on finding
that vbCrLf; you may have to adjust the row parsing routine because I might
be one char off here or there. Particularly where I try to determine whether
a final row is in in the s string that didn't have a vbCrLf termination. I
just pasted the code from above - I may have the final row segment chopped
wrong. The code is to the point where you need to do a little live testing
to debug it further.

Sub InsertClipboard()

Dim rs As DAO.Recordset

Dim s As String
Dim row As String

Dim myArray(100, 2) As Variant

Dim Count As Integer
Dim rowStart As Integer
Dim rowLen As Integer
Dim start As Integer
Dim i As Integer

Set rs = CurrentDb.OpenRecordset("MYTABLE")

'we know the first row of data
myArray(0, 0) = "Name"
myArray(0, 1) = "Semester"
myArray(0, 2) = "MaxGrade"

Count = 1
s = Trim(ClipBoard_GetText())
start = InStr(s, vbCrLf) + 1
rowStart = start

For i = start To Len(s)
If Mid(s, i, 2) = vbCrLf Then
rowLen = i - rowStart
row = Mid(s, rowStart + 1, rowLen)
'parse the text in row in to the myArray elements
myArray(Count, 0) = Trim(Left(row, InStr(row, "-") - 1))
myArray(Count, 1) = Trim(Mid(row, InStr(row, "-") + 1,
InStr(row, "|") - (InStr(row, "-") + 1)))
myArray(Count, 2) = Trim(Mid(row, (InStr(row, "|") + 1),
rowLen - (InStr(row, "|") + 1)))
Count = Count + 1
rowStart = i + 1
End If
Next i

'checking for a last row without vbCrLf
If (rowStart + 5) < i Then
myArray(Count, 0) = Trim(Left(row, InStr(row, "-") - 1))
myArray(Count, 1) = Trim(Mid(row, InStr(row, "-") + 1, InStr(row,
"|") - (InStr(row, "-") + 1)))
myArray(Count, 2) = Trim(Mid(row, (InStr(row, "|") + 1), rowLen -
(InStr(row, "|") + 1)))
Else
Count = Count - 1
End If


For i = 1 To Count
With rs
.AddNew
.Fields(0) = myArray(i, 0)
.Fields(1) = myArray(i, 1)
.Fields(2) = myArray(i, 2)
.Update
End With
Next i
rs.Close
Set rs = Nothing
End Sub

Hope this helps,
Gordon
 
Back
Top