從Excel文件或Access資料庫中獲取指定列數據

最新 05-24

最近部門有一個海外客戶，需要我們提供英文的結算單，由於國內的櫃檯系統只有提供中文版的結算單，所以只能通過櫃檯導出所需要的基礎數據，然後轉換為英文版的結算單。因為基礎數據是csv結尾的文件，所以我就想能夠通過把csv文件導入到Excel或Access中（也可以導入到SQL Server資料庫中），然後再獲取相應的列數據，對其進行中英文轉換。

具體轉換細節就不做介紹了，今天主要為大家介紹如何從Excel文件或Access資料庫中獲取指定列數據。其實微軟提供2種引擎可供在不打開Excel和Access的情況下獲取數據。分別是Microsoft.Jet.OLEDB.4.0（以下簡稱Jet 引擎）和Microsoft.ACE.OLEDB.12.0（以下簡稱ACE 引擎）。

在測試過程中，我發現以上兩個引擎都可以用來訪問Excel擴展名為*.xlsm文件。但Jet引擎只能用於訪問擴展名為.mdb的Access文件，ACE引擎既可以訪擴展名為.mdb的Access文件，也可以訪問擴展名為.accdb的Access文件。

為了更加直觀的演示，我製作了一個虛擬數據，數據內容如下圖所示：

通過上圖可以看出，Sheet1工作表中有5列11行數據，如果製作的報表中只需要其中的姓名、學號、身份證以及出生日期。那該如何操作呢？其中姓名、學號和身份證3列可以直接從上圖中獲取，出生日期列需要經過截取而來。

從Excel中獲取數據，可以通過以下代碼實現，代碼如下：

連接Excel

Subtest1()

Dim conn As New Connection

Set rs = CreateObject("adodb.recordset")

conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "" & ThisWorkbook.Name & ";Extended Properties= Excel 12.0 Xml;HDR=YES "

conn.Open

Sql = "select 姓名, 學號, 身份證, mid(身份證,7,8) as 出生日期 from[Sheet1$]"

填寫新表的列名稱

rs.Open Sql, conn, 1, 3

For i = 0 To rs.Fields.Count - 1

Worksheets("Sheet2").Cells(1, i + 1) = rs.Fields(i).Name

把查詢的結果集放入到A2單元格區域

Worksheets("Sheet2").Range("A1").Offset(1, 0).CopyFromRecordset conn.Execute(Sql)

rs.Close

conn.Close

Set rs = Nothing

Set conn = Nothing

End Sub

核心的代碼就是使用Jet和ACE引擎，上段代碼的作用是從當前打開的工作簿Sheet1工作表中獲取姓名、學號、身份證以及出生日期信息。其中出生日期信息通過對身份證列進行mid截取，再把得到的列通過as關鍵詞變更列名為出生日期。

上面的代碼放入模塊後，按下F5或點擊綠色圖標會提示如下錯誤（如果正常運行請忽略，第一次使用會出現）：

出現上圖的錯誤是由於Excel VBE中未引用相應的Library，可以通過【工具】【引用】，勾選類似【Microsoft ActiveX Data Objects *.*】並點擊確定。我這裡是Microsoft ActiveX Data Objects 6.1，根據各位小夥伴安裝的Office版本的不同，版本號也會不同。