Paste from clipboard to an Excel table

[Guest]

Is it possible to paste from the clipboard object directly into a multi-field
MS Acess table, or alternatively into an array.

The data on the clipboard will be multi column and multi row data. The
tabel will have the same number of columns (fields).

I know there are other ways of performing this task, but this option is the
best for the circumstances.

Thanks

 

[Dave Patrick]

Yes, it does work.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Is it possible to paste from the clipboard object directly into a
multi-field
| MS Acess table, or alternatively into an array.
|
| The data on the clipboard will be multi column and multi row data. The
| tabel will have the same number of columns (fields).
|
| I know there are other ways of performing this task, but this option is
the
| best for the circumstances.
|
| Thanks
|
| --
| CW

 

[Guest]

DO you think you could post some sample code as to how to do this?

 

[Dave Patrick]

Sorry you made no mention of a code. Why not use TransferText from access?
http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactTransferText.asp

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| DO you think you could post some sample code as to how to do this?

 

[Dave Patrick]

Should have been TransferSpreadsheet Method
http://msdn.microsoft.com/library/d...y/en-us/vbaac11/html/acmthactTransferText.asp

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Dave Patrick]

Third times the charm;
http://msdn.microsoft.com/library/en-us/vbaac11/html/acmthactTransferSpreadsheet.asp

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

 

[Guest]

Thanks for you response.

The code is actually running in an Excel spreadsheet and I'm opening another
spreadsheet and wanting to paste the data into an Access table.

 

[Dave Patrick]

I would still use the TransferSpreadsheet Method.

Some assumptions;
there is an access database named 'test.mdb' located in 'D:\DATA\Access'
there is a workbook named 'SomeWB.xls' located in 'D:\DATA\Excel'
there are some column names in the workbook at A1:A3
there is some data in the workbook at B1:C3

Paste the code below in a new module in 'D:\DATA\Excel\SomeWB.xls' and run
SendData

------------------------------------
Public Sub SendData()
Dim strConPathToDB As String
strConPathToDB = "D:\DATA\Access\test.mdb"
Dim appAccess As Object
Set appAccess = CreateObject _
("Access.Application." & AccessVersion & "")
appAccess.OpenCurrentDatabase strConPathToDB
appAccess.Visible = False
appAccess.DoCmd.TransferSpreadsheet _
acImport, acSpreadsheetTypeExcel9, _
"tblNames", "D:\DATA\Excel\SomeWB.xls", _
True, "a1:c3"
appAccess.CloseCurrentDatabase
appAccess.Quit
End Sub
Public Function AccessVersion()
Dim WshShell
Set WshShell = CreateObject("WScript.Shell")
Select Case WshShell.RegRead("HKCR\Access.Application\CurVer\")
Case "Access.Application.8"
AccessVersion = 8
Case "Access.Application.9"
AccessVersion = 9
Case "Access.Application.10"
AccessVersion = 10
Case "Access.Application.11"
AccessVersion = 11
End Select
End Function
------------------------------------

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for you response.
|
| The code is actually running in an Excel spreadsheet and I'm opening
another
| spreadsheet and wanting to paste the data into an Access table.

 

[Guest]

Thanks for you help

 

[Dave Patrick]

Yepper

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for you help

 

[Jamie Collins]

I would still use the TransferSpreadsheet Method.

Automate the server application and get it to 'pull' data from the
client? Wrong mental model, methinks. Better to use data access
technology to 'push' data to the client e.g.

Sub Just_Four_Lines()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\DATA\Access\test.mdb"
con.Execute _
"INSERT INTO tblNames" & _
" SELECT F1, F2, F3 FROM [Excel 8.0;HDR=NO;" & _
"Database=D:\DATA\Excel\SomeWB.xls;].[A1:C3];"
End Sub

Jamie.

--