excelvbaadosql入門教程004：sql中的excel表

1、Excel VBA ADO SQL 入門教程 004： SQL 中的 Excel 表1. 上期我們聊了 SQL 常用查詢語句中的字段查詢， 其簡 化版語法如下： SELECT 字段名 FROM 表名當(dāng)時(shí)我們說， FROM 關(guān)鍵詞指明了要獲取字段信息的表的名稱。 倘若數(shù)據(jù) 源是 Excel 表格， 則需要在表名后增加美元符號 $，并用中括 號包起來，例如Sheet1$事實(shí)上，上述例子是SQL InExcel 對工作表引用最簡單的一種情況，也就是整表引用； 此外還有單元格區(qū)域引用、跨工作簿引用表等。所以我們今 天就再來聊一下 SQL 語句中的 Excel 表。2. 區(qū)域成表 Excel 工作表和數(shù)

2、據(jù)庫的數(shù)據(jù)表有很多不同之處， 最顯著的地方在于，數(shù)據(jù)庫的數(shù)據(jù)表是由行列構(gòu)成的，而 Excel 工作表則是由一個(gè)又一個(gè)單元格構(gòu)成的，且這些單元 格擁有獨(dú)特的地址表述方法（ A1 和 R1C1 ），它們還可以構(gòu) 成數(shù)據(jù)相連的單元格區(qū)域，例如 A2:H8 。于是問題來了，如 果我們只需要使用 SQL 語言計(jì)算某張 Excel 工作表的部分區(qū) 域該怎么表述呢？這種問題是很常見的。比如，很多人的 Excel 標(biāo)題行并不是處于表格的第一行，而是第 2 行如 下圖：此時(shí)，我們希望 SQL 可以使用 A2:F 列的單元格區(qū)域 作為表，而不是整個(gè) Excel 工作表，這樣我們更容易使用字 段名處理數(shù)據(jù)對吧？（我

3、轉(zhuǎn)頭看看書柜，書柜不說 話）再比如，一張表里存在兩個(gè)或更多個(gè)“表”這句話什么 意思呢？見下圖。 圖中所示的表格中， 既存在一份 “教師表”， 又存在一份“學(xué)生表”；如果我們只希望 SQL 引用計(jì)算 A2:D8 的教師表數(shù)據(jù)呃，請把刀放下，君子動手不動刀好伐， Excel中的 SQL 其實(shí)是支持將工作表的單元格區(qū)域作為 “表”使用的。 上圖所示的問題，SQL可以寫成：SELECT姓名，學(xué)科FROM 數(shù)據(jù)表 $A2:D8 查詢結(jié)果如下：而第 1 種情況，我們知道數(shù) 據(jù)開始于 A2 單元格，但不知道結(jié)束于 F 列的哪個(gè)單元格， SQL 可以寫成： SELECT 姓名 ,愛好 FROM 學(xué)生表 $A2

4、:F 另外，如果我們需要 SQL 引用計(jì)算表格 D:G 整列的數(shù)據(jù)： SELECT * FROM 學(xué)生表$D:G總結(jié)以上幾種 Excel工作表 區(qū)域的表述方式，也就是，工作表名稱 美金符號 $ 相對引 用狀態(tài)下的單元格地址，最后使用中括號包起來。就醬紫。 本節(jié)小貼士： 學(xué)生表 $A2:F ，我們說該語句可以引用從 A2 至 F 列最后存在數(shù)據(jù)的單元格區(qū)域，但這是有一個(gè)限制條件 的，即非自連接狀態(tài)。 所謂自連接是指 SQL 應(yīng)用于鏈接自身 的工作簿。自鏈接狀態(tài)下， A2:F 的表達(dá)方式最多是 A2:F65536 行；倘若此時(shí)需要的引用行超過 65536 行，請使用整表模式。3. 跨工作簿的表一個(gè)

5、眾所周知的問題是，Excel 函數(shù)在處理跨工作薄數(shù)據(jù)時(shí)很是疲態(tài)，除了個(gè)別幾個(gè)查找引用類函數(shù)（例 如 VLOOKUP ），絕大部分函數(shù)都需要打開相關(guān)工作簿后才可以使用計(jì)算。是的， VLOOKUP 函數(shù)并不需要打開相關(guān)工 作簿也可以跨工作薄使用， 而且在 VLOOKUP 公式書寫完成 后，即便你把它所引用的工作簿給刪了，也不妨礙它計(jì)算， 這是因?yàn)樗呀?jīng)把相關(guān)數(shù)據(jù)緩存到了公式所在的工作簿中， 不過VLOOKUP這種模式并不支持函數(shù)嵌套使用打個(gè) 響指，關(guān)于這一點(diǎn)，如果你感興趣，我們改天單獨(dú)聊一下。 說回 SQL 我覺得我今天精神老是不集中， 碼個(gè)字也老是 跑題，且慢的像蝸牛，也許有人在想我，也許有人在

