I have an excel spreadsheet that contains开发者_Go百科 the primarky-key/id value for a record in a table in an access database.
I would like to export specific data from certain cells in the spreadsheet to certain fields in the corresponding record in the table.
Is this possible, any help would be greatly appreciated.
Many thanks
Noel
You can use ADO with Excel and Access. You can either open an Access recordset and update or add fields (columns) and records one by one, or you can use an SQL statement with IN key word or an internal connection string.
Very roughly:
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\docs\mydb.mdb"
Set cn = CreateObject("ADODB.Connection")
cn.Open strCon
strSQL="SELECT * INTO NewTable FROM [Sheet1$] IN '' " _
& "[Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\Docs\WB.xls]"
cn.Execute strSQL
精彩评论