Excel 2016 Funktion gesucht, um einzelne Werte aus Access-DB abzurufen

[Yppps]

Hallo zusammen!

Ich scheibe seit Monaten an einer recht komplexen Tabelle, bei der ich nun gezwungen war, Daten in eine Access-Datenbank auszulagern, die ich bislang in einem internen Tabellenblatt gespeichert hatte.

Grundsätzlich bestand halt das Problem, daß die Tabelle aufgrund der Datenmenge (rund 25MB) so aufgepumpt wurde, daß die Anwendung auf einem "bezahlbaren" Laptop endlos langsam und instabil wurde. Dabei werden letzendlich nur 15 x 2 Byte Informationen tatsächlich benötigt.

Die Access Datenbank hat nun folgende Struktur

GKIndex | WS010m | WS020m | ... | WS100m | WB10C | WB10K | WB80C | WB80K | WKNE

Insgesamt also 16 Spalten, bei exakt 572.000 Datensätzen.

Bei GKIndex handelt es sich um einen eindeutigen Schlüssel, der sich aus den jeweils ersten 4 Stellen der Geokoordinaten im Gauss-Krüger-Format (GK-hoch, GK-rechts) zusammensetzt, also z. B 34165712.

Anhand der in der Tabelle einzugebenen Straße und PLZ des Projektstandortes, werden die Geokoordinaten in Längen- und Breitengrad ermittelt und in Gauss-Krüger transformiert.

Bislang konnte ich die zu dem Standort gehörigen Winddaten (WS010m bis WKNE) per einfacher Index-Abfrage erhalten, da die Matrixen ja in einem Arbeitsblatt der Tabelle vorlagen.

Idealerweise suche ich nun nach einer Funktion, die mir das selbe in der Accesss-Datenbank erlaubt, Sinngemäß

="C:/HyRE-x/DWD/DWDDataPool.accdb".W010m.value, GKIndex=GKIndexProjekt

oder

=sverweis(GKIndexProjekt;"C:/HyRE-x/DWD/DWDDataPool.accdb";2 (bis 16) )

Insgesamt würden so sämtliche 15 Einträge des jeweiligen Datensatzes in die entsprechenden Zellen geschrieben.

Sollte es eine solche Funktion nicht geben, kann das natürlich auch per Makro erfolgen, welches automatisch ausgelöst würde, sollte sich der Wert in der Zelle GKIndexProjekt geändert haben.

Habe bereits mehrfach versucht ein Makro aufzuzeichen, welches mit  von Query den "Kanal zur Datenbank" öffnet, die Werte mit vorgegebenem Index ausließt und in ein neues Tabellenblatt einfügt. Während der Aufzeichnung des Makros funktioniert das auch einwandfrei. Sobald ich das Makro dann jedoch (bei gleichen Startbedingungen wie bei der Aufzeichnung) ausführen lasse, erhalte ich eine Fehlermeldung nach der anderen. 

Komme absolut nicht weiter...

Für Eure Anregungen und Lösungen schon jetzt meinen Dank!

LG

André

[gmg-cc]

Moin André,
in Excel 2016 (Win) hast du ja schon Power Query im Bereich Daten integriert. Damit solltest du eine erträgliche Arbeitsgeschwindigkeit von Excel aus hinbekommen. Filter aud die ID setzen und d hast nur 1 Datensatz auf dem Schirm. Oder du machst in Access eine Abfrage uns lässt dir die Daten nach Excel senden ...

[Yppps]

Zunächst mal Danke für die schnelle Antwort!

Leider verhält es sich bei Query so, daß die Makros nach der Aufzeichnung nicht laufen. Es kommt zu Fehlermeldungen, auch wenn die Startbedingungen zu 100% identisch sind wie zu dem Zeitpunkt, als die Makroaufzeichnung durchgeführt wurde.

Das aufgezeichnete Makro sieht folgendermaßen aus:

Sub GetDWD()

    Sheets("Kosten und Finanzierung").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle6").Select
    Sheets("Tabelle6").Name = "DWD Query"


    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DBQ=C:\HYRE-X\DWD\DWDDataPool.accdb;DefaultDir=C:\HYRE-X\DWD;Driver={Microsoft Access Driver (*.mdb, *.accdb)};DriverId=25;FIL=" _
        ), Array( _
        "MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;" _
        )), Destination:=Range("$A$1")).QueryTable
        .CommandType = 0                            ' <----------- HIER KOMMT ES ZUR FEHLERMELDUNG
        .CommandText = Array( _
        "SELECT DWDDataPool.Index, DWDDataPool.WS010m, DWDDataPool.WS020m, DWDDataPool.WS030m, DWDDataPool.WS040m, DWDDataPool.WS050m, DWDDataPool.WS060m, DWDDataPool.WS070m, DWDDataPool.WS080m, DWDDataPool.WS090m, DWDDataPool.WS100m, DWDDataPool.WB10C, DWDDataPool.WB10K, DWDDataPool.WB80C, DWDDataPool.WB80K, DWDDataPool.WKNE" & Chr(13) & "" & Chr(10) & "FROM `C:\HYRE-X\DWD\DWDDataPool.accdb`.DWDDataPool DWDDataPool" & Chr(13) & "" & Chr(10) & "WHERE (DWDDataPo" _
        , "ol.Index=34175512)")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Tabelle_Abfrage_von_DWD"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Der Schlüsselindex muß zudem aus einem Zellwert (Sheet "Konfiguration Turbinen" Range K17) bedient werden, nicht wie in der Aufzeichnung mit der Konstanten ( "ol.Index=34175512)") )

