VBA的数据库导入和导出

Sub 导数据库()

Dim conn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表Dim strCn As String, strSQL As String '字符串变量strCn = "Provider=库名;Server=(IP地址);Database=test;Uid=sa;Pwd=sa;"    '定义数据库链接字符串

conn.Open strCn

i = 2j = 1Sheets(j).SelectDo While Cells(i, 1) <> ""Cells(i, 1).SelectSql = "insert语句"

conn.Execute Sql

i = i + 1

If i > 65536 Theni = 2j = j + 1

If j > Sheets.Count ThenExit DoEnd If

Sheets(j).SelectEnd IfLoop

conn.Closeset conn=NothingEnd Sub

 

 

Sub 读数据库()

Dim conn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用Dim rs As New ADODB.Recordset  '定义记录集对象，保存数据表Dim strCn As String, strSQL As String '字符串变量strCn = "Provider=库名;Server=(ip地址);Database=test;Uid=sa;Pwd=sa;"    '定义数据库链接字符串

conn.Open strCn

Sql = "查询语句"

rs.ActiveConnection = conn

rs.Open SqlRange("A55520").CopyFromRecordset rsrs.Closeconn.Closeset rs=Nothingset conn=NothingEnd Sub