Dieses Beispiel zeigt, wie man in einer Excel Datei die
Datenverbindung zu einer anderen Datenbank in der Laufzeit ändern kann.
In Microsoft Excel können Daten von Access datenbanken mit dem
Datenverbindungsmanager angebunden werden.
Wenn man hier eine Datenverbindung zu einer MS Access
Datenbank aufbaut, dann wird automatisch im Hintergrund eine Oledb Connection
erstellt.
Die Verbindungsinformationen werden dabei in sogenannte
Excel Workbook queries gespeichert.
Dabei findet man im Bereich Excel Power Query (rechter
Arbeitsbereich mit Queries & Connections) die Queries aufgelistet und in
den Query Properties kann man die Informationen der Datenverbindung ermitteln.
Hier steht im Beispiel dass der Connection type: OLE DB
Query ist
Und die Connection string: Provider=Microsoft.Mashup.OleDb.1
ist.
Zur Laufzeit kann man in einem Makro oder direkt in vba
Visual Basic for Applications die Informationen ermitten und verändern, indem
man die Queries durchläuft
Dabei muss man nur mit ThisWorkbook.Queries die
entsprechende .Formula einer Query abfragen.
|
Sub Change_DataSource()
'-------------------<
Change Connection to new AccessDB >-------------
Dim qry As WorkbookQuery
If ThisWorkbook.Queries.Count
> 0 Then
Dim sFormula As String
Set qry =
ThisWorkbook.Queries(1)
sFormula = qry.Formula
sFormula = Replace(sFormula, "DB2", "DB1", 1, 1, vbTextCompare)
qry.Formula = sFormula
End If
'-------------------</
Change Connection to new AccessDB >-------------
End Sub
|
In der einzelnen Query steht dann als Information wie im
Beispiel der Wert für Formula in der Quick Watches Anzeige:
|
'let
'Quelle =
Access.Database(File.Contents("C:\_Daten\Desktop\Demo\Access\2017-11-15
Access_Excel\20171115_Access_Excel.accdb"),
[CreateNavigationProperties=true]),
'_xls_Cars =
Quelle{[Schema="",Item="xls_Cars"]}[Data]
'in
'_xls_Cars"
'
'qry.Formula = sFormula
|
Datenverbindung
ändern
Alles was man jetzt machen musst, ist den Pfad und die
Dateiangabe zur Access Datenbank ändern und wieder in die .Formula Eigenschaft
der Query zurückschreiben.
Wenn man anschliessend mit Data->Refresh All die
Datenbankverbindungen aktualisiert, dann ändern sich auf zeitgleich alle werte
Tutorial