Export Data To Clipboard

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I would like to export the data returned from a query by
pressing a command button. Ideally I would like to send
the data to clipboard so I can paste it into another non-
Microsoft application.
Is this possible?
I can use a macro to save the data as a file but this is a
bit cumbersome and the text has a table format which I
don't want.

All help is appreciated

Nick
 
Hi Nick,

One approach is to use something like this:

DoCmd.OpenQuery "MyQUery", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose

Alternatively - e.g. if you want the clipboard contents formatted
differently, you could write code along these lines:

Open a recordset on the query
Dim a string variable
For each record
For each field
Append the contents of the field to a string
Append the field separator character(s) to the string
unless it's the last field
Next field
Append record separator character(s), e.g. CR LF
Next record
Put the string on the clipboard
Close the recordset

This article
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q138910 provides
basic clipboard support to VBA. (Don't worry
that it only states Access 95/97: it's the same for all 32-bit
applications)
 
John thanks very much.
The first example worked fine but as you expected the
formatting was carried over. When I go to the application
I want to paste, the field heading is pasted and the last
record has part of the record and hashes, eg 2##7#4#3
I went to the web site but unfortunately I'm not very
conversant with code.
My query, "Order" only has one column, "Number" with an
eight digit number as the result.

So I hope you can help me a bit further?

Nick

-----Original Message-----
Hi Nick,

One approach is to use something like this:

DoCmd.OpenQuery "MyQUery", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose

Alternatively - e.g. if you want the clipboard contents formatted
differently, you could write code along these lines:

Open a recordset on the query
Dim a string variable
For each record
For each field
Append the contents of the field to a string
Append the field separator character(s) to the string
unless it's the last field
Next field
Append record separator character(s), e.g. CR LF
Next record
Put the string on the clipboard
Close the recordset

This article
http://support.microsoft.com/default.aspx?scid=kb;EN- GB;q138910 provides
basic clipboard support to VBA. (Don't worry
that it only states Access 95/97: it's the same for all 32-bit
applications)



I would like to export the data returned from a query by
pressing a command button. Ideally I would like to send
the data to clipboard so I can paste it into another non-
Microsoft application.
Is this possible?
I can use a macro to save the data as a file but this is a
bit cumbersome and the text has a table format which I
don't want.

All help is appreciated

Nick
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Air code, will need checking and correcting. You will also need to get
the code from
http://support.microsoft.com/default.aspx?scid=kb;en-us;138909
in order to make the ClipBoard_SetData() function available.

Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant

'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("Order")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Number") _
& vbCRLF
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)


John thanks very much.
The first example worked fine but as you expected the
formatting was carried over. When I go to the application
I want to paste, the field heading is pasted and the last
record has part of the record and hashes, eg 2##7#4#3
I went to the web site but unfortunately I'm not very
conversant with code.
My query, "Order" only has one column, "Number" with an
eight digit number as the result.

So I hope you can help me a bit further?

Nick

-----Original Message-----
Hi Nick,

One approach is to use something like this:

DoCmd.OpenQuery "MyQUery", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose

Alternatively - e.g. if you want the clipboard contents formatted
differently, you could write code along these lines:

Open a recordset on the query
Dim a string variable
For each record
For each field
Append the contents of the field to a string
Append the field separator character(s) to the string
unless it's the last field
Next field
Append record separator character(s), e.g. CR LF
Next record
Put the string on the clipboard
Close the recordset

This article
http://support.microsoft.com/default.aspx?scid=kb;EN- GB;q138910 provides
basic clipboard support to VBA. (Don't worry
that it only states Access 95/97: it's the same for all 32-bit
applications)



I would like to export the data returned from a query by
pressing a command button. Ideally I would like to send
the data to clipboard so I can paste it into another non-
Microsoft application.
Is this possible?
I can use a macro to save the data as a file but this is a
bit cumbersome and the text has a table format which I
don't want.

All help is appreciated

Nick
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
John,
I create a module called modClipboard; I then copied from
the web site and pasted the first section to the General
Declaration of the module. I next copied the second
section to the General ClipBoard_SetData. I didn't make
any changes to the code from the web site.
I then went to my form and into the On Click, Event
Procedure and pasted the code that you posted. The next
thing when I pressed the command button a Microsoft Visual
Basic, Run-time error '3061': To few parameters. Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here

Nick
-----Original Message-----
Air code, will need checking and correcting. You will also need to get
the code from
http://support.microsoft.com/default.aspx?scid=kb;en- us;138909
in order to make the ClipBoard_SetData() function available.

Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant

'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("Order")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Number") _
& vbCRLF
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)


John thanks very much.
The first example worked fine but as you expected the
formatting was carried over. When I go to the application
I want to paste, the field heading is pasted and the last
record has part of the record and hashes, eg 2##7#4#3
I went to the web site but unfortunately I'm not very
conversant with code.
My query, "Order" only has one column, "Number" with an
eight digit number as the result.

So I hope you can help me a bit further?

Nick

-----Original Message-----
Hi Nick,

One approach is to use something like this:

DoCmd.OpenQuery "MyQUery", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose

Alternatively - e.g. if you want the clipboard contents formatted
differently, you could write code along these lines:

Open a recordset on the query
Dim a string variable
For each record
For each field
Append the contents of the field to a string
Append the field separator character(s) to the string
unless it's the last field
Next field
Append record separator character(s), e.g. CR LF
Next record
Put the string on the clipboard
Close the recordset

This article
http://support.microsoft.com/default.aspx?scid=kb;EN- GB;q138910 provides
basic clipboard support to VBA. (Don't worry
that it only states Access 95/97: it's the same for all 32-bit
applications)


On Sun, 5 Dec 2004 05:20:42 -0800, "Nick"

I would like to export the data returned from a query by
pressing a command button. Ideally I would like to send
the data to clipboard so I can paste it into another non-
Microsoft application.
Is this possible?
I can use a macro to save the data as a file but this
Click to expand...
is
a
bit cumbersome and the text has a table format which I
don't want.

All help is appreciated

Nick

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Nick,

This is most likely because qDaily_Job_Sheets_Complete is expecting two
parameters (probably criteria that determine what records it should
return) but can't find them. Using OpenRecordset works directly with the
Jet database engine, which doesn't know about Access forms.

Assuming that's the case, I think there are two ways to go.

1) Modify your code so that it builds a SQL statement equivalent to the
query with parameters included and uses this to open the recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form (this lets it
find its parameters in the usual way). Then, instead of opening a new
recordset and iterating through that, just do the same thing with the
form's RecordsetClone.
 
Hi John,
Well if finely got it to copy to the clipboard without the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick
-----Original Message-----
Nick,

This is most likely because qDaily_Job_Sheets_Complete is expecting two
parameters (probably criteria that determine what records it should
return) but can't find them. Using OpenRecordset works directly with the
Jet database engine, which doesn't know about Access forms.

Assuming that's the case, I think there are two ways to go.

1) Modify your code so that it builds a SQL statement equivalent to the
query with parameters included and uses this to open the recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form (this lets it
find its parameters in the usual way). Then, instead of opening a new
recordset and iterating through that, just do the same thing with the
form's RecordsetClone.

The next
thing when I pressed the command button a Microsoft Visual
Basic, Run-time error '3061': To few parameters. Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Hi Nick,

I've just used this code:
DoCmd.OpenQuery "FOJ", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose
to copy the records returned by a query to the clipboard. The query
returns the field name and a dozen records; they are placed on the
clipboard as both plain text and RTF text (and perhaps in other formats)
and have pasted correctly into the five non-Microsoft applications I've
tried so far.

So I suspect the problem is with the particular non-Microsoft
application that you are using and the way it accesses the clipboard. It
could be that it's accessing the formatted text on the clipboard but
interpreting it incorrectly.

If the application allows, use Paste Special instead of Paste, and
select Unformatted Text (or similar).

Otherwise, you could use the VBA approach I suggested, which would to
put plain text on the clipboard without the formatted text (in which
case the only thing to paste will be plain text, and the application
will paste that for want of anything else).

Another approach, which should be possible but which I've never actually
tried, would be to use RunCommand as above, and then clear all but the
plain text version from the clipboard. This will require Windows API
calls or perhaps the Visual Basic Clipboard object.

Hi John,
Well if finely got it to copy to the clipboard without the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick
-----Original Message-----
Nick,

This is most likely because qDaily_Job_Sheets_Complete is expecting two
parameters (probably criteria that determine what records it should
return) but can't find them. Using OpenRecordset works directly with the
Jet database engine, which doesn't know about Access forms.

Assuming that's the case, I think there are two ways to go.

1) Modify your code so that it builds a SQL statement equivalent to the
query with parameters included and uses this to open the recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form (this lets it
find its parameters in the usual way). Then, instead of opening a new
recordset and iterating through that, just do the same thing with the
form's RecordsetClone.

