VBA Copy and Paste

  • Thread starter Thread starter Ross
  • Start date Start date
R

Ross

This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and mouse.
I can’t believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?
 
Ross said:
This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and
mouse.
I can't believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?
Click to expand...

An alternative way of doing it would be to loop through the QueryDefs
collection:

Sub Untested()
Dim ThisDB as DAO.Database
Dim QDef as DAO.QueryDef
Dim rstDestination as DAO.Recordset

Set ThisDB=CurrentDB()
Set rstDestination=ThisDB.OpenRecordset("tblSQLstore", dbOpenDynaset)

ThisDB.Execute "DELETE * FROM tblSQLStore;",dbFailOnError

For Each QDef in ThisDB.QueryDefs
rstDestination.AddNew
rstDestination!SQL=QDef.SQL
rstDestination.Update
Next QDef
End Sub

Ed Metcalfe.
 
Ross said:
This question (what I want to do) is simple but I am giving a complete
description.

I am in the QBE grid and switch to SQL view. When this view opens, it
automatically highlights the SQL from my query.

This is what I would like to do:

1. Copy the highlighted SQL to my clipboard.
2. Open a table called tblSQLStore
3. Then Paste this SQL into a field (the only field) in this table
4. Close the table.

This is the code that I fire from a hotkey ({F7}):

'*********************
Public Function Set_Up_SQL()
'**********************
Dim strsql As String

'3. Copy in SQL
SendKeys "^c", True

'1. Empty
strsql = "DELETE tblSQLStore.SQL "
strsql = strsql & "FROM tblSQLStore;"
DoCmd.SetWarnings False
DoCmd.RunSQL strsql
DoCmd.SetWarnings True

'2. Open table
DoCmd.OpenTable ("tblSQLStore")

'3. Paste in SQL
SendKeys "^v", True

'4. Close Table
'DoCmd.Close acTable, ("tblSQLstore")

End Function

This code has worked precisely the way that I want for a years and now, is
no longer working. However, it does works on some machines, particularly
without wireless keyboards and mouse. I love my wireless keyboard and
mouse.
I can't believe that that may be the issue but it might.

The reason that I paste into the field (which is memo) is that this
preserves that format which is essential to my purpose.

ie
SELECT tblVersion.VersionNo
FROM tblVersion
ORDER BY tblVersion.VersionNo DESC;

Is there a better, more reliable way to do this? Suggestions?
Click to expand...

BTW - The reason I'd recommend against SendKeys is you don't know which
control or application will have the focus whilst the code is running.
SendKeys is best to be avoided whenever possible.

Ed Metcalfe.
 
Ed,
I agree with you on sendkeys. It is for those (like me, that don't know of
a better way).

I took a look at your code and will give it a try. My only concern is that
I will loose the format which the past process preserves (i need to preserve
the format.

I will let you know what I find. Thanks for you help.

Ross
 
It works. Thanks dude!

Ross

Ed Metcalfe said:
An alternative way of doing it would be to loop through the QueryDefs
collection:

Sub Untested()
Dim ThisDB as DAO.Database
Dim QDef as DAO.QueryDef
Dim rstDestination as DAO.Recordset

Set ThisDB=CurrentDB()
Set rstDestination=ThisDB.OpenRecordset("tblSQLstore", dbOpenDynaset)

ThisDB.Execute "DELETE * FROM tblSQLStore;",dbFailOnError

For Each QDef in ThisDB.QueryDefs
rstDestination.AddNew
rstDestination!SQL=QDef.SQL
rstDestination.Update
Next QDef
End Sub

Ed Metcalfe.
Click to expand...
 
Hi Ed,

I'm trying to basically "copy" a record and then "paste" it to the same
table (using a DAO recordset) and when I took your code, I got an error 3265
rstDestination!SQL=QDef.SQL
Click to expand...

I have the DAO 3.6 in my reference library and I can't figure out what is
going wrong.

Any help is appreciated.

Thanks
 
Back
Top