用ADO,實現EXCEL多個工作表數據的匯總

2020-12-17 VBA語言專家

大家好，今日我們繼續講解VBA資料庫解決方案，今日講解第35講：利用ADO,實現EXCEL多個工作表數據的匯總。在前幾講中，我們講了用ADO連接EXCEL實現一些便捷操作的方法，其實，這些操作中是把EXCEL作為一種特殊的資料庫來對應的，我們在實際工作中面臨的實際情況多種多樣，要實現特殊的操作有時要組合利用一些基本的方法。

我們今日講解的是利用ADO,實現EXCEL多個工作表數據的匯總，模擬的場景是某個工作薄中有若干個工作表，我們要把這些工作表的數據一次提取出來，放在當前的工作表內。如下面的截圖；為文件「16年.xlsx」 的數據記錄，一共有4個工作表，每個工作表中的記錄各不相同：

為了實現各個工作表中的數據都匯總到同一個工作表中，我們看下面的代碼：

Sub mynzexcels_4()

'第35講，利用ADO,實現EXCEL多個工作表數據的匯總

Dim cnADO, rsADO As Object

Dim strPath, strTable, strSQL As String

Dim SH As Worksheet

ThisWorkbook.ActiveSheet.Cells.Clear

Set cnADO = CreateObject("ADODB.Connection")

'建立連接

strPath = ThisWorkbook.Path & "\" & "16年.xlsx"

Workbooks.Open strPath

cnADO.Open "provider=Microsoft.ACE.OLEDB.12.0;extended properties='excel 8.0;hdr=no;imex=1';data source=" & strPath

For Each SH In Worksheets

strTable = "[" & SH.Name & "$]"

strSQL = "select * from " & strTable

ThisWorkbook.ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset cnADO.Execute(strSQL)

Next

Workbooks("16年").Close

cnADO.Close

Set cnADO = Nothing

End Sub

代碼截圖：

代碼講解：

1 Workbooks.Open strPath 這裡要打開這個文件，以便提取每個工作表的數據，如果不打開時下面的FOR EACH會報錯。

2 strTable = "[" & SH.Name & "$]"

strSQL = "select * from " & strTable

上述代碼是嵌套在for each 循環中，先提取每個工作表的名字，然後建立SQL可執行的語句。

3 ThisWorkbook.ActiveSheet.Range("a65536").End(xlUp).Offset(1, 0).CopyFromRecordset cnADO.Execute(strSQL)

將數據提取處來放到當前的工作表中，這處是執行了一個SQL語句。

4 Workbooks("16年").Close 關閉這個文件。這處可以用Workbooks("16年").Close（false）,較好。

下面看我們代碼的執行過程：

點擊」多工作表的數據匯總」：

這樣就實現了我們的目的：多個工作表的數據匯總。

今日內容回向：

1 如何實現多個工作表的匯總？

2 為啥要先打開這個文件呢？