The next
thing when I pressed the command button a Microsoft Visual
Basic, Run-time error '3061': To few parameters. Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thanks John,
I have found two way of pasting, one of which works OK.
The only problem that I just don't know how to fix is if
the copy button is pressed with nothing to copy an error
comes up as follows;
Run-timeerror '5':
Invalid procedure call or argument - Debug

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant
'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub

The second last line is highlighted - strBuffer = Left
(strBuffer, Len(strBuffer) - 2)
Is it possible to have a message come up on error to state
that there is nothing to copy and then press OK to close
the error?

Thanks again for your help.
Nick
-----Original Message-----
Hi Nick,

I've just used this code:
DoCmd.OpenQuery "FOJ", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose
to copy the records returned by a query to the clipboard. The query
returns the field name and a dozen records; they are placed on the
clipboard as both plain text and RTF text (and perhaps in other formats)
and have pasted correctly into the five non-Microsoft applications I've
tried so far.

So I suspect the problem is with the particular non- Microsoft
application that you are using and the way it accesses the clipboard. It
could be that it's accessing the formatted text on the clipboard but
interpreting it incorrectly.

If the application allows, use Paste Special instead of Paste, and
select Unformatted Text (or similar).

Otherwise, you could use the VBA approach I suggested, which would to
put plain text on the clipboard without the formatted text (in which
case the only thing to paste will be plain text, and the application
will paste that for want of anything else).

Another approach, which should be possible but which I've never actually
tried, would be to use RunCommand as above, and then clear all but the
plain text version from the clipboard. This will require Windows API
calls or perhaps the Visual Basic Clipboard object.

Hi John,
Well if finely got it to copy to the clipboard without the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick
-----Original Message-----
Nick,

This is most likely because qDaily_Job_Sheets_Complete
Click to expand...
is
expecting two
parameters (probably criteria that determine what
Click to expand...
records
it should
return) but can't find them. Using OpenRecordset works directly with the
Jet database engine, which doesn't know about Access forms.

Assuming that's the case, I think there are two ways to go.

1) Modify your code so that it builds a SQL statement equivalent to the
query with parameters included and uses this to open
Click to expand...
the
recordset, e.g.
....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form (this lets it
find its parameters in the usual way). Then, instead of opening a new
recordset and iterating through that, just do the same thing with the
form's RecordsetClone.

On Wed, 8 Dec 2004 05:51:29 -0800, "Nick"

The next
thing when I pressed the command button a Microsoft Visual
Basic, Run-time error '3061': To few parameters. Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
I'd use something like this, to check whether the recordset has any
records before assembling strBuffer:

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant

'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
'Check whether recordset is empty
If rstR.EOF Then
MsgBox "Recordset is empty. Nothing to copy", _
vbInformation + vbOKOnly
Else
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
End If
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub
Thanks John,
I have found two way of pasting, one of which works OK.
The only problem that I just don't know how to fix is if
the copy button is pressed with nothing to copy an error
comes up as follows;
Run-timeerror '5':
Invalid procedure call or argument - Debug

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant
'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub

The second last line is highlighted - strBuffer = Left
(strBuffer, Len(strBuffer) - 2)
Is it possible to have a message come up on error to state
that there is nothing to copy and then press OK to close
the error?

Thanks again for your help.
Nick
-----Original Message-----
Hi Nick,

I've just used this code:
DoCmd.OpenQuery "FOJ", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose
to copy the records returned by a query to the clipboard. The query
returns the field name and a dozen records; they are placed on the
clipboard as both plain text and RTF text (and perhaps in other formats)
and have pasted correctly into the five non-Microsoft applications I've
tried so far.

So I suspect the problem is with the particular non- Microsoft
application that you are using and the way it accesses the clipboard. It
could be that it's accessing the formatted text on the clipboard but
interpreting it incorrectly.

If the application allows, use Paste Special instead of Paste, and
select Unformatted Text (or similar).

Otherwise, you could use the VBA approach I suggested, which would to
put plain text on the clipboard without the formatted text (in which
case the only thing to paste will be plain text, and the application
will paste that for want of anything else).

Another approach, which should be possible but which I've never actually
tried, would be to use RunCommand as above, and then clear all but the
plain text version from the clipboard. This will require Windows API
calls or perhaps the Visual Basic Clipboard object.

Hi John,
Well if finely got it to copy to the clipboard without the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick

-----Original Message-----
Nick,

This is most likely because qDaily_Job_Sheets_Complete is
expecting two
parameters (probably criteria that determine what records
it should
return) but can't find them. Using OpenRecordset works
directly with the
Jet database engine, which doesn't know about Access
forms.

Assuming that's the case, I think there are two ways to
go.

1) Modify your code so that it builds a SQL statement
equivalent to the
query with parameters included and uses this to open the
recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form
(this lets it
find its parameters in the usual way). Then, instead of
opening a new
recordset and iterating through that, just do the same
thing with the
form's RecordsetClone.

On Wed, 8 Dec 2004 05:51:29 -0800, "Nick"

The next
thing when I pressed the command button a Microsoft
Visual
Basic, Run-time error '3061': To few parameters.
Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Hi John,
Can you explain the second last line, strBuffer = Left
(strBuffer, Len(strBuffer) - 2)? If I leave this line in I
get an error, however if I remove the line all seems to
work.
Regards
Nick
-----Original Message-----
I'd use something like this, to check whether the recordset has any
records before assembling strBuffer:

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant

'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
'Check whether recordset is empty
If rstR.EOF Then
MsgBox "Recordset is empty. Nothing to copy", _
vbInformation + vbOKOnly
Else
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
End If
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub
Thanks John,
I have found two way of pasting, one of which works OK.
The only problem that I just don't know how to fix is if
the copy button is pressed with nothing to copy an error
comes up as follows;
Run-timeerror '5':
Invalid procedure call or argument - Debug

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant
'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub

The second last line is highlighted - strBuffer = Left
(strBuffer, Len(strBuffer) - 2)
Is it possible to have a message come up on error to state
that there is nothing to copy and then press OK to close
the error?

Thanks again for your help.
Nick
-----Original Message-----
Hi Nick,

I've just used this code:
DoCmd.OpenQuery "FOJ", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose
to copy the records returned by a query to the
Click to expand...
clipboard.
The query
returns the field name and a dozen records; they are placed on the
clipboard as both plain text and RTF text (and perhaps
Click to expand...
in
other formats)
and have pasted correctly into the five non-Microsoft applications I've
tried so far.

So I suspect the problem is with the particular non- Microsoft
application that you are using and the way it accesses the clipboard. It
could be that it's accessing the formatted text on the clipboard but
interpreting it incorrectly.

If the application allows, use Paste Special instead of Paste, and
select Unformatted Text (or similar).

Otherwise, you could use the VBA approach I suggested, which would to
put plain text on the clipboard without the formatted text (in which
case the only thing to paste will be plain text, and
Click to expand...
the
application
will paste that for want of anything else).

Another approach, which should be possible but which
Click to expand...
I've
never actually
tried, would be to use RunCommand as above, and then clear all but the
plain text version from the clipboard. This will
Click to expand...
require
Windows API
calls or perhaps the Visual Basic Clipboard object.

On Sun, 12 Dec 2004 06:06:17 -0800, "Nick"

Hi John,
Well if finely got it to copy to the clipboard without the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick

-----Original Message-----
Nick,

This is most likely because
Click to expand...
qDaily_Job_Sheets_Complete
is
expecting two
parameters (probably criteria that determine what records
it should
return) but can't find them. Using OpenRecordset works
directly with the
Jet database engine, which doesn't know about Access
forms.

Assuming that's the case, I think there are two ways to
go.

1) Modify your code so that it builds a SQL statement
equivalent to the
query with parameters included and uses this to open the
recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form
(this lets it
find its parameters in the usual way). Then, instead of
opening a new
recordset and iterating through that, just do the same
thing with the
form's RecordsetClone.

On Wed, 8 Dec 2004 05:51:29 -0800, "Nick"

The next
thing when I pressed the command button a Microsoft
Visual
Basic, Run-time error '3061': To few parameters.
Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to go
from here

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Each time something is added to strBuffer, a carriage return and line feed
is added to it as well. That code takes the final CR/LF off the string.

The problem is that if the recordset is empty, strBuffer doesn't contain
anything. Try moving that line between the End With and End If statements.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Nick said:
Hi John,
Can you explain the second last line, strBuffer = Left
(strBuffer, Len(strBuffer) - 2)? If I leave this line in I
get an error, however if I remove the line all seems to
work.
Regards
Nick
-----Original Message-----
I'd use something like this, to check whether the recordset has any
records before assembling strBuffer:

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant

