Copy contents of listbox to clipboard

[jpl458]

I have a listbox (DisplayQrylb) and I want to be able to copy it's contents to the clipboard. And I tried this bit of code in onclick event:

  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", =DisplayQrylb

It works with "some text for clipboard" inplace of =DisplayQrylb

 

[cheekybuddha]

What is the RowSource of your query?

You will have to get the ListItems into a string and pass that to the .SetData method.

Depending on how you populate your listbox and how many listitems it contains, there are various methods to do that.

 

[jpl458]

The query doesn't have a row source, but the listbox does, and there is nothing in it.

 

[jpl458]

What are you expecting to get copied to the clipboard?

1. the ControlSource?
2. the RowSource?

I don't know the answer to that> The list box will contain date in n number of rows, where n is a small number op 10 or so, but usually less.

What are you expecting to get copied to the clipboard?

1. the ControlSource?
2. the RowSource?

 

[cheekybuddha]

Yes, sorry, I meant what is the RowSource of your listbox?

 

[jpl458]

The list that is visible in the listbox is the RowSource. If you look at the rowSource property, it will be the name of a table or a query. You would copy that table or query to the clipboard.

The ControlSource is the selected item and it is what will be saved in the record.

It's a query named QryPhoneCalls

Here is the SQL for that query;

SELECT Mastertbl3.calldate, Mastertbl3.starttime, Mastertbl3.Duration, Format([callingnumber],"(000) 000-0000") AS calnumber
FROM RoboMastertbl3
WHERE (((Mastertbl3.companyname1)=[Forms]![QueriesFrm]![SelectCallCocb])
ORDER BY Mastertbl3.calldate;

Criteria is gathered from a combobox.

It will generate, usually 3 to 5 rows

If you need more let me know.

Thanks

 

[cheekybuddha]

Here's one way to do it:

' ...
  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1
' ...
  strSQL = Me.DisplayQrylb.RowSource
  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdText)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy
' ...

 

[cheekybuddha]

It will generate, usually 3 to 5 rows

Oops! I missed this!

With this few rows, you may just as well loop over the rows and fill your string variable to pass to your ClipboardData.SetData() method

 

[jpl458]

Here's one way to do it:

' ...
  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1
' ...
  strSQL = Me.DisplayQrylb.RowSource
  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdText)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy
' ...

Thanks. I'll be trying it right now. Just loaded it up and got "Invalid argument" on

objCP.ParentWindow.ClipboardData.SetData "text", strCopy

.
I have no idea what caused it. Understand that I am still learning, but will this capture multiple rows in the listbox?

 

[cheekybuddha]

Set objCP = CreateObject("HtmlFile")
objCP.ParentWindow.ClipboardData.SetData "text", strCopy

Have you used this code before? I have never seen it, so am taking your word for it that it works as intended.

The code I offered uses an ADODB recordset (via the CurrentProject.Connection.Execute() method) and produces an output of all its records in a srting.

It uses a comma as a field delimiter and a vbNewLine as a record delimiter.

You can add a Debug.Print strCopy line before trying to set it to the clipboard to check you have the desired output in the Immediate Window (Ctrl+G)

 

[cheekybuddha]

It's a query named QryPhoneCalls

Here is the SQL for that query;

I really did a poor job of reading your reply in Post #8

You are using a named query - I thought it was just the SQL statement.

The code i suggested will need tweaking to work.

It might be as simple as just commenting out the following line:

' ...
'  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
' ...

But it may not work directly because of the form reference parameter - but then again, it might! So give it a try!

 

[jpl458]

Here's one way to do it:

' ...
  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1
' ...
  strSQL = Me.DisplayQrylb.RowSource
  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdText)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy
' ...

Sorry for taking so long, but I just ran this code and it had this error:

On this line of code:

[CODE]With CurrentProject.Connection.Execute(strSQL, adCmdText)[/CODE]

Windows 10 Office 365

I have no clue how to fix that.

Thanks for the help

 

[cheekybuddha]

Sorry for taking so long, but I just ran this code and it had this error:

Yes, in Post#15 I noted that it might need a small tweak (commenting out one line) but, as noted, it may still break because of the parameter in the saved query which may need to be evaluated first.

Also, you may need to change the CommandType parameter (I have added below some extra constant declarations)

However, try this and see where you get to from here:

' ...
  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1,
        adCmdTable As Integer = 2,
        adCmdStoredProc As Integer = 4,
        adCmdUnknown As Integer = 8
' ...
  strSQL = Me.DisplayQrylb.RowSource
'  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdUnknown)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy
' ...

 

[jpl458]

WHAT are you going to do with the data once it's in the clipboard? Copy paste, is almost never done with a relational database. We have queries to select data. We don't need to write code to copy it from a query into the clipboard and then do some other thing with it from there?

You ASSUMED you knew the solution but didn't know the code technique. I'm pretty sure copy/paste isn't the solution you need.

I want to paste it into a word document

Yes, in Post#15 I noted that it might need a small tweak (commenting out one line) but, as noted, it may still break because of the parameter in the saved query which may need to be evaluated first.

Also, you may need to change the CommandType parameter (I have added below some extra constant declarations)

However, try this and see where you get to from here:

' ...
  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1,
        adCmdTable As Integer = 2,
        adCmdStoredProc As Integer = 4,
        adCmdUnknown As Integer = 8
' ...
  strSQL = Me.DisplayQrylb.RowSource
'  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdUnknown)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy
' ...

Put it behind a button, but when I copied into VB a lot of red showd up;

I tried some hunches but nothing worked. I am a VBWIT (VB Wizard,...... in training)

 

[cheekybuddha]

Sorry, not enough coffee when I posted this morning

It should have been:

' ...
  Const adCmdText As Integer = 1, _
        adCmdTable As Integer = 2, _
        adCmdStoredProc As Integer = 4, _
        adCmdUnknown As Integer = 8
' ...

 

[jpl458]

Sorry, not enough coffee when I posted this morning

It should have been:

' ...
  Const adCmdText As Integer = 1, _
        adCmdTable As Integer = 2, _
        adCmdStoredProc As Integer = 4, _
        adCmdUnknown As Integer = 8
' ...

I know the feeling.

Plugged it in and got this, again:

Code looks like;

  Dim strSQL As String, strCopy As String
  Const adCmdText As Integer = 1, _
        adCmdTable As Integer = 2, _
        adCmdStoredProc As Integer = 4, _
        adCmdUnknown As Integer = 8
  ' ...
  strSQL = Me.DisplayQrylb.RowSource
'  strSQL = Replace(strSQL, "[Forms]![QueriesFrm]![SelectCallCocb]", " '" & Forms.QueriesFrm.SelectCallCocb & "' ")
  With CurrentProject.Connection.Execute(strSQL, adCmdUnknown)
    strCopy = .GetString(, , ",", vbNewLine)
    .Close
  End With
  Set objCP = CreateObject("HtmlFile")
  objCP.ParentWindow.ClipboardData.SetData "text", strCopy

here is the bad line;

 With CurrentProject.Connection.Execute(strSQL, adCmdUnknown)

The RowSource is QryPhoneCalls

Thanks a ton. Really appreciate your help.