資料處理 Excel & VBA (2)

基本介紹

教學目標

初步了解 Excel VBA 如何撰寫巨集程式,自動化整合資料庫操作處理之應用。

重點概念

首先微軟所推出的 Excel 軟體早在 1993 年就能夠透過 Visual Basic for Applications (VBA) 巨集自動化執行並產生各種客制化解決方案,當時就已經能夠在 Windows 和 Mac OS 跨平台上執行,然而至今雖已超過二十年,依然存在於 Office 未來規劃的藍圖中,主要應用在於自動化處理和與本機電腦的互動,並且支援 Excel 軟體所有的版本

雖然 Excel VBA 是老舊的技術,但其非常穩定且可靠,只可惜相關教學書籍非常少,所以我們一定會有疑問,主要考量巨集開發學習成本會不會很高、需求變更容不容易快速修改、開發時間會不會很長、開發完成之後有人會維護… 等開發的問題。首先 VBA 語言也就是 Visual Basic 語言,Visual Basic 語言則源自於 BASIC 語言,BASIC 語言全名為 Beginner’s All-purpose Symbolic Instruction Code,意指初學者的全方位符號指令程式碼是一種直譯式的程式語言,在完成編寫後不須經由編譯及連結等手續即可執行,適用於初學者的人機互動式語言,早在 1964 年推出,更在 1984 年成了 ISO 標準之資料處理的程式語言,至今已超過五十年。因此我們大致可以推論 VBA 理應也是適用於初學者的人機互動式語言,所以學習成本不會很高,並且因為是直譯式所以彈性大開發時間短,至於完成之後的維護問題則取決於團隊的程式設計風格 (Coding Style),若不在意中文命名有時也是不錯的解決方案。

接著我們要可以透過 Excel VBA 如何撰寫巨集程式,藉由 ADO 物件的方法建立 ODBC 連線,執行 SQL 查詢語言,接著關閉 ODBC 連線,自動化整合資料庫操作處理之應用,至於何謂 ADO 物件,全名為 ActiveX Data Objects,也就是 ActiveX 資料物件主要為微軟在 1996 年所發佈一種標準的應用程式介面 (Application Interface,API) 以實作存取資料庫管理系統中的資料,以及何謂 ODBC 連線,全名為 Open Database Connectivity,也就是開放資料庫連線主要為微軟在 1992 年所發佈一種標準的應用程式介面以實作存取資料庫管理系統,支援 SQL 查詢語言,至今已超過二十年,也因此目前現行的資料庫系統大部份皆有支援 ODBC 連線,例如: Teradata 資料庫。

相關程式碼

1
2
3
4
5
6
7
8
9
10
11
12
13
Sub 範例()
Dim 連線 As ADODB.Connection
Set 連線 = New ADODB.Connection
連線.Open "ODBC 連線名稱的資料來源名稱", "使用者帳號", "使用者密碼"
If 連線.State = adStateOpen Then
MsgBox "連線成功開啟 ^o^"
Else
MsgBox "連線失敗 >_<"
End If
Set 回傳結果 = 連線.Execute("SELECT * FROM PARTY")
MsgBox 回傳結果("CUSTOMER_ID") & " " & 回傳結果("CUSTOMER_NAME")
連線.Close
End Sub

最後 Excel 最新技術應用中主要能夠透過 Microsoft Power Query for Excel 強化自助商業智慧 (Business Intelligence,BI),在探索、合併及精簡各種來源的資料方面,包括關聯式、結構化、Hadoop、… 等,提供直覺且一致的操作體驗,同樣的相關應用也能透過 Excel VBA 進行自動化整合資料庫操作處理,例如: ETL 自動化處理,此外 Excel 本身就是個強大的資料處理操作介面,我們自動化將原始資料進行整合,接著只要搭配 Excel 的功能理應就能夠讓使用者自助完成客制化的報表應用。

相關資源