'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
'Check whether recordset is empty
If rstR.EOF Then
MsgBox "Recordset is empty. Nothing to copy", _
vbInformation + vbOKOnly
Else
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
End If
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub
Thanks John,
I have found two way of pasting, one of which works OK.
The only problem that I just don't know how to fix is if
the copy button is pressed with nothing to copy an error
comes up as follows;
Run-timeerror '5':
Invalid procedure call or argument - Debug

Private Sub Command62_Click()
Dim rstR As DAO.Recordset
Dim strBuffer As String
Dim varRetVal As Variant
'Open recordset
Set rstR = DBEngine(0)(0).OpenRecordset("tbClipboard")
With rstR
'Loop through records
Do While Not .EOF
'Only one field involved, so no inner loop needed
strBuffer = strBuffer & .Fields("Order") _
& vbCrLf
.MoveNext
Loop
.Close
End With
Set rstR = Nothing

'strBuffer now contains all the records with a CRLF after
each.
'we need to remove the last CRLF
strBuffer = Left(strBuffer, Len(strBuffer) - 2)
varRetVal = ClipBoard_SetData(strBuffer)

End Sub

The second last line is highlighted - strBuffer = Left
(strBuffer, Len(strBuffer) - 2)
Is it possible to have a message come up on error to state
that there is nothing to copy and then press OK to close
the error?

Thanks again for your help.
Nick

-----Original Message-----
Hi Nick,

I've just used this code:
DoCmd.OpenQuery "FOJ", acViewNormal, acReadOnly
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdClose
to copy the records returned by a query to the clipboard.
The query
returns the field name and a dozen records; they are
placed on the
clipboard as both plain text and RTF text (and perhaps in
other formats)
and have pasted correctly into the five non-Microsoft
applications I've
tried so far.

So I suspect the problem is with the particular non-
Microsoft
application that you are using and the way it accesses
the clipboard. It
could be that it's accessing the formatted text on the
clipboard but
interpreting it incorrectly.

If the application allows, use Paste Special instead of
Paste, and
select Unformatted Text (or similar).

Otherwise, you could use the VBA approach I suggested,
which would to
put plain text on the clipboard without the formatted
text (in which
case the only thing to paste will be plain text, and the
application
will paste that for want of anything else).

Another approach, which should be possible but which I've
never actually
tried, would be to use RunCommand as above, and then
clear all but the
plain text version from the clipboard. This will require
Windows API
calls or perhaps the Visual Basic Clipboard object.

On Sun, 12 Dec 2004 06:06:17 -0800, "Nick"

Hi John,
Well if finely got it to copy to the clipboard without
the
formatting, sort of. This work when pasting to Microsoft
applications but unfortunately when I paste into another
it has another fault. As in one of the previous posts
the
result is;
20367034
20368823
10606540
1#0#6#1#
The difference is that now the word Order does not
paste.
If I paste into Excel or Word first and then re-copy the
four numbers, they paste into my other application fine.
Do you have any suggestions?

Thanks
Nick

-----Original Message-----
Nick,

This is most likely because qDaily_Job_Sheets_Complete
is
expecting two
parameters (probably criteria that determine what
records
it should
return) but can't find them. Using OpenRecordset works
directly with the
Jet database engine, which doesn't know about Access
forms.

Assuming that's the case, I think there are two ways to
go.

1) Modify your code so that it builds a SQL statement
equivalent to the
query with parameters included and uses this to open
the
recordset, e.g.

....
Dim strSQL As String
....

strSQL = "SELECT Number FROM MyTable " _
& "WHERE (SomeField = " & SomeVariable _
& ") AND (OtherField = " & OtherVariable _
& ");"
....
set rstR = DbEngine(0)(0).OpenRecordset(strSQL)
....

2) Use the existing query as the recordsource of a form
(this lets it
find its parameters in the usual way). Then, instead of
opening a new
recordset and iterating through that, just do the same
thing with the
form's RecordsetClone.

On Wed, 8 Dec 2004 05:51:29 -0800, "Nick"

The next
thing when I pressed the command button a Microsoft
Visual
Basic, Run-time error '3061': To few parameters.
Expected
2.
When I went to the De-bug this line was highlighted
Set rstR = DBEngine(0)(0).OpenRecordset
("qDaily_Job_Sheets_Complete") I don't know where to
go
from here

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.


--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thanks, Doug.


Each time something is added to strBuffer, a carriage return and line feed
is added to it as well. That code takes the final CR/LF off the string.

The problem is that if the recordset is empty, strBuffer doesn't contain
anything. Try moving that line between the End With and End If statements.
Click to expand...
 
Back
Top