Habe es jetzt 15x versucht mit dem Makro-Reorder. ohne Erfolg.

Was mache ich falsch?

LG

André

[gmg-cc]

Dass deine Infos im Verhältnis zum Gesamtprojekt einfach zu "dünn" sind, das kanst du dir denken.
Und: Ich habe nicht von Query (der klassischen Version) sondern von Power Query (bzw. dem Nachfolger) gesprochen.

Auf der anderen Seite: Vielleicht sollte da ein Dienstleister dran, der dann alles sieht. Kostet zwar Geld, aber schließlich verdient ihr ja auch Geld mit dem, was ihr erwirtschaftet. Und eine Behörde sollte so wie so einen zentralen IT-Ansprechpartner haben.

[Yppps]

Das mit dem Geld verdienen wäre schön 

Leider soll die Anwendung kostenlos in einem Portal für Interessierte an Kleinwindkraftanlagen zur Verfügung gestellt werden, damit jeder im Vorfeld einen Eindruck bezüglch der generellen Eignung seines Standortes für die Gewinnung von Strom durch Wind erhalten kann. Hierzu stellt der DWD kostenlos Millionen von Daten zur Verfügung, die mit diesem Tool zusammengetragen und ausgewertet werden sollen.

Bei meinen Recherchen bin ich jetzt auf die Möglicheit eines "Late binding" gestoßen. Nur finde ich keine verwertbaren Informationen über die genaue Synthax.

Wenn ich das richtig verstanden habe (war auf einer englischsprachigen Seite), würde eine solche Lösung dann auch in älteren Excel-Versionen (2003/2007) laufen, da das Power Query AddIn 2010/2013 nicht benötigt wird.

Also nochmal zusammengefasst: Ich muss aus Excel heraus Zugriff auf eine Accessdatenbank (mdb oder accdb) erhalten, um die in der Datenbank abgelegten Werte zu einem bekannten Schlüsselindex in Excelzellen zu übergeben.

Nochmals Danke für Deine/Eure Bemühungen.

LG

André

[Yppps]

Habe jetzt folgenden Code zusammengehackt...

Sub DWDDataPoolHolen()
'
' DWDDataPoolHolen Makro
'

'
Application.ScreenUpdating = False
Application.StatusBar = "Initialisiere DWDDataPool"
   
ThisWorkbook.Worksheets.Add.Name = "DWD Query"

On Error GoTo weiter
   
    Sheets("DWD Query").Select
   
Application.StatusBar = "Setze Zeiger auf " & Sheets("Konfiguration Wind").Range("AX13").Value

    ActiveWorkbook.Queries.Add Name:="DWDDataPool", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Quelle = Access.Database(File.Contents(""C:\HyRE-x\DWD\DWDDataPool.accdb""), [CreateNavigationProperties=true])," & Chr(13) & "" & Chr(10) & "    _DWDDataPool = Quelle{[Schema="""",Item=""DWDDataPool""]}[Data]," & Chr(13) & "" & Chr(10) & "    #""Gefilterte Zeilen"" = Table.SelectRows(_DWDDataPool, each [Index] = " & Sheets("Konfiguration Wind").Range("AX13").Value & ")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Gefilterte Zeilen"""

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""DWDDataPool""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [DWDDataPool]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "DWDDataPool"
        .Refresh BackgroundQuery:=False
    End With
   
weiter:

Application.StatusBar = "Schreibe Import-Maske"
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Application.StatusBar = "Übergebe Daten an Tabelle"
    Call WerteSetzen_DWDQuery

Application.StatusBar = "Trenne Verbindung"
    Do While ActiveSheet.QueryTables.Count > 0
        ActiveSheet.QueryTables.Item(ActiveSheet.Queries.Count).CancelRefresh
        ActiveSheet.QueryTables.Item(ActiveSheet.Queries.Count).Unlink
    Loop

    Do While ActiveWorkbook.Connections.Count > 0
        ActiveWorkbook.Connections.Item(ActiveWorkbook.Connections.Count).Delete
    Loop

Sheets("DWD Query").Delete

End Sub

Das importieren der Daten funktioniert damit wunderbar. Allerdings nur 1x. Damit das Makro ein zweites mal sauber abläuft, muss ich manuell die Arbeitsmappenabfrage löschen. Meine Versuche, zunächst die queries und anschließend die Connections via VBA zu löschen, werden gnadenlos ignoriert.

Auch mußte ich eine "On error goto"-Krücke einbauen, da das Makro nach Ausführung des Befehls

    With ActiveSheet.ListObjects.Add(....
    ...
    end with

automatisch neu startet.

Ist das en Bug, oder ist der Code so "unsauber", daß VBA quasi abstürzt und deshalb das Makro erneut startet?

Bin für jede Anregung dankbar!

LG

André