VBA-Verbindungseigenschafte/Befehlstext

Forumthread: VBA-Verbindungseigenschafte/Befehlstext

21.10.2016 12:10:02

Fred

Hallo Experten,
mal wieder so eine Frage!
Meine Basis:
Meine betreffende Arbeitsmappe ist mit 6 weiteren Excel-Arbeitsmappen verbunden. Die verbundenen Mappen haben alle den gleichen Struktur-Aufbau,- also gleiche Blattanzahl und gleiche Blattnamen mit identischen Spaltenüberschriften.
Die (für meine Anfrage hier im Forum) wohl relevanten Verbindungseigenschaften meiner 6 Verknüpfungen lauten;
Verbindungsname: 1Jahr, Befehlstext: D1$
Verbindungsname: 2Jahre, Befehlstext: D1$
Verbindungsname: 3Jahre, Befehlstext: D1$
Verbindungsname: 4Jahre, Befehlstext: D1$
Verbindungsname: 5Jahre, Befehlstext: D1$
Verbindungsname: Aktuell, Befehlstext: D1$
Will ich z.B. das Tabellenblatt mit dem Namen E1 durch meine Verbindungen aufrufen, ist meine bisherige Vorgehensweise so, daß ich jede einzelne Verbindungseigenschaft aufrufe und händisch den Befehlstext in E1$ ändere. Bei öfteren wechseln ist das natürlich schon mit Aufwand und Zeit verbunden, dementsprechend auch mit Fehleranfälligkeit.
Meine Wunschlösung:
Ich habe in meiner betreffenden Arbeitsmappe das Tabellenblatt Auswahl.
In A1 steht z.B. F1$ und durch Klick auf eine Befehlsschaltfläche sollen die Befehlstexte in den Verbindungseigenschaften / Definitionen entsprechend geändert werden.
Oder zumindest eine Definition und dann eben 6 Schaltflächen und Klicks 
Meine Frage ist folglich, ob diese Änderung in den Verbindungseigenschaften mit VBA überhaupt möglich ist und wenn ja, ob mir jemand diesen Syntax schreiben kann.
freundliche Grüße
Fred

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: VBA-Verbindungseigenschafte/Befehlstext

22.10.2016 01:11:44

Piet
AW: VBA-Verbindungseigenschafte/Befehlstext

22.10.2016 11:48:04

Fred
AW: VBA-Befehlstext

22.10.2016 12:40:56

Fred

AW: VBA-Verbindungseigenschafte/Befehlstext

22.10.2016 01:11:44

Piet

Hallo Fred,
ich nehme an das sich F1$ in einer Formel befindet und geaendert werden soll. Kleiner Tipp:
Alle Formeln lassen sich von Hand mit Suchen und Ersetzen aendern. Wenn du den Bereich vorher
eingrenzt wird nur in dem markierten Bereich geaendert. Formeln kann man so auch deaktivieren,
indem man vor das Gleichzeichen ein ' setzt. Dann ist die Formel ein Text. Zum aendern ein Beispiel:

Sub Replace_Text()
Range("B2:B20").replace what:="F1", replacement:="E1", lookat:=xlPart, MatchCase:=False
end Sub

mfg Piet

AW: VBA-Verbindungseigenschafte/Befehlstext

22.10.2016 11:48:04

Fred

Hallo Piet,
also ich habe Verbindungsdaten z.B. zur Arbeitsmappe 1Jahr und deren Tabellenblatt D1.
Tabellenblatt D1 ist ja unter den Verbindungseigenschaften / Befehlstext mit D1$ definiert.
Ich möchte per VBA die Verbindung ändern. Es soll sich mit dem Tabellenblatt verbinden, welches in meiner ausgehenden Datei in Sheet Auswahl, Zelle A1 angegeben ist. Will ich also z.B. eine Verbindung zu Tabellenblatt F1, muß in Sheet Auswahl, Zelle A1 der Text stehen: F1$.
Mache ich die Verbindung z.B. über den Recorder, ist dies folgender Code:


Sub aendern()
Sheets("Auswahl").Select
With ActiveWorkbook.Connections("1Jahr").OLEDBConnection
.BackgroundQuery = True
.CommandText = Array("F1$")
.CommandType = xlCmdTable
.Connection = Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Besitzer\ _
Desktop\Daten\1Jahr.xls;Mode=Shar" _
, _
"e Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB: _
Registry Path="""";Jet OLEDB:Engine Type=35;Jet" _
, _
" OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global  _
Bulk Transactions=1;Jet OLEDB:New Database P" _
, _
"assword="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False; _
Jet OLEDB:Don't Copy Locale on Compact=False;" _
, _
"Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support  _
Complex Data=False" _
)
.RefreshOnFileOpen = True
.SavePassword = False
.SourceConnectionFile = ""
.SourceDataFile = _
"C:\Users\Besitzer\Desktop\Daten\1Jahr.xls"
.ServerCredentialsMethod = xlCredentialsMethodIntegrated
.AlwaysUseConnectionFile = False
End With
With ActiveWorkbook.Connections("1Jahr")
.Name = "1Jahr"
.Description = ""
End With
ActiveWorkbook.Connections("1Jahr").Refresh
End Sub

Also, mein Wunsch:
.CommandText = soll sich auf den Text in Sheet Auswahl, Zelle A1 beziehen.
mfg Fred

AW: VBA-Befehlstext

22.10.2016 12:40:56

Fred

also,
habe nun festgestellt, das folgender Syntax wohl absolut ausreichend ist:


Sub aendern()
Sheets("Auswahl").Select
With ActiveWorkbook.Connections("1Jahr").OLEDBConnection
.CommandText = Worksheets("Auswahl").Cells(1, 1)
End With
ActiveWorkbook.Connections("1Jahr").Refresh
End Sub

Infobox / Tutorial

VBA-Verbindungseigenschaften und Befehlstext in Excel ändern

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die Befehlstexte in den Verbindungseigenschaften Deiner Excel-Arbeitsmappe mittels VBA zu ändern, folge dieser Anleitung:

Öffne Deine Excel-Arbeitsmappe.
Erstelle ein neues Modul, indem Du im VBA-Editor (Alt + F11) mit der rechten Maustaste auf "VBA-Projekt" klickst und "Modul einfügen" auswählst.

Füge den folgenden VBA-Code in das Modul ein:

Sub aendern()
   Sheets("Auswahl").Select
   With ActiveWorkbook.Connections("1Jahr").OLEDBConnection
       .CommandText = Worksheets("Auswahl").Cells(1, 1).Value
   End With
   ActiveWorkbook.Connections("1Jahr").Refresh
End Sub

Stelle sicher, dass in Deinem Blatt "Auswahl" in Zelle A1 der gewünschte Befehlstext, z.B. "F1$", steht.
Führe das Makro aus, um die Verbindungseigenschaften zu aktualisieren.

Häufige Fehler und Lösungen

Fehler: "Verbindung nicht gefunden"
- Lösung: Überprüfe den Namen der Verbindung in ActiveWorkbook.Connections("1Jahr"). Stelle sicher, dass der Name korrekt geschrieben ist.
Fehler: ".CommandText ist nicht gültig"
- Lösung: Achte darauf, dass der Befehlstext in Zelle A1 korrekt formatiert ist und die Syntax einhalten, z.B. "F1$".
Fehler: "Makro kann nicht ausgeführt werden"
- Lösung: Stelle sicher, dass die Makros in Excel aktiviert sind. Gehe zu den Excel-Optionen und aktiviere die Makros.

Alternative Methoden

Eine alternative Methode, um die Befehlstexte zu ändern, ist die Verwendung von Suchen und Ersetzen:

Markiere den Bereich, in dem die Formeln stehen.
Drücke Strg + H, um das Such- und Ersetzfenster zu öffnen.
Gib im Feld "Suchen nach" den alten Befehlstext ein (z.B. "D1$").
Gib im Feld "Ersetzen durch" den neuen Befehlstext ein (z.B. "F1$").
Klicke auf "Alle ersetzen".

Diese Methode ist schnell, jedoch weniger flexibel als die VBA-Lösung.

Praktische Beispiele

Ein Beispiel für die Verwendung des VBA-Codes:

Angenommen, Du hast mehrere Verbindungen mit den Namen "1Jahr", "2Jahre", usw. und möchtest den Befehlstext für jede dieser Verbindungen ändern. Du kannst den obigen Code in einer Schleife anpassen:

Sub aendernAlle()
    Dim i As Integer
    Dim verbindung As String
    For i = 1 To 6
        verbindung = i & "Jahr"
        With ActiveWorkbook.Connections(verbindung).OLEDBConnection
            .CommandText = Worksheets("Auswahl").Cells(1, 1).Value
        End With
        ActiveWorkbook.Connections(verbindung).Refresh
    Next i
End Sub

Dieser Code ändert den Befehlstext für alle sechs Verbindungen basierend auf dem Wert in Zelle A1.

Tipps für Profis

Verwende Error Handling in Deinem VBA-Code, um mögliche Laufzeitfehler abzufangen und besser zu steuern.
Halte Deine Excel-Arbeitsmappe sauber und dokumentiere Deine VBA-Codes, damit Du sie später leichter wiederfinden und verstehen kannst.
Experimentiere mit Benutzereingaben über Formulare, um dynamische Werte für Befehlstexte zu erhalten.

FAQ: Häufige Fragen

1. Kann ich mehrere Befehlstexte gleichzeitig ändern?
Ja, Du kannst den VBA-Code anpassen, um mehrere Verbindungen in einer Schleife zu aktualisieren.

2. Was passiert, wenn der Befehlstext ungültig ist?
Die Verbindung wird nicht erfolgreich aktualisiert, und eventuell tritt ein Fehler auf. Stelle sicher, dass der Befehlstext korrekt ist.

3. Funktioniert dieser Ansatz in allen Excel-Versionen?
Der VBA-Code sollte in den meisten modernen Excel-Versionen funktionieren, allerdings kann es Unterschiede in den Datenquellen geben.

Beliebteste Forumthreads (12 Monate)

VBA-Verbindungseigenschafte/Befehlstext

Forumthread: VBA-Verbindungseigenschafte/Befehlstext

3 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

VBA-Verbindungseigenschaften und Befehlstext in Excel ändern

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread