Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Auslesen aller .ODBCConnection

Forumthread: Auslesen aller .ODBCConnection

Auslesen aller .ODBCConnection
Martin
Hallo Zusammen
Einmal mehr Ratlos.
Da kann nur die Herber Gemeinde weiterhelfen.
Ausgangslage:
Habe ein Problem mit meinen bestehenden Makros welches alle Querytables ausliest und auflistet in einem neuen Tabellenblatt. Dies ist hilfreich/notwendig wenn viele Abfragen in einer Arbeitsmappe vorhanden sind und die Datenbank sich verändert, damit der sich veränderte Connectionstring mithilfe dieser Makros angepasst werden kann.
Arbeitsschritte:
Schritt 1: alle Qureys auslesen (mit Makro: "QuerysAuslesen()")
Schritt 2: alle Connectionstrings anpassen
Schritt 3: alle Querrys updaten (mit Makro: "QueriesEinlesen()")
Nun zum Problem:
Eine in Excel 2010 erstellte ODBC Abfrage wird nicht gefunden. Die Abfrage ist aber mit Garantie existent.
Verwirrend ist, eine bestehnde Abfrage welche noch mit der Version 2003 erstellt wurde kann ohne weiteres ausgelesen werden auch wenn das File in die Version 2010 konvertiert wurde.
Ist hier ein möglicher Fehler im Script? (war ursprünglich für 2003 unter Mithilfe des Herber Forums erstellt worden)

Makro zum Auslesen:

Sub QuerysAuslesen()
Dim qrt As QueryTable
Dim wsh As Worksheet
Dim bAddList As Boolean
Dim qrt_Anzahl As Integer
qrt_Anzahl = 0
bAddList = True
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name = "QuerryTables" Then
bAddList = False
Exit For
End If
Next
If bAddList Then
ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "QuerryTables"
End If
Sheets("QuerryTables").Cells(1, 1).Value = "BlattName"
Sheets("QuerryTables").Cells(1, 2).Value = "QueryName"
Sheets("QuerryTables").Cells(1, 3).Value = "ConnectionString"
Sheets("QuerryTables").Cells(1, 4).Value = "SQLString"
For Each wsh In ActiveWorkbook.Worksheets
For Each qrt In wsh.QueryTables
qrt_Anzahl = qrt_Anzahl + 1
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1) = wsh.Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) = qrt.Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3) = qrt.Connection
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4) = qrt.Sql
Next
Next
If qrt_Anzahl = 0 Then
MsgBox "Keine Queries in dieser Arbeitsmappe", vbExclamation
Else
MsgBox "Total " & qrt_Anzahl & " Queries in der Arbeitsmappe.", vbInformation
End If
End Sub


Makro zum einlesen der angepassten Querys:

Sub QueriesEinlesen()
Dim qrt As QueryTable
Dim wsh As Worksheet
Dim Tab_vorhanden As Boolean
Dim qrt_Anzahl As Integer
Tab_vorhanden = False
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name = "QuerryTables" Then
Tab_vorhanden = True
Exit For
End If
Next
If Not (Tab_vorhanden) Then
MsgBox "QuerryTables existiert nicht !" & vbCrLf & _
"Keine Queries angepasst", vbCritical
Exit Sub
End If
For Each wsh In ActiveWorkbook.Worksheets
For Each qrt In wsh.QueryTables
qrt_Anzahl = 1
Do While Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value  ""
If wsh.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value And _
qrt.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) Then
qrt.Connection = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3).Value
qrt.Sql = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4).Value
MsgBox "Blatt:" & wsh.Name & vbCrLf & _
"Query:" & qrt.Name & " angepasst!", vbInformation
End If
qrt_Anzahl = qrt_Anzahl + 1
Loop
Next
Next
End Sub

Anzeige

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: Auslesen aller .ODBCConnection
30.07.2012 11:08:36
fcs
Hallo Martin,
Excel 2010 erstellt beim Import von Daten über Abfragen in vielen Fällen eine Tabelle (Listobject) mit den abgefragten Daten.
Das kannst du daran erkennen, dass wenn du in die importierten Daten klickst das Menü "Tabellentools" angezeigt wird.
Die Querrie/Connection ist dann ein Unterobjekt des Listobjectes und nicht des Worksheets.
Du müsstest beim Einlesen zusätzlich zusätzlich prüfen, ob im Worksheet Tabellen(Listobjects) vorhanden sind und diese dann auf die Datenquelle prüfen und deren Informationen in deine Tabelle schreiben.
Ähnliches gilt dann beim zurückschreiben der Connection-Strings.
Nachfolgend deine beiden Makros mal in diese Richtung angepasst.
Gruß
Franz
Sub QuerysAuslesen()
Dim qrt As QueryTable, objListObject As ListObject, iIndex As Integer
Dim wsh As Worksheet
Dim bAddList As Boolean
Dim qrt_Anzahl As Integer
qrt_Anzahl = 0
bAddList = True
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name = "QuerryTables" Then
bAddList = False
Exit For
End If
Next
If bAddList Then
ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "QuerryTables"
End If
Sheets("QuerryTables").Cells(1, 1).Value = "BlattName"
Sheets("QuerryTables").Cells(1, 2).Value = "QueryName"
Sheets("QuerryTables").Cells(1, 3).Value = "ConnectionString"
Sheets("QuerryTables").Cells(1, 4).Value = "SQLString"
Sheets("QuerryTables").Cells(1, 5).Value = "ListObject-Name"
Sheets("QuerryTables").Cells(1, 6).Value = "ListObject-Index"
For Each wsh In ActiveWorkbook.Worksheets
For Each qrt In wsh.QueryTables
qrt_Anzahl = qrt_Anzahl + 1
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1) = wsh.Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) = qrt.Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3) = qrt.Connection
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4) = qrt.Sql
Next
iIndex = 0
For Each objListObject In wsh.ListObjects
iIndex = iIndex + 1
With objListObject
If .SourceType = xlSrcQuery Then
Set qrt = .QueryTable
qrt_Anzahl = qrt_Anzahl + 1
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1) = wsh.Name
'Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) = qrt.Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3) = qrt.Connection
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4) = qrt.Sql
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 5) = .Name
Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 6) = iIndex
End If
End With
Next
Next
If qrt_Anzahl = 0 Then
MsgBox "Keine Queries in dieser Arbeitsmappe", vbExclamation
Else
MsgBox "Total " & qrt_Anzahl & " Queries in der Arbeitsmappe.", vbInformation
End If
End Sub
Sub QueriesEinlesen()
Dim qrt As QueryTable, objListObject As ListObject, iIndex As Integer
Dim wsh As Worksheet
Dim Tab_vorhanden As Boolean
Dim qrt_Anzahl As Integer
Tab_vorhanden = False
For Each wsh In ActiveWorkbook.Worksheets
If wsh.Name = "QuerryTables" Then
Tab_vorhanden = True
Exit For
End If
Next
If Not (Tab_vorhanden) Then
MsgBox "QuerryTables existiert nicht !" & vbCrLf & _
"Keine Queries angepasst", vbCritical
Exit Sub
End If
For Each wsh In ActiveWorkbook.Worksheets
For Each qrt In wsh.QueryTables
qrt_Anzahl = 1
Do While Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value  ""
If wsh.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value And _
qrt.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) Then
qrt.Connection = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3).Value
qrt.Sql = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4).Value
MsgBox "Blatt:" & wsh.Name & vbCrLf & _
"Query:" & qrt.Name & " angepasst!", vbInformation
End If
qrt_Anzahl = qrt_Anzahl + 1
Loop
Next
iIndex = 0
For Each objListObject In wsh.ListObjects
iIndex = iIndex + 1
qrt_Anzahl = 1
Do While Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value  ""
If wsh.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value And _
iIndex = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 6) Then
Set qrt = objListObject.QueryTable
qrt.Connection = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3).Value
qrt.Sql = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4).Value
MsgBox "Blatt:" & wsh.Name & vbCrLf & _
"Listobject-Query:" & objListObject.Name & " angepasst!",  _
vbInformation
End If
qrt_Anzahl = qrt_Anzahl + 1
Loop
Next
Next
End Sub

Anzeige
AW: Auslesen aller .ODBCConnection
30.07.2012 11:31:19
Martin
Hallo Franz
Mann o mann. Wie soll ich den jetzt Danke sagen, das war es. Leider war ich seit längerem nicht mehr mit Makros konfrontiert und das neue Excel ist in vieler Hinsicht anders geworden.
Danke Franz, vielen Dank.
Das passt perfekt.
Gruss
Martin
;
Anzeige

Infobox / Tutorial

ODBC-Verbindungen in Excel auslesen und anpassen


Schritt-für-Schritt-Anleitung

