Office: Datenquelle von Powerquery per VBA "Auswahlbox" selektieren

Hallo zusammen,

ich habe in Excel 2016 unter Abfragen und Verbindungen eine PowerQuery eingerichtet.

Die TXT aus der ich die Daten ziehe hat jedoch nach jeder Aktualisierung einen anderen Pfad und Namen.

Bis jetzt muss ich immer den Klassischen weg über "Bearbeiten -> Quelle ändern" nehmen um die Dateiauswahl zu öffnen.

Kann ich per VBA Application.GetOpenFilename() eine TXT auswählen und diese TXT dann an die Powerquery weitergeben? Das hätte auch den Vorteil, das "Power Query" ungeübte die Daten ohne Berührung mit PowerQuery selbst durchführen könnten (ohne den Umweg über den "Abfragen und Verbindungen" Dialog).

Der Makrorekorder zeichnet leider für die Aktion "Quelle ändern" nichts auf.


Viele Grüße,

Dominik

:)

 

Moin Dominik,
imho geht das nicht.
Entweder du erstellst per VBA eine Kopie der *.txt und benennst diese dann nach dem Standard-*.txt (überschreiben) oder du legst in PQ eine Parameter-Tabelle an, wo der User den kompletten Pfad/Namen eingibt. Diese Tabelle kann dann von PQ ausgewertet werden.

 

Guten Morgen Drogist,

mit der Paramter-Tabelle meinst du damit eine solche Lösung?


Ich kann dort ein Verzeichnis definieren -> ok. Kann ich auch prüfen, wenn dort mehrere TXT liegen, ob eine von dem Format vorliegt, wie ich es in das Query einlesen will? Eventuell liegen dort unterschiedliche SAP-Extrakte ab.

Kannst du die Methode mit der Kopie noch etwas erläutern? Der User kann die .TXT quasi beliebig benennen und ablegen (Extrakt aus SAP kann überall gespeichert werden).
Kann ich prüfen, ob eine TXT welche entsprechende Bedingungen erfüllt zumindest im selben Verzeichnis wie die .XLSM, diese dann in den Standardnamen umbennnen und dann automatisch einlesen? So würde ich als Arbeitsanweisung nur vorgeben "TXT muss ins gleiche Verzeichnis und dann klick hier drauf in der Exceldatei".


Viele Grüße,

Dominik

 

Hallo Dominik,

du kannt natürlich auch über Application:GetOpenFilename() den Anwender die datei, die mit PowerQuery gelesen werden soll, auswählen lassen und dann auf die Quelle für PQ kopieren.

PQ hat dann immer eine feste Quelle, aber der Anwender hat vorher ausgewählt.

Hier der Code für diese Variante:

Sub gewählte_datei_kopieren()
Dim Source As Variant
Dim Filename As String
Dim Target As String

Source = Application.GetOpenFilename()
If Source = False Then
MsgBox "Der Benutzer hat abgebrochen.", vbInformation
Else
Filename = Split(Source, "")(UBound(Split(Source, "")))

Target = "C:Temp" & Filename
FileCopy Source, Target

End If
End Sub

Das Target musst du natürlich anpassen und FileCopy überschreibt direkt ohne lästigen Dialog.

Diese Variante erscheint mir etwas eleganter als die Paramatertabelle. Denn in der Paramtertabelle müsste der Pfad und Dateiname immer von Hand geändert werden.

 

Moin WS-53,

verstehe ich das korrekt, dass der User dann die Datei wählt, diese wird in das Target Verzeichnis + Target Filename "überführt" (wenn also die PQ immer von C: Daten report.txt einliest, dann müsste das dort stehen).

Blöde Frage: Findet eigentlich nach der Auswahl und Kopieren eine automatische Aktualisierung der Daten statt oder ändern sich nur die Daten und aktualisieren muss ich manuell?


Viele Grüße,

Dominik

 

Hallo Dominik,

