Using Clipboard_SetData function to copy small table

Peter_W

New Member
Joined
Apr 2, 2018
Messages
31
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.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Read the table, record by record into a string.
Add CR & LF to each value.
Then copy string variable to clipboard. using that syntax?
 
Last edited:
Upvote 0
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
Code:
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
 
Upvote 0
Thank you sir for the code! I will try this now in my database, and will reply back. Best...
 
Upvote 0
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:
Code:
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
 
Last edited by a moderator:
Upvote 0
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
Rich (BB code):
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
Rich (BB code):
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.?
Rich (BB code):
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top