Excel通过VBA脚本将SQL数据显示到Excel

silence_dungpark

已于 2023-10-24 09:50:06 修改

阅读量305

点赞数

文章标签： 1024程序员节 sql excel

于 2023-10-24 09:46:53 首次发布

本文链接：https://blog.csdn.net/silence_dungpark/article/details/134004718

版权

软件：Excel2007、SQL2008

功能需求：

SQL数据库中有5个Table，通过Excel将不同Table中的数据（含表头），导出到Excel中；

脚本包含：

链接SQL数据库脚本；
数据导出后，固定Excel第几行插入；
需要将SQL表头同时导出到Excel中；
每次表头内容都不一样，需要根据每次查询条件进行刷新；
每次查询后，需要从第几行进行数据清除。

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ss As String
Dim StartData As String
Dim EndData As String
Dim ER As String
Dim DD As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
Range("3:" & Rows.Count).Clear
ER = Sheet1.ComboBox1.Text
If ER = "C802_1" Then
    DD = "date1"
ElseIf ER = "C802_2" Then
    DD = "date2"
ElseIf ER = "C802_3" Then
    DD = "date3"
ElseIf ER = "C802_4" Then
    DD = "date4"
ElseIf ER = "C802_5" Then
    DD = "date5"
Else
    DD = "date1"
End If

Startyear = Mid(Range("Datatime1").Value, 1, 4)
Startmon = Mid(Range("Datatime1").Value, 6, 2)
Startday = Mid(Range("Datatime1").Value, 9, 2)
StartData = Startyear + "/" + Startmon + "/" + Startday
Endyear = Mid(Range("Datatime2").Value, 1, 4)
Endmon = Mid(Range("Datatime2").Value, 6, 2)
Endday = Mid(Range("Datatime2").Value, 9, 2)
EndData = Endyear + "/" + Endmon + "/" + Endday

conn.ConnectionString = "Provider=sqloledb;Server=127.0.0.1;Database=DataCollcetion;Integrated Security=SSPI;Persist Security Info=False;"
conn.Open
sqlcn = "select * from " + ER + " where " + DD + " BETWEEN  '" + StartData + "' AND  '" + EndData + "' order by " + DD + " desc"

rs.Open sqlcn, conn
'Range("3:" & Rows.Count).Clear   '从三行开始删除
For i = 0 To rs.Fields.Count - 1   '增加表头
    Sheet1.Cells(3, i + 1) = rs.Fields(i).Name
Next i
Range("A4").CopyFromRecordset rs

rs.Close: Set rs = Nothing
conn.Close: Set conn = Nothing