在excel內全部使用SQL語法查詢－zen2965的部落格

狀況一:在同一份工作表內

左邊先從sqlserver把資料抓進來,經過處理顯示在右邊,使用excel巨集來篩選便無法像sql般得心應手

excel程式碼:

Sub 單股營收()
Sheets("營收").Range("p5:ad65536").ClearContents '清除表格資料 Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strDataSrcXlsPath As String
strDataSrcXlsPath = ThisWorkbook.FullName
'MsgBox "本活頁簿檔案所在之路徑為 " & ActiveWorkbook.Path
'Workbooks.Open Filename:=ActiveWorkbook.Path & "poem.xls"
'ThisWorkbook.FullName = 目前檔案的位置
'I) 以下建立數據庫連接

Dim filepath
filepath = "Data Source = " & ActiveWorkbook.Path & "\" & "股票資訊1.xlsm" & ";Extended Properties=Excel 12.0;"
'MsgBox aaa
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = filepath
.Open

cn.CursorLocation = adUseClient

End With

'II-1) 準備SQL
Dim strQuery As String
Dim strQueryWorksheet As String
Dim strStartlocation As String
Dim lngRowCounter As Long

strStartlocation = "p5"
strQueryWorksheet = "Query" '數據查詢頁面位置股票名稱股票代號月份營收最新一年營收前一年營收前二年營收前三年營收前四年

'執行SQL
strQuery = "SELECT 股票名稱 as 股票名稱1 ,股票代號 as 股票代號1,月份 as 月份1,營收最新一年 as 營收2022, 營收前一年 as 營收2021,營收前二年 as 營收2020,營收前三年 as 營收2019,營收前四年 as 營收2018,round((營收最新一年 -營收前一年)/營收前一年*100,2) as [近一年增幅%] FROM [營收$] where (股票名稱 = '" & Range("v2").Value & "') and (營收前一年 > 0) order by 股票名稱 asc, 月份 asc "
MsgBox strQuery
Set rs = cn.Execute(strQuery)

'III) 把數據抄至所需的位置

' MsgBox rs.Fields(lngColCounter).Name
If rs.Fields.Count > 0 Then
For lngColCounter = 0 To rs.Fields.Count - 1
Sheets("營收").Range(strStartlocation).Offset(0, lngColCounter) = rs.Fields(lngColCounter).Name
Next lngColCounter
'III-2 把數據庫資料列表拿出
Sheets("營收").Range(strStartlocation).Offset(1, 0).CopyFromRecordset rs
MsgBox "轉檔結果巳完成"
Else
MsgBox "轉檔結果無資料"
End If

'IV) 關掉/清理連接
cn.Close
'rs.Close
Set cn = Nothing
Set rs = Nothing

End Sub

*連線字串要稍作改變先找出excl檔的路徑(filepath變數,每個人放的位置都不會相同),才有辦法執行

*同一個工作表有兩個資料表,欄位名稱不能相同,否則會無法執行(如果在執行後先清除掉相同欄位名稱也可以)

* [營收$]:此指excel的"營收"工作表,查詢excl工作表後面都要加$,加上[ ]是sql規定當遇到數字或% 等特殊字在首尾

狀況二:使用join也可行,不管是同一工作表或是兩個不同工作表

這邊使用"營收" 及 "eps"工作表

excel程式碼:

Sub 營收vseps()
Sheets("營收").Range("p5:ac65536").ClearContents '清除表格資料 Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strDataSrcXlsPath As String
strDataSrcXlsPath = ThisWorkbook.FullName
'MsgBox "本活頁簿檔案所在之路徑為 " & ActiveWorkbook.Path
'Workbooks.Open Filename:=ActiveWorkbook.Path & "poem.xls"
'ThisWorkbook.FullName = 目前檔案的位置
'I) 以下建立數據庫連接

Dim filepath
filepath = "Data Source = " & ActiveWorkbook.Path & "\" & "股票資訊1.xlsm" & ";Extended Properties=Excel 12.0;"
'MsgBox aaa
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = filepath
.Open

cn.CursorLocation = adUseClient

End With

'II-1) 準備SQL
Dim strQuery As String
Dim strQueryWorksheet As String
Dim strStartlocation As String
Dim lngRowCounter As Long

strStartlocation = "p5"
strQueryWorksheet = "Query" '數據查詢頁面位置季度 EPS累計最新一年 EPS累計前一年 EPS累計前二年 EPS累計前三年 EPS累計前四年
'執行SQL
strQuery = "select a.股票名稱,a.股票代號,a.月份,a.營收最新一年 as 營收2022,a.營收前一年 as 營收2021,營收前二年 as 營收2020,營收前三年 as 營收2019,營收前四年 as 營收2018,round((營收最新一年 -營收前一年)/營收前一年*100,2) as [近一年增幅%]"
strQuery = strQuery & " ,b.EPS累計最新一年 as eps2022,b.EPS累計前一年 as eps2021,b.EPS累計前二年 as eps2020,b.EPS累計前三年 as eps20219,b.EPS累計前四年 as eps2018 from [營收$] as a left join [eps$] as b on a.股票代號 = b.股票代號 and val(a.月份) = val(b.季度)"
strQuery = strQuery & " where (營收前一年 > 0) order by a.股票代號 asc,a.月份 asc,round((營收最新一年 -營收前一年)/營收前一年*100,2) desc"

MsgBox strQuery
Set rs = cn.Execute(strQuery)
'III) 把數據抄至所需的位置

'IV) 關掉/清理連接
cn.Close
'rs.Close
Set cn = Nothing
Set rs = Nothing

End Sub

*[營收$] as a left join [eps$] as b,這兩個資料表以a b來取代,可以減少程式碼,這邊val(a.月份) = val(b.季度),因為月份有1~12月而季度只有1~4,所以esp季度資料只會出現在1~4(目前資料只到第三季),如果使用inner join便無法顯示營收的5~12月,這邊使用left join便能強制把左邊的營收全部顯示

*where (營收前一年 > 0) : 計算漲幅時分母不能為零否則便會出現錯誤

來個影片