Daten-Abfrage dynamisieren und automatisieren

[mhryvo]

Hallo Leute,
ich habe folgendes Problem:
Ich habe eine Excel Datei(über 100MB groß) mit über 50 Tabellenblättern die alle Zeitreihen-Daten in dem Format enthalten: (siehe Quelldatei.xlsx)
 [img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Nun möchte ich daraus immer die vorletzte Spalte mit dem Kopf "2016" in eine Auswertungs-Tabelle ziehen: (siehe Auswertung.xlsm)
[img]
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Erklärung zu den hochgeladenen Dateien: Die Datei "Quelldatei.xlsx" enthält die Zeitreihen-Daten in 3 beispielhaften Tabellenblättern und hat sonst keine weitere Funktion. Die Datei "Auswertung.xlsm" hat ein Blatt "Daten", worin die Daten(immre Spalte "2016") aus "Quelldatei" importiert werden sollen, alle nacheinander Spalte für Spalte. Falls es hilft, ist in dem Blatt "DatenParameter" die gleiche Tabelle nochmal, nur anstatt von normalen Tabellenköpfen, der Name des jeweiligen Blattes woraus die Daten gezogen werden sollen.

Das wollte ich mit einer Abfrage(Query) umsetzen. Allerdings müsste ich dann für jedes einzelne Worksheet (über 50) eine neue Abfrage einrichten. Daher dachte ich wäre es sinnvoll dies mit VBA zu automatisieren. Allerdings schaffe ich es nicht alles, was variabel sein soll, mit Variablen zu bestücken, d.h. dass Name des Worksheets und Spaltename automatisch verändert werden können.

Code:

   ActiveWorkbook.Queries.Add Name:="RoE", Formula:= _
        "let" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:UsersMaxOneDriveFinanzenReutersProjekttimeseries request neu.xlsm""), null, true)," & Chr(10) & "    Sheet = Source{[Item=""RoE"",Kind=""Sheet""]}[Data]," & Chr(10) & "    PromoteHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])," & Chr(10) & "    RemoveColumns = Table.SelectColumns(PromoteHeaders,{""2016""})" & Chr(10) & "in" & Chr(10) & "    RemoveColumns"
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=RoE", _
        Destination:=Range("$D$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [RoE]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = False
        .ListObject.DisplayName = "RoE"
        .Refresh BackgroundQuery:=False
    End With

In dem Fall heißt das Worksheet "RoE", weshalb ich versuchen wollte überall wo dieser Name steht, eine Variable "shName" einzusetzen. Allerdings scheitert dies scheinbar beim

Code:

Location=RoE

und 
   

Code:

.CommandText = Array("SELECT * FROM [RoE]")

  Auswertung.xlsm (Größe: 31,47 KB / Downloads: 2)

  Quelldatei.xlsx (Größe: 23,95 KB / Downloads: 4)

Ich hoffe ihr könnt mir dabei helfen.
Vielen Dank im Voraus!

PS: Ist das überhaupt die beste, vorallem schnellste Möglichkeit um über 100MB Daten zu laden, denn Excel läd ja trotzdem immer die ganze Tabelle auch wenn nur eine spezielle Spalte benötigt wird.

Edit: Beispieldatei hinzugefügt.

[shift-del]

Hallo

Der Datenaufbau erinnert mich diese Videos:

Mr Excel & excelisfun Trick 174: Transform Data Set: Advanced Filter? Or Power Query?
https://www.youtube.com/watch?v=JBSDZl7mB7I

Excel Magic Trick 1242: Transform Large Data Set to Final GDP Report: TTC, MATCH, Filter & Format
https://www.youtube.com/watch?v=IZtYysDCyps

Bill Szysz at YouTube: Power Query - Duel 174
https://www.youtube.com/watch?v=Zw9EyVVtGLA

Excel Magic Trick 1243: Transform Data Set: Power Query 2nd Method or Advanced Filter?
https://www.youtube.com/watch?v=dk7-Ana8qZc

Excel Magic Trick 1244: Transform GDP Data Set: Advanced Filter with Formula Criteria wins!
https://www.youtube.com/watch?v=tdNkFEt4NmI

[steve1da]

Hallo la,

zur Info:

http://ms-office-forum.net/forum/forumdisplay.php?f=29
Gruß,
steve1da

[mhryvo]

Der Datenaufbau erinnert mich diese Videos:

Danke für die vielen Videos!
Ich weiß ja wie ich die Daten bekomme mit PowerQuery. Allerdings geht es mir darum, dass mit etwa 50 Zeitreihen mit jeweils über 10000 Zeilen automatisiert zu machen, also nicht jedes mal eine Abfrage selbst erstellen oder duplizieren muss, sondern Name des Sheets und Spaltename variabel setzen kann, sodass ich das 50 mal mit VBA Loop durchlaufen lassen kann. Allerdings schaffe ich es nicht manche Werte variabel zu setzen. Da liegt mein Problem. Und "einfaches" kopieren lassen von VBA funktioniert wohl bei solchen großen Datenmengen nicht so gut und vorallem nicht schnell.

[GMG-CC]

[Sarkasmus] In der Tat, 50 Abfragen zu erstellen ist wirklich eine unzumutbare Aufgabe ... [/Sarkasmus]
Jeder Dienstleister wird dir die Arbeit gewiss gerne abnehmen!
Oder du arbeitest dich in VBA mehr ein, aber das kostet ja auch wieder

[shift-del]

sodass ich das 50 mal mit VBA Loop durchlaufen lassen kann.

Warum VBA wenn es doch auch mit Power Query geht?

[mhryvo]

Ja, ich werde dafür aber keinen Dienstleister nutzen  :19:
Und ja ich arbeite mich in VBA ein, aber ich will nicht sinnlos in der Theorie lernen, sondern damit Aufgaben bewerkstelligen und das versuche ich gerade.
Eigentlich ist meine Frage ja erstmal ganz einfach. Ich frage erstmal ja nur danach, ob es möglich ist, die beiden von mir zitierten Punkte im Code mit Variablen zu bestücken oder nicht.
Wenn das möglich ist, dann sollte ich es schaffen, dass ganze in einer Loop zum laufen zu bringen.

Warum VBA wenn es doch auch mit Power Query geht?

Kann man mit PowerQuery auch Schleifen schreiben, sodass das ganze mehrmals ausgeführt ist? Weil es sind ja wirklich immer die gleichen Abfragen nur mit anderen Blattnamen und evtl. Spaltennamen.
Ich dachte eigentlich, dass mein Konstrukt eine Art Powerquery erstellt? Ich habe ja auch mit PowerQuery die Abfrage so eingestellt, dass die richtige Spalte ausgegeben wird. 

Also nochmal zur Info: Der obengenannte Code erstellt die Abfrage mit meiner PowerQuery Formel, sodass meine gewünschte Spalte ausgegeben wird. Allerdings ja nur für das Worksheet was ich angegeben habe.
Allerdings will ich das ja für alle weitere Worksheets in den darauffolgenden Spalten (in der Auswertungsdatei) auch durchführen.

Allerdings kommt mir gerade die Idee, ob man mit PowerQuery-Editor während man ein Worksheet gefiltert hat, auch auf die anderen Worksheets zugreifen kann?

[Kuwer]

Hallo,

Eigentlich ist meine Frage ja erstmal ganz einfach. Ich frage erstmal ja nur danach, ob es möglich ist, die beiden von mir zitierten Punkte im Code mit Variablen zu bestücken oder nicht.

Code:

 Dim strName As String
 strName = "RoE"

  ActiveWorkbook.Queries.Add Name:="RoE", Formula:= _
       "let" & Chr(10) & "    Source = Excel.Workbook(File.Contents(""C:UsersMaxOneDriveFinanzenReutersProjekttimeseries request neu.xlsm""), null, true)," & Chr(10) & "    Sheet = Source{[Item=""RoE"",Kind=""Sheet""]}[Data]," & Chr(10) & "    PromoteHeaders = Table.PromoteHeaders(Sheet, [PromoteAllScalars=true])," & Chr(10) & "    RemoveColumns = Table.SelectColumns(PromoteHeaders,{""2016""})" & Chr(10) & "in" & Chr(10) & "    RemoveColumns"
   With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
       "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & strName, _
       Destination:=Range("$D$1")).QueryTable
       .CommandType = xlCmdSql
       .CommandText = Array("SELECT * FROM [" & strName & "]")

