用EXCEL连接MSSQL简单生成报表

1、打开EXCEL选择开发工具，点设计模式，再插入表单控件，选择按钮。如图再设置好两个单元格作为输入条件的内容。

2、通过快捷键ALT+F11，调出Microsoft Visual Basic for Applications，输入连接数据库代码和执行代码，参考如下：

Private Sub CommandButton1_Click()

          Dim conn1 As New ADODB.Connection, ConnStr1 As String, rds1 As New ADODB.Recordset, blstr1 As String, blstr2 As String

          Dim sqlstr1 As String

          Dim sqlstr2 As String

          blstr1 = UCase(ActiveSheet.Range("B1"))

          blstr2 = UCase(ActiveSheet.Range("D1"))

          ActiveSheet.Range("A3:IV65536").ClearContents

          ConnStr1 = "Provider=SQLOLEDB.1;Password=密码;Persist Security Info=True;User ID=用户名;Initial Catalog=数据库;Data Source=数据库来源地址"

          sqlstr1 = "select * from 表1 where A列='" & blstr1 & "%' and B列='" & blstr2 & "%'"

          conn1.Open ConnStr1

          rds1.CursorLocation = adUseClient

          rds1.Open sqlstr1, conn1, adOpenForwardOnly, adLockReadOnly

          ActiveSheet.Range("a3").CopyFromRecordset rds1

          ActiveSheet.Range("A3", "R" & (2 + rds1.RecordCount)).Borders.Lin           eStyle = xlContinuous

          rds1.Close

          conn1.Close

End Sub

3、结果如图，代码中可以更改一些参数或单元格编号来使刷新出来的表格带有框线，如ActiveSheet.Range("a3").CopyFromRecordset rds1，代表从A3格开始输出，ActiveSheet.Range("A3", "R" & (2 + rds1.RecordCount)).Borders.Lin，代表A3到R列有数据的内容增加框线。