如何实现EXCEL实时提取SQL中表的数据？

Question

如何用一个按钮实现 点一下按钮自动从SQL中表数据更新到我的EXCEL中，实现EXCEL表中的数据与SQL数据同步

Answer 1

用VBA实现
Sub 连接数据库1()
Set Cnn = CreateObject("ADODB.Connection")
Set rt = CreateObject("ADODB.Recordset")
ID = "localhost"
Cnn.ConnectionString = "Provider = SQLOLEDB;server=" & ID & ";User ID= sa;password=sa;Data Source=" & test & ";" & "Initial Catalog = test"
Cnn.Open
SQL = "select * from 成绩表 "
Set rt = Cnn.Execute(SQL)
With Sheet1
For i = 0 To rt.Fields.Count - 1
.Cells(1, i + 1) = rt.Fields(i).Name
Next i
.Range("a2").CopyFromRecordset rt
.Cells.EntireColumn.AutoFit
End With
Set Cnn = Nothing
Set rt = Nothing
End Sub

Answer 2

您可以用powerbi（Excel2016及以上版本自带，其他低版本需要下载插件；或者下载powerbi desktop）

如果有powerbi专业版，将数据模型发布到powerbi网络服务后，还能自动刷新数据，连按钮都省了。

本回答被网友采纳

Answer 3

用VBA实现
Sub 连接数据库1()
Set Cnn = CreateObject("ADODB.Connection")
Set rt = CreateObject("ADODB.Recordset")
ID = "localhost"
Cnn.ConnectionString = "Provider = SQLOLEDB;server=" & ID & ";User ID= sa;password=sa;Data Source=" & test & ";" & "Initial Catalog = test"
Cnn.Open
SQL = "select * from 成绩表 "
Set rt = Cnn.Execute(SQL)
With Sheet1
For i = 0 To rt.Fields.Count - 1
.Cells(1, i + 1) = rt.Fields(i).Name
Next i
.Range("a2").CopyFromRecordset rt
.Cells.EntireColumn.AutoFit