VBA链接SQL server数据库

创建一个函数

Public Function search_sql()
Dim I2 As Integer, j2 As Integer, sht As Worksheet
Dim R, C, F, I As Integer
Dim strCn As String, strSQL As String
Dim cn As Object
Dim rs As Object
链接当前的excel表  sys是我的excel表名
Set sht = ActiveWorkbook.Worksheets("sys")
Set cn = CreateObject("Adodb.Connection")
Set rs = CreateObject("Adodb.Recordset")

数据库链接字符串
strCn = "Provider=sqloledb;Server=自己的IP地址;Database=数据库名称;Uid=用户名;Pwd=密码"

SQL server中的sql语句
strSQL = "select Q.Content as QuestionContent,A.Content,A.Ctime,A.Mtime,U.UserName,case when U.UserGender=1 then '女'else '男'end  as Sex,U.Age,U.City,U.Height,U.Weight,U.PhoneNumber,U.UserGuid from 表1 A join 表2 Q on A.QuestionGuid=Q.QuestionGuid join 表3 U on U.UserGuid=A.Userid"
cn.Open strCn
rs.Open strSQL, cn
R = 3
C = 1
Do While Not rs.EOF
rs中的内容是你的字段名称如果你的多表查询那么可以在sql语句中起一个别名人rs中填写别名就ok

sht中的Cell代表的事excel表中单元格的位置此处用了一个循环
sht.Cells(R, C) = rs("QuestionContent")
sht.Cells(R, C + 1) = rs("Content")
sht.Cells(R, C + 2) = rs("Ctime")
sht.Cells(R, C + 3) = rs("Mtime")
sht.Cells(R, C + 4) = rs("UserName")
sht.Cells(R, C + 5) = rs("Sex")
sht.Cells(R, C + 6) = rs("Age")
sht.Cells(R, C + 7) = rs("City")
sht.Cells(R, C + 8) = rs("Height")
sht.Cells(R, C + 9) = rs("Weight")
sht.Cells(R, C + 10) = rs("PhoneNumber")
sht.Cells(R, C + 11) = rs("UserGuid")
rs.MoveNext
R = R + 1
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Function

将查询出来的数据进行删除从第三行开始一直到65536行如果要删除一行那么可以将Range换成Rows里面写你要删除的那个行数就ok

Sub del()
Range("3:65536").Delete
End Sub

我弄了一个按钮这个是按钮中执行的函数
Sub 按钮1_Click()
Call search_sql
End Sub