AW: Ergänzung zur Fragestellung
25.04.2006 10:35:31
EtoPHG
Hallo Martin,
Probiers mal so:
Sub QueriesAuslesen()
Dim qrt As QueryTable
Dim wsh As Worksheet
Dim bAddList As Boolean
Dim iQueryCnt As Integer
iQueryCnt = 0
bAddList = True
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name = "QueryList" Then
bAddList = False
Exit For
End If
Next
If bAddList Then
ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "QueryList"
End If
Sheets("QueryList").Cells(1, 1).Value = "BlattName"
Sheets("QueryList").Cells(1, 2).Value = "QueryName"
Sheets("QueryList").Cells(1, 3).Value = "ConnectionString"
Sheets("QueryList").Cells(1, 4).Value = "SQLString"
For Each wsh In ActiveWorkbook.Worksheets
For Each qrt In wsh.QueryTables
iQueryCnt = iQueryCnt + 1
Sheets("QueryList").Cells(1 + iQueryCnt, 1) = wsh.Name
Sheets("QueryList").Cells(1 + iQueryCnt, 2) = qrt.Name
Sheets("QueryList").Cells(1 + iQueryCnt, 3) = qrt.Connection
Sheets("QueryList").Cells(1 + iQueryCnt, 4) = qrt.Sql
Next
Next
If iQueryCnt = 0 Then
MsgBox "Keine Queries in dieser Arbeitsmappe", vbExclamation
Else
MsgBox "Total " & iQueryCnt & " Queries in der Arbeitsmappe.", vbInformation
End If
End Sub
Gruss Hansueli