Clipboard usage (1 Viewer)

[gblack]

I can copy (Ctrl+C) a list of data from wherever and post that into a string variable strVal. When I run MsgBox(strVal) It shows me the list I just copied... as a list.

What I'd like to do is append the data from this string variable (i.e. strVal) directly into a table as a list (i.e. each value in the list as a seperate record). I don't know how to parse the string variable as a list... into a table. Does anyone know how to do this?

I've spent hours searching on this the best I could find was this function: ArrayToClipboardString

But I don't know if it would help or how to use it if it does... If someone knows how I might use this function to solve my problem I am please elaborate.

Or... is there some way of posting my clipboard data into a different variable type and then using that variable to append my copied list directly into a table?

Thanks,
Gary

 

[TimW]

Hi Gary
My initial thoughts
If you cut and paste to a table:

• Do you know what limits each field
• do you know when a new record is needed?
• Do the fields in the destination table need checking for type?

You could use VBA to parse the string based on predefined limiters
I think it could be done but other methods would be less work IMHO

Some elaboration on what you are trying to achieve may show more light on the matter.

HTH

T

 

[gblack]

Hmm... Yes I see, now, I wasn't very specific... To answer your questions:

There would be one and only one field in the clipboard and also in the table I would paste the clipboard data into.

The size of each data element would probably never be much longer than 13 (or so) characters, but it would varry. It could range from say 1 to 20 characters...

There would be no limits on the number of records I might be pasting. Essentially I would be doing something like dragging values from a column in excel and hitting Ctrl+C

For simplicity sake, the data-type would always be a text (i.e. a string) value.

I don't know how to parse through a string when there's no delimiter (like a comma or a space etc...). I suppose there's some sort of break/return that makes the data stack on to of each other in the clipboard, but I don't know how to parse through something like that. If I did, I could loop through the data and push it into the table using a recordset.

Essentially each list would look like something like this (see list of 5 values below) except I would never know how many records/data elements there would be in the list... there might be 5 (like shown below), 25, or even 50, but it could always be a single array, like so:

0000LLCYB3640
0000LLCYB5364
0000LLCYB5366
0000LLCYB5370
0000LLCYB5371

What I am trying to do is to be able to copy a list (like one above) into my clipboard, then (using VBA) paste it directly into a specified table in MS Access.

The table I would be pasting into would house only a single field and be used specifically to hold only the data from my clipboard. Essentially I'd delelte the data from the table before each append of new data from my clipboard. Obviously, I want to do other things with that data afterwards... but this is the issue I am having trouble with at the moment. Seems like this should be pretty simple... but I can't figure out how to do this.


Thanks so much for considering my issue,
Gary

 

[gblack]

Well,
I figured out what I was trying to do, this code takes a list you've copied onto your clipboard an pastes it into a table. Basically it loops through the values in your clipboard and uses a recordset to append to your table. My tables name was [VALUES] and the text field name was [INPUT_FIELD].

Public Sub ClipboardToTable()

Dim DataObj As New MSForms.DataObject
Dim strString As String
Dim arrString() As String
Dim rs As DAO.Recordset 
Dim i As Integer
Dim Db As DAO.Database

Set Db = CurrentDb
Set rs = Db.OpenRecordset("VALUES")
 
' Retrieve clipboard contents into data object
 DataObj.GetFromClipboard
 
' Clipboard to string variable
strString = DataObj.GetText

' Convert string variable to array
arrString = Split(strString, vbNewLine)

' Post Array to Table using DAO recordset
For i = 0 To UBound(arrString) - 1

    'Update Table with data from clipboard
    rs.AddNew
    rs!INPUT_FIELD = arrString(i)
    rs.Update

Next i
 
Set rs = Nothing
 
End Sub

You'll need the Microsoft Forms 2.0 reference. I didn't have it as an option since I am using 2007... so i had to find the FM20.DLL file then add it like so:

References.AddFromFile "C:\Windows\SysWOW64\FM20.DLL"

Also... attached is the tool I made from this. It takes a list you copy and converts it into an IN() function to post in the criteria of MS Access/Oracle/SQL Server... whatever...

HTHs
-Gary

 

[TimW]

Hi Gary,
Very good
I will save a link to this for later!

T