乔山办公网我们一直在努力
您的位置:乔山办公网 > excel表格制作 > 怎么在<em>VBA</em>中把excel中的数据 写入数据库-vba sql excel数据

怎么在<em>VBA</em>中把excel中的数据 写入数据库-vba sql excel数据

作者:乔山办公网日期:

返回目录:excel表格制作


用VBA将EXCEL内容一次性导入SQL
'工具->引用->Microsoft ActiveX Date Object 2.0
Public Sub SaveData()
Dim Cnn As ADODB.Connection
Dim SQL As String

Set Cnn = New ADODB.Connection

'建立于数据库的链接
'这里根据你的实际值修改ConnectionString = "Driver=SQL Server;Server=<a href="https:///s?wd=%E6%9C%8D%E5%8A%A1%E5%99%A8%E5%90%8D%E7%A7%B0&tn=44039180_cpr&fenlei=mv6quAkxTZn0IZRqIHckPjm4nH00T1d9uHcvryRLuW99uHb1uHK90ZwV5Hcvrjm3rH6sPfKWUMw85HfYnjn4nH6sgvPsT6KdThsqpZwYTjCEQLGCpyw9Uz4Bmy-bIi4WUvYETgN-TLwGUv3EnHnvP1RdPjTsn1R3P1Dkn1DLrf" target="_blank" class="baidu-highlight">服务器名称</a>;Database=数据库;Uid=账号;Pwd=密码;"
With Cnn
.Provider = "SQLOLEDB"
.ConnectionString = "Driver=SQL Server;Server=mxb\sqlexpress;Database=test;Uid=sa;Pwd=xiaoma;"
.Open
End With

'保存数据
r = Range("A65534").End(xlUp).Row
For i = 1 To r
'拼sql
SQL = "insert into T values('" & Cells(i, 1) & "','" & Cells(i, 2) & "'," & Cells(i, 3) & ")"
Cnn.Execute SQL
Next
Cnn.Close
Set Cnn = Nothing
MsgBox "保存成功"

End Sub

上面是通过VBA,插入数据到数据库,下面是从SQL查询Excel,然后直接insert into到数据库,也可以用数据库导入向导e799bee5baa6e59b9ee7ad94332
--查询excel2007
select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\2007.xlsx;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]
--查询excel2003
select * from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:\2003.xls";Extended properties=Excel 5.0')...[Sheet1$]


以下是我用了实现从Excel导数据进ACCESS的代码,你参考一下,应该就可以做出来了:(Excel和Access两个文件放一个文件夹下)
Public Sub 客户正常供价批量维护()
If Cells(4, 6) = "" Then
MsgBox "请先选择需要维护价格的系统!", vbInformation
Cells(4, 6).Select
Exit Sub
Else

Dim i As Integer, j As Integer, k As Integer, sht As Worksheet 'i,j,k为整数变量;sht 为excel工作表对象e5a48de588b6e79fa5e98193333变量,指向某一工作表
Dim cn As New ADODB.Connection '定义数据链接对象 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量
Dim mdbFile As String

On Error GoTo add_err

mdbFile = ActiveWorkbook.Path & "\DY_DATA.mdb"

strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFile '定义数据库链接字符串
cn.Open strCn '与数据库建立连接,如果成功,返回连接对象cn

Set rs = New ADODB.Recordset
rs.Open "dbl直营客户正常供价表", cn, adOpenKeyset, adLockOptimistic

k = Cells(4, 8) + 8
For i = 9 To k
If Cells(i, 7) = "" Then

Else
rs.AddNew
rs(1) = Cells(i, 2)
rs(2) = Cells(i, 7)
rs(3) = Cells(i, 8)

rs.Update
Cells(i, 7) = ""
Cells(i, 8) = ""

End If
Next
MsgBox "数据记录添加成功!", vbInformation
Cells(4, 6).Select
ActiveWorkbook.RefreshAll

add_exit:
Exit Sub
End If
add_err:
MsgBox Err() & vbCrLf & Error()
Resume add_exit
End Sub

Dim conn As New ADODB.Connection
connStr = "Driver={SQL Server};DataBase=test;Server=(local);UID=sa;PWD=123"
conn.Open connStr '连接zhidao数据库
Dim rs As New Recordset
sql = "select * from a" '查看表a
rs.Open sql, conn, 3, 3
if Not rs.EOF then
   For iCols = 0 To rs.fields.Count - 1
       Sheets(1).Cells(1, iCols + 1).Value = rs.fields(iCols).Name
   Next iCols
   Sheets(1).Cells(2, 1).CopyFromRecordset rs
endif

相关阅读

关键词不能为空
极力推荐

ppt怎么做_excel表格制作_office365_word文档_365办公网