Using Clipboard_SetData function to copy small table

[Peter_W]

Hello,

Within Access 2010. I am familiar with using the the Windows API, and then creating the function, Clipboard_SetData, which will copy some data to the clipboard, when called. Testing this, the function works fine for a small string of data.

However, in my database, I would like to use the Clipboard_SetData function to copy a small, one-column table, called Orders, which is filled with about a dozen, 8-character strings.

So my question is, would anyone have a quick example of the code to do this? I have seen many examples of the function, but the code examples almost always look like like:

Clipboard_SetData ("A single, small string sample like this")

Again, how would I use this function to copy the contents of a table to the clipboard? The table Orders will vary over time slightly, but will always be a single column of strings.

Thank you in advance for any response here.

 

[welshgasman]

Read the table, record by record into a string.
Add CR & LF to each value.
Then copy string variable to clipboard. using that syntax?

 

[Peter_W]

Read the table, record by record into a string.
Add CR & LF to each value.
Then copy string variable to clipboard. using that syntax?

Thank you welshgasman! Let me try that solution.

 

[welshgasman]

I'll save you some work, as I was interested in the method.
This will do it for any table and field.
It will automatically put in the clipboard and return as a string. You can amend to suit. I've tested it with about 279 records of about 20 chars per record.

HTH

Function Table2String(pstrTable As String, pstrField As String)
Dim strSQL As String, strData As String
Dim db As DAO.Database, rst As DAO.Recordset
Dim fldData As Field
Dim objClip As New DataObject


strSQL = "SELECT " & pstrField & " FROM " & pstrTable
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
Set fldData = rst.Fields(0)
Do While Not rst.EOF
    strData = strData & fldData.Value & vbCrLf
    'Debug.Print strData
    rst.MoveNext
Loop
strData = Left(strData, Len(strData) - 1)
Table2String = strData
'Clipboard_SetData (strData)
objClip.SetText strData
objClip.PutInClipboard


Set rst = Nothing
Set db = Nothing
Set objClip = Nothing


End Function

 

[Peter_W]

Thank you sir for the code! I will try this now in my database, and will reply back. Best...

 

[Peter_W]

welshgasman, please excuse my ignorance, but how do I use this function? I assume I call it within a sub. My specific Access table is named Orders, and the field I need to make into a string is Ordernum. Thanks in advance for any help.

Here is what I have tried so far:

Function Table2String(Orders As String, OrderNum As String)
Dim strSQL As String, strData As String
Dim db As DAO.Database, rst As DAO.Recordset
Dim fldData As Field
Dim objClip As New DataObject

strSQL = "SELECT " & OrderNum & " FROM " & Orders
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL)
Set fldData = rest.Fields(0)
Do While Not rst.EOF
    strData = strData & fldData.Value & vbCrLf
    'Debug.Print strData
    rst.MoveNext
Loop
strData = Left(strData, Len(strData) - 1)
Table2String = strData
'Clipboard_SetData strData
objClip.SetTest strData
objClip.PutInClipboard
Set rst = Nothing
Set db = Nothing
Set objClip = Nothing
End Function
    
Sub TestClip2()
 
Call Table2String

End Sub

 

[welshgasman]

It is a function. A function generally returns something, else you use a Sub, well I do anyway.
You have two choices. Initially I wrote it to return a string that you would use in your function just as you asked, then I thought, why not put it in the clipboard anyway.?
I could not use your Clipboard_SetData as i did not have the code, so used the old fashioned method.

So basically you would call it like

StrDataIWantToCopy = Table2String("orders","ordernum")

then do what you want with that string.

However it has also been copied into the clipboard already for you, so you can just go to wherever and just paste.

If you are not interested in returning the string, but just want it in the clipboard, then you could use

call table2string("orders","ordernum")

If you do not want it the clipboard each time comment out as below, and then you will only get it via the first method.?

objClip.SetTest strData
objClip.PutInClipboard

You could even put a third parameter (boolean) in it to show whether it should also put it in the cllipboard or not and test for that?

However I made it to work for any table and any singular field.? More versatile that way. That is why I wrote it.
If you are going to hard code it for one table then no need for parameters, just return the value as Table2String.

HTH

 

[Peter_W]

Sir, thanks for all your help, but I'm still having trouble. I am trying to call the function from a sub: Call Table2String ("orders", "ordernum")

VBA stops the function at this line" Dim objClip As New DataObject. Says" Compile error: User-defined type not defined

Maybe I need to add something under VBA references? Also, just curious: is it "SELECT " and nt "SELECT *" (with *?)

Any further help is much appreciated, thanks.

 

[RoryA]

FWIW, if you were to use ADO rather than DAO, the ADO Recordset object has a GetString method that will save you looping through all the records and fields.

 

[Peter_W]

Welshgasman, I had a typo in the code. Also, I needed to add MSForms (FM20.dll) under references too. The code works like a champ now! THANK YOU! Made my day, to say the least. Thanks RoryA for the tip. Will have to try ADO some day, but usually I can get the DAO code like this to work. Thanks again!