Copy contents of listbox to clipboard (1 Viewer)

[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.

 

[Pat Hartman]

What are you expecting to get copied to the clipboard?

1. the ControlSource?
2. the RowSource?

 

[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?

 

[Pat Hartman]

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.

 

[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

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

I guess you're still fixated on copy/paste.

You have a typo in the SQL String. What happens when you run the query directly rather than in code? Sometimes you get better error messages. Put a stop on the line of code that executes the query. Print strSQL to the debug window, then copy it and paste it into the QBE and run it. What happens? Also, I'm not sure that Execute can run a select query. I think it only runs action queries and that may be the problem.

Here's a link to a sample that uses OLE automation to populate bookmarks in a word document. It is probably a better method to solve your problem. If you have only one document type to fill, the hard-coding in the sample will be sufficient. If the app will have multiple document types that need filling, there are better methods that use tables to map bookmarks to table/query fields.

Word Automation Sample

This database presents an example of using VBA to fill bookmarked fields in a Word document. The Database is A2003 format. However, the only version of Word installed on my PC is W2007 so before using the example, you may need to open any code module and change the Word reference to whatever...

www.access-programmers.co.uk