如何使用vba 将excel指定行的数据导入到sql2008中?

发布网友发布时间：2022-04-22 21:56

共1个回答

热心网友时间：2023-08-01 10:20

你在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)
&"')"
cnn.Execute(SQL)
cnn.Close
Set
cnn
=
Nothing
End
Sub