相關焦點

  • 利用ADO,實現同一文件夾下多個EXCEL工作表的數據匯總
    大家好，今天繼續講解《VBA資料庫解決方案》，今日講解的是第37講，利用ADO，實現同一文件夾下多個EXCEL工作表的數據匯總。最近的內容實用性比較強，如今日的內容，只把需要匯總的EXCEL文件放在同一個文件夾下，而且格式一致，那麼利用ADO匯總這幾個文件的數據是非常快的,這講的內容和第32講的內容是類似的，不過第32講的內容是要事先知道文件的名稱，然後建立一個數組來分別對應每個文件，通過循環來實現從每個文件中提取數據的目的，本講的內容是事先不知道每個文件的名稱。
  • excel匯總多個工作表數據的神器——合併計算
    在excel中，經常會遇到多個表格的數據需要匯總，但是有時候每個表格的順序、項目不一定完全相同，比如下圖中，動圖一是五個不完全相同的工作表，如何將五個工作表的數據匯總到一個工作表中（圖二所示），並對這些數據進行求和或者其他運算呢？這裡就為大家介紹一下合併計算的功能。
  • Excel表格用Vlookup、Indirect、Match函數匯總多個工作表的數據
    數據的匯總是我們日常工作中經常會遇到的問題。如果工作表的數量較少可以通過複製粘貼匯總，但是工作表很多時在用複製粘貼效率就非常低。今天就為朋友們分享一種單純使用函數、不包含任何VBA代碼快速匯總多個工作表數據的方法。
  • Excel如何跨工作表動態引用數據(合併匯總必備)
    在進行一些合併、匯總工作中，經常碰到的一個問題是有一堆格式類似的不同工作表，希望能有一張匯總表顯示其中的一些數據，又不想一個個手動link。而想用公式拉時又會發現工作表名無法作為變量隨之移動。單元格區域」）*indirect也可以跨工作簿，但一定要工作簿維持打開才可以保持引用有效，沒啥用就不介紹了。跨工作簿大量&維持連結的引用至今沒有很好的解決方法。觀察規律，發現括號裡頭尾各有一個雙引號，如果這時非動態引用一個名為C.現金的工作表的B2單元格，公式如下：=INDIRECT("' C.現金'!
  • Excel應用技巧:多個工作表合併
    工作中我們經常會遇到多表合併的問題，今天xyz就和大家聊聊關於工作表合併的問題。
  • Excel多工作表數據匯總到一個工作表,其實很簡單
    我們有時候需要將在多個工作表的數據到同一個工作表當中，首先我們需要確認的是這些數據的格式是否具有一致性，如果所有的數據格式都是一樣的，那麼這個問題就非常好處理了。今天和大家分享的是一鍵匯總各分表數據到總表。
  • 使用INDIRECT函數快速匯總多個工作表,數據匯總竟能如此簡單
    Hello.大家好，今天跟大家分享下我們如何使用INDIRECT函數快速匯總同一工作薄下的多個工作表，使用這種方法匯總工作表雖然具有一定的局限性，但是我們只需通過簡單的設置就能達到相應的要求，如果你在工作中遇到類似的問題，就可以使用這種方法快速匯總數據
  • Excel多個工作表數據匯總,你還在複製粘貼嗎?
    有一天，院長讓學生會主席把學院裡成員的個人簡歷都整理匯總成花名冊，儘快上交。C2")】，對應的就是工作表【院長】的【姓名】單元格。所有列數據依次寫好後，得到下表。　　　　3.快速填充　　5　　首先，填充輔助列O列，提取所有工作表的名稱。點擊O3單元格右下角的「+」號往下拖動。　　然後，填充數據列B至N列，填充所有數據。選擇第3行B至N列，點擊N3單元格右下角的「+」號往下拖動。
  • 如何快速拆分excel工作表?用數據透視表即可快速搞定
    Hello.大家好，今天跟大家分享下我們如何將1個工作表，按照某1個欄位拆分為多個工作表，工作中我們也會遇到類似的問題，就是將匯總表按照某個類別拆分為多個工作表，大部分都是一個一個的粘貼複製非常的麻煩，今天就跟大家如何利用數據透視快速的完成表格拆分
  • excel數據處理：如何快速進行工作表拆分
    各位小夥伴有沒有遇到過這樣的問題：當我們把所有的信息匯總在一張表裡後，又需要將這張大表按某一條件再拆分成多個工作表。那怎麼才能實現呢？可能最笨的方法就是在原工作表篩選數據然後複製粘貼到新工作表，不過這種方法不適合數據多的案例，並且新工作表也需要一一重命名，顯得繁瑣。今天就給大家介紹兩種快捷實用的工作表拆分方法。如圖，現在要把這個工作表的內容按城市拆分成多個工作表。
  • 將多個工作表的數據合併到一個工作表中
    歡迎來到我的公號，excel函數解析。在工作中，我們的數據可能有很多，比如有很多個部門的數據，要分別放在不同的工作表中。但在匯總分析的時候，需要將所有部門的數據合併起來，統一處理。這時，將多表數據合併到一個工作表中，可能就是很多小夥伴頭疼的問題。今天，就來分享一段簡短的代碼來解決這個問題。如下圖所示，有5個班級的數據，分別放在5個不同的工作表中，它們的數據結構一樣。
  • 小白也能快速掌握的多個Excel工作簿和工作表的匯總的方法
    前幾節，我們分享了多個Excel工作簿的匯總方法和一個Excel工作簿中有多個Sheet工作表的匯總方法。這一節給大家分享第三個應用場景的匯總方法：也就是前兩個應用場景的混合場景，也是最複雜的應用場景：多個工作簿中的多個Sheet工作表的匯總。例如：10個工作簿裡面有10個工作表sheet，要快速匯總這10個工作簿中的10個Sheet工作表內容。
  • 使用Python pandas讀取多個Excel工作表
    ，pandas 本文將嘗試使用Python pandas讀取來自同一文件的多個Excel工作表。我們可以通過兩種方式來實現這一點：使用pd.read_excel()方法，並使用可選的參數sheet_name；另一種方法是創建一個pd.ExcelFile對象，然後解析該對象中的數據。 註：本文示例文檔可在知識星球完美Excel社群中下載。
  • 小白也能快速掌握Excel中多個Sheet工作表的匯總方法
    上一節，給大家分享了第一個應用場景：小白也能快速掌握的多個Excel工作簿匯總的方法這一節給大家分享第二個應用場景的匯總方法：一個Excel工作簿中多個Sheet工作表的匯總。例如：一個工作簿裡面有12個工作表sheet，要快速匯總這12個sheet的工作表內容。
  • Excel VBA:匯總多個工作簿每個工作表名稱包含指定關鍵詞的數據
    今天再分享下匯總指定文件夾下每個工作簿中工作表名稱包含某個指定關鍵詞的小代碼（當不指定關鍵詞時，則默認匯總所有工作表數據）。舉個慄子。假設有一文件夾，內有十幾個工作簿，每個工作簿又各有多個不等數量的工作表，現在我們只想匯總每個工作簿中工作表名稱包含「看見星光」的數據，那就可以使用我們今天分享的小代碼了。如果是想把所有工作表的數據一股腦全部匯總呢？
  • excel數據處理技巧:分類匯總讓你的數據井然有序
    平時我們做表的時候，難免會遇到數據繁多、雜亂的情況，今天要給大家分享的是一個excel中整理數據的神功能——分類匯總，它能3秒解決數據「髒、亂、差」的問題，讓你的數據煥然一新，趕緊來看看吧！當工作表中的數據比較繁雜時，可以在對關鍵字進行排序後，通過分類匯總的方法對數據進行分析，如統計某部門的員工數量、某業務員的業績情況等，對於經常用Excel處理數據的人來說，分類匯總是一個必不可少的技巧，它可以讓很多工作事半功倍。學習更多技巧，請收藏部落窩教育excel圖文教程。
  • Excel合併多個工作表數據,實現同步更新!
    在工作中，我們經常需要將多個工作表中的數據合併到一個表格中，有時候可能會藉助一些函數公式來幫我們實現，對於那些不怎麼會函數的小白來說
  • Excel vba工作表事件【實現快速自動隱藏工作表】
    前兩天有個朋友諮詢我，在下面的表格中，C列是每個表格的工作表名稱的匯總後來編寫了如下代碼，其中我把字符「X」優化了一下，利用數據有效性，每個工作表名稱後面為隱藏和不隱藏兩個選擇：就可以實現，隱藏哪一個工作表，選中哪一個工作表後面的狀態後，工作表自動隱藏或者不隱藏。
  • 欄位順序不同的多工作表的數據匯總!
    歡迎來到我的公號，excel函數解析。之前分享了欄位順序相同的多工作表的數據匯總，今天來分享下欄位順序不同的多工作表數據匯總的方法。案例分享如下圖所示，數據源有3個工作表，分別是2018年，2019年和2020年。每個工作表記錄的是當年各銷售員在各城市的銷售數據。現在每個工作表的欄位順序是不同的。
  • excel多表操作：如何快速完成多工作表匯總求和
    ，簡單到不好意思相信：數據透視表嚮導合併計算多個數據區域就是分分鐘的事兒。多工作表數據匯總是職場白領經常遇到的問題，如同一個工作簿（文件）中的多個分公司數據表要匯總、多個月份數據表要匯總。有人用VBA來完成匯總，有人使用函數公式來匯總，也有人通過手工計算完成匯總。