6、罵我， 不管了，我先去刷會微博，哦，對了，星光俺的微博是 EXCELers，沒事記得關(guān)注下說回 SQL我們之前分享的 SQL 語句都是處理當(dāng)前工作簿的表格， 如果我 們所需要處理的數(shù)據(jù)位于其它工作簿時(shí)， SQL 該怎么表述 呢？例如，獲取位于計(jì)算機(jī) D 盤的“ EH 小學(xué)”文件夾下的 “學(xué)生表 .xlsx ”工作簿中的“成績表”的所有數(shù)據(jù)一口 氣讀完這話的，送你一臉壞笑 暴擊點(diǎn)贊。如果是 OLE DB 法（該方法參考第 1章）使用SQL，語句如下：SELECT * FROM D:EH 小學(xué) 學(xué)生表 .xlsx. 成績表 $FROM 后的指定 表字符串有兩個(gè)部分構(gòu)成，第一個(gè)中括號內(nèi)是指定工作簿的

7、 存放路徑 帶后綴的完整工作簿名稱，后一個(gè)中括號內(nèi)是工 作表名稱，兩個(gè)中括號之間使用英文句號（.）相連。如果是通過 VBA ADO 使用 SQL 語句嘟嘟敲書柜前方預(yù)警： VBA 基礎(chǔ)差的童鞋請自行跳過以下內(nèi)容相比于 OLE DB法，VBA ADO的方法要靈活的多，它可以使用ADO直接創(chuàng)建并打開和指定工作簿的鏈接， 因此 SQL 語句便無需再指定工作簿完整名稱等。代碼參考如下： Sub ADO_Sql() 適用于除 2003 版以外的高版本 Excel Dim cnn As Object, rst As Object Dim Mypath As String, Str_cnn As Strin

8、g, Sql As String Dim i As Long Set cnn = CreateObject(adodb.connection) Mypath = D:EH 小學(xué) 學(xué) 生表.xlsx指定工作簿 Str_cnn = Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & Mypath cnn.Open Str_cnn 創(chuàng)建并打開到指定工作簿的鏈接Sql =SELECT * FROM 成績表 $ Sql 語句，查詢成績表的所有數(shù) 據(jù) Set rst = cnn.Execut

9、e(Sql) 執(zhí)行 SQL Cells.ClearContents For i = 0 To rst.Fields.Count - 1 Cells(1, i 1) = rst.Fields(i).Name Next Range(a2).CopyFromRecordset rst cnn.Close Set cnn = NothingEnd Sub 但更多的情況是， ADO 創(chuàng)建的鏈接是一個(gè) 工作簿，需要獲取的數(shù)據(jù)在另一個(gè)或多個(gè)工作簿，例如跨表 格數(shù)據(jù)查詢統(tǒng)計(jì)。 此時(shí)通常使用的代碼如下： Sub ADO_Sql2() 適用于除 2003 版以外的高版本 Excel Dim cnn AsSet c

10、nn =Object, rst As Object Dim Mypath As String, Str_cnn AsString, Sql As String Dim i As LongCreateObject(adodb.connection) Mypath = ThisWorkbook.FullName 代碼所在工作簿的完整名稱 Str_cnn = Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source= & Mypath cnn.Open Str_cnn 創(chuàng)建到代碼所在工作簿的鏈接S

11、ql =SELECT * FROM Excel 12.0;DATABASE=D:EH 小學(xué) 學(xué)生 表.xlsm.成績表$Sql語句，查詢成績表的所有數(shù)據(jù)Set rst = cnn.Execute(Sql) 執(zhí)行 SQLCells.ClearContentsFor i = 0 To rst.Fields.Count - 1Cells(1, i 1) =rst.Fields(i).Name Next Range(a2).CopyFromRecordset rst cnn.CloseSet cnn= NothingEnd Sub (代碼詳細(xì)注釋及適用于 0316 版本 Excel 的 VBA 代碼模版請參考第 2 章，回復(fù)關(guān)鍵詞 SQL 可以獲取 過往系列文章)代碼中的 SQL 語句是：SELECT * FROM Excel 12.0;DATABASE=D:EH 小學(xué) 學(xué)生 表.xlsx.成績表$FROM指定表的字符串有兩部分組成。第 一個(gè)中括號中， Excel 12.0 是目標(biāo)工作簿的版本號，第 2 章 時(shí)我們講過， Excel 12.0 適用于除了 2003 以外的所有 Excel 版本。 DATABASE 指定的是數(shù)據(jù)源工作簿的路徑和名稱。 第 2 個(gè)中括號內(nèi)是工作表名