VBA

資料處理 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 的功能理應就能夠讓使用者自助完成客制化的報表應用。

相關資源

資料處理 Excel & VBA (1)

基本介紹

IT 專業人員要如何與一般使用者有效進行溝通呢? 建議採用 Excel 整理之後再進行溝通,IT 專業人員藉由 Excel 可以清楚的表達所需要的欄位資訊,交付給一般使用者進行處理之後,接著上傳至對應系統匯入資料。此外一般使用者藉由 Excel 可以清楚的表達所需要的欄位資訊,交付給 IT 專業人員透過函式和 VBA 進行處理之後,再寄回給一般使用者理應就能自動化進行資料處理。更重要的是 Excel 操作介面對於一般使用者並不陌生,且對於 IT 專業人員透過函式和 VBA 又有很大的彈性自動化處理,更重要的是目前企業普遍使用 Office 2007 以上的版本,透過函式和 VBA 就能解決問題了。

重點概念

在銀行進行資料倉儲的工作已達半年,已經有些經驗在不涉及商業機密的情況下進行技術分享。

VBA 主要是以執行「巨集」的方式運作,能完成的事情非常多元,例如:

  1. 連接資料庫進行資料表查詢。
  2. 寄送 E-mail 通知信件。
  3. 進行資料檔案的處理。

接著搭配函式 (SUM()、AVERAGE()、TODAY()、TEXT()、INDEX()、MATCH()、IF() … ) 即可進行資料處理自動化、資料分析和商業建模等進階的應用。

最後結果的呈現非常多元化,IT 專業人員可以很簡易的客制樣式和產生圖表,讓一般使用者有更佳的體驗操作。

相關資源