¨F©Ôªo

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

¨F©Ôªo

¤@­Óª«¥óÃþ§O¡B¤@­Ó¼Ò²Õ
¶K¤Wµ{¦¡½X¤§«á±o­«·s¶}±ÒÀÉ®×

1. 'ª«¥óÃþ§O¼Ò²Õ QytCls ªºµ{¦¡½X
   
2. Public WithEvents qyt As QueryTable
   
3. Public qname As String
   
4. Private Sub qyt_AfterRefresh(ByVal Success As Boolean)
   
5.   MsgBox qname & " §ó·sµ²§ô¡I"
   
6. End Sub
   
7. '¼Ò²Õ Module1 ªºµ{¦¡½X
   
8. Dim qyts() As New QytCls
   
9. Private Sub auto_open()
   
10.   Dim n As Integer
    
11.   Dim sh As Worksheet
    
12.   '²Î­p¥»¬¡­¶Ã¯¦@¦³´X­Ó¬d¸ßª«¥ó¡I
    
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 & " ªº¬d¸ß " & qyt.Name
    
23.       i = i + 1
    
24.     Next qyt
    
25.   Next sh
    
26. End Sub

½Æ»s¥N½X

ªþ¤W¤W­±µ{¦¡½X©Ò»s§@ªº½d¨Ò¡I