Um alle ODBC-Verbindungen in Excel auszulesen und anzupassen, kannst du die folgenden Schritte befolgen:

  1. Makro zum Auslesen der Queries: Verwende das folgende VBA-Makro, um alle QueryTables in deiner Arbeitsmappe auszulesen und in einem neuen Blatt namens "QuerryTables" aufzulisten.

    Sub QuerysAuslesen()
       Dim qrt As QueryTable
       Dim wsh As Worksheet
       Dim bAddList As Boolean
       Dim qrt_Anzahl As Integer
       qrt_Anzahl = 0
       bAddList = True
       For Each wsh In ActiveWorkbook.Worksheets
           If wsh.Name = "QuerryTables" Then
               bAddList = False
               Exit For
           End If
       Next
       If bAddList Then
           ActiveWorkbook.Sheets.Add Before:=ActiveWorkbook.Worksheets(1)
           ActiveSheet.Name = "QuerryTables"
       End If
       Sheets("QuerryTables").Cells(1, 1).Value = "BlattName"
       Sheets("QuerryTables").Cells(1, 2).Value = "QueryName"
       Sheets("QuerryTables").Cells(1, 3).Value = "ConnectionString"
       Sheets("QuerryTables").Cells(1, 4).Value = "SQLString"
       For Each wsh In ActiveWorkbook.Worksheets
           For Each qrt In wsh.QueryTables
               qrt_Anzahl = qrt_Anzahl + 1
               Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1) = wsh.Name
               Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) = qrt.Name
               Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3) = qrt.Connection
               Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4) = qrt.Sql
           Next
       Next
       If qrt_Anzahl = 0 Then
           MsgBox "Keine Queries in dieser Arbeitsmappe", vbExclamation
       Else
           MsgBox "Total " & qrt_Anzahl & " Queries in der Arbeitsmappe.", vbInformation
       End If
    End Sub
  2. Makro zum Einlesen der angepassten Queries: Nutze das folgende Makro, um die angepassten Verbindungen zurückzuschreiben.

    Sub QueriesEinlesen()
       Dim qrt As QueryTable
       Dim wsh As Worksheet
       Dim Tab_vorhanden As Boolean
       Dim qrt_Anzahl As Integer
       Tab_vorhanden = False
       For Each wsh In ActiveWorkbook.Worksheets
           If wsh.Name = "QuerryTables" Then
               Tab_vorhanden = True
               Exit For
           End If
       Next
       If Not (Tab_vorhanden) Then
           MsgBox "QuerryTables existiert nicht !" & vbCrLf & _
           "Keine Queries angepasst", vbCritical
           Exit Sub
       End If
       For Each wsh In ActiveWorkbook.Worksheets
           For Each qrt In wsh.QueryTables
               qrt_Anzahl = 1
               Do While Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value <> ""
                   If wsh.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 1).Value And _
                   qrt.Name = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 2) Then
                       qrt.Connection = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 3).Value
                       qrt.Sql = Sheets("QuerryTables").Cells(1 + qrt_Anzahl, 4).Value
                       MsgBox "Blatt:" & wsh.Name & vbCrLf & _
                       "Query:" & qrt.Name & " angepasst!", vbInformation
                   End If
                   qrt_Anzahl = qrt_Anzahl + 1
               Loop
           Next
       Next
    End Sub
  3. Sicherstellen, dass alle Queries vorhanden sind: Stelle sicher, dass deine Queries in der Arbeitsmappe vorhanden sind, bevor du das Einlesen durchführst.


Häufige Fehler und Lösungen

  • Fehler: Abfrage nicht gefunden: Wenn eine ODBC-Abfrage nicht gefunden wird, überprüfe, ob sie als ListObject importiert wurde. In Excel 2010 und höheren Versionen kann es vorkommen, dass Abfragen als ListObjects gespeichert werden. Du musst die Makros entsprechend anpassen, um auch diese Abfragen zu berücksichtigen.

  • Lösung: Füge Überprüfungen in dein Auslese-Makro ein, um auch ListObjects zu berücksichtigen. Siehe dazu den angepassten Code von Franz.


Alternative Methoden

  • Über Power Query: Eine alternative Methode zur Verwaltung von ODBC-Verbindungen besteht darin, Power Query zu verwenden. Damit kannst du Daten aus verschiedenen Quellen importieren und die Verbindungen einfacher verwalten.

  • Direktes Editieren der Verbindungen: Du kannst auch die Datenverbindungen in Excel unter "Daten" > "Abfragen und Verbindungen" einsehen und bearbeiten.


Praktische Beispiele

Hier ist ein Beispiel, wie du eine ODBC-Verbindung in Excel 2010 anpassen kannst:

  1. ODBC-Verbindung erstellen: Gehe zu "Daten" > "Daten abrufen" > "Aus anderen Quellen" > "Aus ODBC".
  2. Datenquelle auswählen: Wähle deine ODBC-Datenquelle aus.
  3. SQL-Abfrage anpassen: Verwende den SQL-Editor, um deine Abfrage anzupassen und importiere die Daten.

Tipps für Profis

  • Makros optimieren: Achte darauf, dass deine Makros effizient sind, insbesondere wenn du mit großen Datensätzen arbeitest. Vermeide unnötige Schleifen und nutze With-Anweisungen, um den Code zu vereinfachen.

  • Fehlerbehandlung einbauen: Implementiere eine Fehlerbehandlung in deinen Makros, um unerwartete Fehler besser zu handhaben und dem Benutzer hilfreiche Rückmeldungen zu geben.


FAQ: Häufige Fragen

1. Warum kann ich meine ODBC-Abfragen in Excel 2010 nicht finden?
In Excel 2010 werden ODBC-Abfragen oft als ListObjects gespeichert. Stelle sicher, dass dein Makro auch diese Objekte berücksichtigt.

2. Wie kann ich die Verbindungen in Excel 2010 bearbeiten?
Du kannst die Verbindungen unter "Daten" > "Abfragen und Verbindungen" einsehen und bearbeiten. Alternativ kannst du VBA-Makros nutzen, um die Verbindungen programmgesteuert zu aktualisieren.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige