你在EXCEL中增加一个列名为ID,后在VBA中写以下代码,并引用Microsoft ActiveX Data Objects 2.8后执行
Public Sub 写入SQL2008() Dim cnn As New ADODB.Connection Dim SQL As String, mydata As String, mytable As String Dim i% mydata = KKKK '指定要修改的数据库 mytable = aaaa '指定数据表
'建立与指定SQL Server数据库的连接 cnn.ConnectionString = Provider=SQLOLEDB; _ & User ID=sa; _ 'sa改成你SQL账号(通常不用改) & Password =123; _ '123改成你SQL2008密码 & Data Source=sowin; _ 'sowin改成你的SQL2008的电脑名, '如sql2008在网络上,则sowin改成IP地址(如192.168.0.1) & Initial Catalog = & mydata cnn.Open '查询全表某些字段的记录 for i = 2 to [a65536].end(xlup).row SQL = UPDATE & mytable & SET x= & cells(i,1) & , & _ y= & cells(i,2) & , & _ z= & cells(i,3) & , & _ where id=11 next i '注意引号里面的空格 '数据库没有更新的数据则用INSERT INTO 'SQL = INSERT INTO & mytable & COLUMNS(x,y,z) & _ VALUES(' & CELLS(i,1) & ',' & cells(i,2) & ',' & cells(i,3) &')