mit PQ habe ich bisher noch kaum gearbeitet. Nur teilweise Demos nachgestellt, die der Drogist dokumentiert hat. Somit versuche ich, mich immer mehr diesem tollen Tool zu nähern.

Soweit ich weiß, musst du dann aber immer noch aktualisieren. Aber ich glaube mich erinnern zu können, dass dies auch schon mal gefragt wurde und es darauf auch eine Antwort gab.

Aber anssonsten denke ich, dass ich deine Frage mit JA beantworten kann.

Im SAP habe ich übrigens mal einem Report insoweit ergänzt, dass der Anwender bei der Ausführung eine Checkbox aktivieren kann, über die das Ergebnis am Ende in die Zwischenablage kopiert werden kann.

Die Excelmappe, mit der diese Daten dann aufbereitet werden, prüft bei jedem öffnen, ob sich etwas in der Zwischenablage befindet und im Falle von Ja, wird dann nioch geprüft, ob sich die Struktur der Daten mit dem deckt, was mit der Mappe verarbeitet werden soll. Ist dies der Fall, dann wird der Anwender gefragt, ob der Inhalt der Zwischenablage direkt import und aufbereitet werden soll.

 

Hallo nochmal,

danke dir schonmal für deine Ausführungen.

Kann leider erst in den nächsten Tagen testen, melde mich dann nochmal.


Vielen Dank,

Dominik

 

Moin Dominik,
YT-Videos als Begleitung zu schriftlichen Darlegungen sind für mich OK, als Standalone: sehe ich mir nicht an. OK, das ist mein Ding, trifft aber hier zu. Insofern kann ich dir nicht sagen, ob das dort gezeigte zutrifft oder nicht. Meine Idee ging in die Richtung: https://www.excelguru.ca/blog/2014/1...r-power-query/

 

Hallo Drogist,

ja kein Ding, es handelt sich um die gleiche Idee.

Genial wäre es natürlich, wenn man es doch per Auswahl so umsetzen könnte wie von WS-53 vorgeschlagen.

Werde es alsbald testen und möglicherweise dann nochmal um Hilfe ersuchen.

/edit: Konnte es nun doch testen mit folgendem Quellcode

Code:

Kriege den Fehler Laufzeitfehler 52 Datename oder -nummer falsch, sobald ich eine TXT auswähle, z. B. Neue Textdatei.txt

Habe ich das Prinzip eventuell falsch verstanden? Muss ich statt Filename mein Wunsch-Zieldateinamen eingeben?

Dann kriege ich Fehler 424, Objekt erforderlich.

 

Das YT-Video vom Meister Thehos basiert auf Deinen Link ;-)

 

Danke Ralf,
nun ja, Ken Puls ist ja einer der ganz Großen in Sachen Power Query und ist nun einmal "state of the art" als auch Vordenker für anspruchsvolle User. *wink.gif*

 

Hallo Dominik,

ich habe nunb einmal, entsprechend meiner Vorstellungen eine Testmappe , die Stammquelle und 2 auswählbare Quellfiles erstellt. Und dann den Code an die Zielumgebung (Laufwerk "H:") angepasst.

Bei mir läuft es wie gewünscht und durch die zusätzlich PQ-Refreshanweisung, sehe ich auch immer sofort die neu geladenen Daten.

Somit ist die PQ-Quelle voll flexible und die Bedienung ist sehr komfortabel.

Anbei die Files.


Nacghtrag: ich denke für variable Filter ist das vom Meister Thehos beschriebene Verfahren sehr gut geeignet. Aber die die variable Auswahl einer Quelldatei finde ich es nicht so prickelnd.

 

Hallo alle zusammen,

ich möchte mich nochmal bei allen beteiligten Bedanken, ich konnte die Lösung dank eurer Hilfe so wie von mir gewünscht umsetzen.

Herzlichen Dank,

Dominik

 

Hallo Dominik,

Korrekt wäre es gewesen, wenn du auch erläutert hättest, wie deine Umsetzung nun aussieht.