iT邦幫忙

DAY 28
3

Excel VBA 的眉眉角角系列 第 28

Excel VBA 的眉眉角角Day28: SQL運用

Excel可以透過VBA與SQL語言來進行資料運算,但使用上不如MS Access來的方便,雖然如此,對於熟悉SQL語句的人而言,這真是如獲至寶,這寶貝叫做ADO,只要安裝好Office,它就一直存在,而我們只要了解它、運用它,就等於有了這個寶貝!

以下是我個人製作的一個Function,主要是使用SQL語句來操作Excel檔案,資料來源可以是外部檔案,或同檔案,透過此程式,可以指定產生資料後要存放的工作表,儲存前是否先清除工作表內容,存到該工作表的起始於哪個欄位,資料產生後,是否自動調整欄位大小。以此方式來操作ADO,可減輕不少程式撰寫工作,只須把參數帶入,即可產生所需的統計資料到指定的位置。以下讓我們來看程式內容:

Public Function SQL(strCommandText As String, Optional strXLSFileName As String, Optional strSheetName As String, Optional bnGotoSheet As Boolean = True, Optional bnDelSht As Boolean = True, Optional iRow As Integer = 1, Optional iCol As Integer = 1, Optional bnNoField As Boolean = False, Optional bnNoAutoFit As Boolean = False)
    
    '參考資料:
    '如何從 Visual Basic 或 VBA 搭配使用 ADO 與 Excel 資料
    ' http://support2.microsoft.com/kb/257819/zh-tw
    
    '變數介紹:
    
    '必要:
    'strCommandText:  SQL語句
    
    '選擇性
    'strXLSFileName: Excel檔案完整路徑,預設為執行的Excel檔案
    'strSheetName: 存放結果的工作表名稱,未指定則程式會建立一個工作表存放結果
    'bnGotoSheet: 是否跳到存放結果的工作表?預設為是
    'bnDelSht: 若指定存放工作表,是否清除原工作表內容,預設清除
    'iRow : 存放列,預設1
    'iCol : 存放欄,預設1
    'bnNoField : 不包含表頭,預設含表頭
    'bnNoAutoFit : 不自動調整儲存格大小,預設自動調整大小
    '
    
    Dim strConnectString As String
    Dim vADODBConnection As Variant
    Dim vADODBRecordset As Variant
    Dim objWrkSht As Object

    Application.ScreenUpdating = False
    
    If strXLSFileName = "" Then
        strXLSFileName = ActiveWorkbook.FullName
    End If
    
    strConnectString = "Driver={Microsoft Excel Driver (*.xls)}; " & _
                                        "DBQ=" & strXLSFileName & ";" & _
                                        "ReadOnly=True"
    
    Set vADODBConnection = CreateObject("ADODB.Connection")
    Set vADODBRecordset = CreateObject("ADODB.Recordset")
    
    vADODBConnection.Open strConnectString
    vADODBRecordset.Open strCommandText, vADODBConnection, 3, 1, 1
    
    If strSheetName = "" Then
        Sheets.Add
        Set objWrkSht = ActiveSheet
    Else
       If bnGotoSheet = True Then Sheets(strSheetName).Select
        Set objWrkSht = Sheets(strSheetName)
        If bnDelSht = True Then
            objWrkSht.Cells.Clear
        End If
    End If
    
    Dim f As Integer
    'r = 1
    If iRow = 0 Then iRow = 1
    If iCol = 0 Then iCol = 1
    
    If bnNoField <> True Then
        For f = 0 To vADODBRecordset.Fields.Count - 1
            objWrkSht.Cells(iRow, f + iCol).Value = vADODBRecordset.Fields(f).Name
        Next
    End If
    
    While Not vADODBRecordset.EOF
        iRow = iRow + 1
        For f = 0 To vADODBRecordset.Fields.Count - 1
            objWrkSht.Cells(iRow, f + iCol).Value = vADODBRecordset.Fields(f).Value
        Next
        vADODBRecordset.movenext
    Wend
        
    vADODBConnection.Close
    
    If bnNoAutoFit <> True Then
        'objWrkSht.Activate
        objWrkSht.Cells.Rows.AutoFit
        objWrkSht.Cells.Columns.AutoFit
        'objWrkSht.Cells(1, 1).Select
    End If
    
    Application.ScreenUpdating = True
    
End Function

以下兩個範例,分別有外部檔案與同檔案進行不同的SQL語句處理:

範例一:由外部檔案彙整資料至Day28工作表,統計血型數量

Sub Day28_1()
Dim strXLSFileName As String
Dim strCommandText As String

strXLSFileName = "\\ntsvr\Public\mis\SQL_TEST.xls"
    strCommandText = "SELECT A.血型 , COUNT(A.血型) AS 總數 " & _
                     "FROM [Data$] AS A " & _
                     "GROUP BY A.血型 "
                     
Call SQL(strCommandText, strXLSFileName, "Day28", True, 1, 1, False, False)

End Sub

外部檔案資料如下:

產生出來的資料如下:

範例二:由本地檔案Day2工作表彙整資料至Day28工作表,統計姓氏數量並遞減排序

Sub Day28_2()
    Dim strXLSFileName As String
    Dim strCommandText As String

    strCommandText = "SELECT LEFT((A.姓名),1) AS 姓氏 , COUNT(LEFT((A.姓名),1)) AS 總數 " & _
                     "FROM [Day2$] AS A " & _
                     "GROUP BY LEFT((A.姓名),1) " & _
                     "ORDER BY COUNT(LEFT((A.姓名),1)) DESC;"
                     
    Call SQL(strCommandText, strXLSFileName, "Day28", True, True, 1, 1, False, False)

End Sub

外部檔案資料如下:

資料內容一樣,只是放在同一檔案的Day2資料表中

產生出來的資料如下:

如此簡單幾步,即可進行複雜的統計運算。

希望各位能有如獲至寶的感覺,如果沒有,也沒關係,當有需求的時候,才有此感覺的。以前的我,就是不懂SQL、沒學過資料庫,後來慢慢接觸後,才發現之前在Excel上做了很多傻事,用SQL簡單幾句就可以處理完的,Excel可能要花不少時間用公式拼湊出來,拼不出來還要用VBA寫程式處理。

學會SQL後,其實可以跟Excel本身的內建功能有所互補,畢竟Excel本身有些功能有較多彈性,而SQL的話,則是歷史悠久,語法學會後,可以在不同環境中使用,只是各家資料庫還是有些語法的差異,不過大部分語法是箱通的。

我個人是由Excel的VBA學習後才開始接觸到SQL,然後在由SQL踏入Access的學習,最後才接觸到SQL Server,若您最早已經接觸過資料庫,那在Excel中使用SQL就更駕輕就熟了!以上分享,希望對各位有幫助!


上一篇
Excel VBA 的眉眉角角Day27: 匯入外部圖片、縮圖放大以及大頭照的快速裁切方法
下一篇
Excel VBA 的眉眉角角Day29:如何抓取網路上的資料?以issuu.com為例
系列文
Excel VBA 的眉眉角角30

1 則留言

0
paicheng0111
iT邦高手 1 級 ‧ 2020-03-27 14:04:18

隨著EXCEL 2007的推出,strConnectString也要修改。

可以參考https://www.connectionstrings.com/excel-2007-odbc/

Andy Chiu iT邦研究生 3 級 ‧ 2020-03-27 15:25:23 檢舉

了解了,感謝您提供的資訊!

我要留言

立即登入留言