Gruß Uwe

[mhryvo]

Ah sehr gut, genau das wollte ich wissen. Habe schon gemerkt, dass man irgendwie anders deutlich machen muss, dass es Variablen sind und kein Text/strings. Danke! Werde ich gleich mal ausprobieren.

[shift-del]

Mit "Daten aus Arbeitsmappe" wähle ich die Arbeitsmappe und die Tabellenblätter aus.
Mit "Abfragen als neu anfügen" kommen alle Einzel-Tabellen in eine Tabelle.
Dann kommt die Feinarbeit.
Z.B. die Spalte A splitten, NA durch null ersetzen, die Jahres-Spalten entpivotisieren, die Fehlerzeile entfernen, Überschriften ändern.
Dann habe ich eine Tabelle mit 497 Zeilen.

	A	B	C	D
1	Name	Typ	Jahr	Wert
2	11 88 0 SOLUTIONS	EARNINGS PER SHR	2000	0,18
3	11 88 0 SOLUTIONS	EARNINGS PER SHR	2001	0
4	11 88 0 SOLUTIONS	EARNINGS PER SHR	2002	0
5	11 88 0 SOLUTIONS	EARNINGS PER SHR	2003	0
6	11 88 0 SOLUTIONS	EARNINGS PER SHR	2004	0,62
7	11 88 0 SOLUTIONS	EARNINGS PER SHR	2005	1,41
8	11 88 0 SOLUTIONS	EARNINGS PER SHR	2006	0,79
9	11 88 0 SOLUTIONS	EARNINGS PER SHR	2007	0,75
10	11 88 0 SOLUTIONS	EARNINGS PER SHR	2008	1,52
11	11 88 0 SOLUTIONS	EARNINGS PER SHR	2009	1,18
12	11 88 0 SOLUTIONS	EARNINGS PER SHR	2010	0,53
13	11 88 0 SOLUTIONS	EARNINGS PER SHR	2011	0,14
14	11 88 0 SOLUTIONS	EARNINGS PER SHR	2012	0,13
15	11 88 0 SOLUTIONS	EARNINGS PER SHR	2013	2,46
16	11 88 0 SOLUTIONS	EARNINGS PER SHR	2014	0
17	11 88 0 SOLUTIONS	EARNINGS PER SHR	2015	0
18	11 88 0 SOLUTIONS	EARNINGS PER SHR	2016	0
19	11 88 0 SOLUTIONS	EARNINGS PER SHR	2017	0
20	2G ENERGY	EARNINGS PER SHR	2011	1,44
21	2G ENERGY	EARNINGS PER SHR	2012	1,52
22	2G ENERGY	EARNINGS PER SHR	2013	2,58
23	2G ENERGY	EARNINGS PER SHR	2014	0,2

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Die Schreibweise der Firmennamen ist allerdings nicht identisch.