沙拉油

http://blog.xuite.net/saladoil/excel/7379326

沙拉油

一個物件類別、一個模組
貼上程式碼之後得重新開啟檔案

1. '物件類別模組 QytCls 的程式碼
   
2. Public WithEvents qyt As QueryTable
   
3. Public qname As String
   
4. Private Sub qyt_AfterRefresh(ByVal Success As Boolean)
   
5.   MsgBox qname & " 更新結束！"
   
6. End Sub
   
7. '模組 Module1 的程式碼
   
8. Dim qyts() As New QytCls
   
9. Private Sub auto_open()
   
10.   Dim n As Integer
    
11.   Dim sh As Worksheet
    
12.   '統計本活頁簿共有幾個查詢物件！
    
13.   For Each sh In ThisWorkbook.Worksheets
    
14.     n = n + sh.QueryTables.Count
    
15.   Next
    
16.   ReDim qyts(1 To n) As New QytCls
    
17.   Dim i As Integer
    
18.   Dim qyt As QueryTable
    
19.   For Each sh In ThisWorkbook.Worksheets
    
20.     For Each qyt In sh.QueryTables
    
21.       Set qyts(i + 1).qyt = qyt
    
22.       qyts(i + 1).qname = sh.Name & " 的查詢 " & qyt.Name
    
23.       i = i + 1
    
24.     Next qyt
    
25.   Next sh
    
26. End Sub

複製代碼

附上上面程式碼所製作的範例！