Solved: Copy from Datasheetview to clipboard

[johnny]

Hi I have a VB form in datasheet view.
I want to copy the data from the datasheet to clipboard using vba.
I want to select the contents of the datasheet and paste set them in clipboard so that I can paste the content to Excel.
I'm new to VBA.
Any help will be greatly appreciated.

[Kenneth Hobs]

Welcome to the forum!

I am not sure what you mean by a vb form dataview. Is that a control from a Form dialog in vb?

Typically, one uses the object properties to do such things. Sendkeys can be used somethings. The DataObject can be used to set text. e.g.
Private Sub setClipboard()
'Reference: Microsoft Forms 2.0 Object
Dim MyData As DataObject

On Error Resume Next
Set MyData = New DataObject
MyData.SetText strClip
MyData.PutInClipboard

End Sub

Other times, one might need to use API commands.

If you attach an example, it might clear things up.

[johnny]

I tried the following code.

Private Sub ExportExcel_GotFocus()
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
End Sub

It does copy to the clipboard but not from the subform as it is called from the main form.
Can you tell me how to access the subform from the main form.

[Kenneth Hobs]

I recommend that you post your question to an Access forum. DoCmd is an Access command.

I am not sure that you need to use copy. If the goal is to put Access data into Excel, there are other ways. One typically uses ADO to do such things though a QueryTable will work too. Here is a QueryTable example using a stored Access procedure. Access is probably using an SQL for the datasheet.


Sub Test()
Dim mdbPath As String, dbName As String, cmdText As String
Dim rngDestination As String
'mdbPath = "E:\ADO\NWind2003.mdb" 'change the path here to suit your needs
'mdbPath = "c:\myfiles\edrive\excel\ado\NWind2003.mdb"
mdbPath = "//matpc10/ExcelVBAExamples/ado/NWind2003.mdb"
dbName = "NWind2003_1" 'change the database name here to suit your needs
cmdText = "Aug94" 'change the stored SQL here to suit your needs
rngDestination = "A1" 'change the destination range here to suit your needs

'Clear previous data
Cells.Delete

InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination

'Insert other data to the right of A1 with a blank column separating the two
rngDestination = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 2).Address
cmdText = "Sales by Category"
InsertTableWithStoredSQL mdbPath, dbName, cmdText, rngDestination
End Sub

Sub InsertTableWithStoredSQL(mdbPath As String, dbName As String, _
cmdText As String, rngDestination As String, _
Optional bFieldNames = True)

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=" & mdbPath & ";Mode=ReadWrite;Extended Properties=""" _
, """;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Datab" _
, "ase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";J" _
, "et OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Co" _
, "mpact Without Replica Repair=False;Jet OLEDB:SFP=False"), Destination:=Range("" & rngDestination & ""))
.CommandType = xlCmdTable
.CommandText = Array(cmdText)
.Name = dbName
.FieldNames = bFieldNames
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = mdbPath
.Refresh BackgroundQuery:=False
End With
End Sub

[johnny]

Thanx a lot.........
it worked