Office 2016 Mehrere Bereiche aus geschlossener Datei importieren

[MiSpi]

Guten Tag, liebe Excel-Freunde,

ich scheitere an einem Makro, mithilfe dessen ich aus einer* geschlossenen Datei mehrere Bereiche auslesen und in die aktuelle Arbeitsmappe einfügen kann.
(*vorerst eine Datei - später sollen es einige hundert Dateien in einem vorgegebenen Ordner sein...)

Die Auszulesenden Bereiche in der Datei  "Auszulesende_Datei.xlsx" befinden sich in:
C50:H58, C100:H108, C150:H158 - also mit vielen Leerzeilen dazwischen, die ich nicht mit übernehmen will.

Einfügen mag ich sie gerne in die Bereiche (jeweils nur 10 Zeilen):
C1:H9, C11:H19, C21:H29 usw.

Was ich mir bis jetzt zusammengesucht und geschrieben habe, ist das Untenstehende:
Da fehlt, wie ihr seht, jedoch noch das Wichtigste, nämlich dass mithilfe mehrerer Loops die verschiedenen Bereiche (insg. 15 Bereiche pro Datei) ausgewählt und importiert werden.

Hat jemand eine Idee, wie man das am besten in VBA umsetzten kann?
Vielen Dank für eure Vorschläge!
Michael


Private Function GetValue(pfad, datei, blatt, zelle)
'** Daten aus geschlossener Arbeitsmappe auslesen

'*** Dimensionierung der Variablen
Dim arg As String

'** Das Argument erstellen
arg = "'" & pfad & "[" & datei & "]" & blatt & "'!" & Range(zelle).Range("A1").Address(, , xlR1C1)

'** Auslesen ?ber Excel4Macro
GetValue = ExecuteExcel4Macro(arg)

End Function
'----------------------------------------

Sub Mehrere_Bereiche_aus_geschlossener_Datei_importieren()
'** Dimensionierung der Variablen
Dim pfad As String, datei As String, blatt As String, bereich As Range, zelle As Object

'** Angaben zum auszulesenden Bereich
pfad = "D:\Test_Daten\"
datei = "Auszulesende_Datei.xlsx"
blatt = "Eingabe Heizung und Strom"
Set bereich = Range("C50:H58")


'** Bereich auslesen
For Each zelle In bereich

    '** Zellen umwandeln
   zelle = zelle.Address(False, False)
 
'** Eintragen in Bereich
ActiveSheet.Cells(zelle.Row, zelle.Column).Value = GetValue(pfad, datei, blatt, zelle)

Next zelle

End Sub

[gmg-cc]

Moin,
wenn du das Ergebnis wie im Anhang haben willst (ja, auch für hunderte Files in 1 Folder) dann geht das ohne eine Zeile VBA ... (Aber natürlich auch mit  )
Ich bin über Daten | Abrufen und transformieren gegangen, die geschlossene Quelldatei liegt in C:\Daten.

[MiSpi]

Lieber Günther,
vielen Dank für den wertvollen Hinweis!

Daten Abrufen ist wirklich ein großartiges Tool, das alles viel leichter macht 
Nur zu empfehlen an alle, die ähnliche Probleme haben!

Zwei Fragen hätte ich noch:
1. Funktioniert das Auslesen mehrer Dateien mit einem Klick, indem man bei "Neue Quellen" - "Datei" - "Ordner" den entsprechenden Ordner auswählt (in dem nur die auszulesenden Dateien zu finden sind)? Oder gibt es auch andere Wege?

2. Kann man einstellen, dass jeder weitere importierte Bereich (aus jeder weiteren Datei) je bei einer bestimmten Zelle anfängt? Also dass man zum Beispiel in Spalte A in die Zellen A2, A202, A402,... den Namen der auszulesenden Datei schon vorher reinschreibt und Excel dann die importierten Bereiche daneben in den Zellen B2, B202, B402,... starten lässt?

Das wäre noch klasse!
Danke, wenn sich noch wer der Fragen annimmt 
Liebe Grüße

[gmg-cc]

Daten Abrufen ist wirklich ein großartiges Tool, das alles viel leichter macht 
Nur zu empfehlen an alle, die ähnliche Probleme haben!

In der Tat! Ein Hinweis ist mir aber in diesem Zusammenhang wichtig: Wenn du im Internet dazu etwas suchst, wirst du nicht wirklich viel finden, wenn du den obigen Begriff verwendest. Der alte (und imho auch bessere) Name ist Power Query, und da findest du sehr viel deutsche und noch mehr englische Quellen.

Zwei Fragen hätte ich noch:
1. Funktioniert das Auslesen mehrer Dateien mit einem Klick, indem man bei "Neue Quellen" - "Datei" - "Ordner" den entsprechenden Ordner auswählt (in dem nur die auszulesenden Dateien zu finden sind)? Oder gibt es auch andere Wege?

Der typische Weg ist erheblich eleganter: Power Query liest von alleine alle Files eines Directory ein. Schau beispielsweise mal hier nach: http://www.excel-ist-sexy.de/kategorie/power-query/daten-zusammenfuehren/ oder etwas spezieller hier: http://www.excel-ist-sexy.de/alle-xlsx-eines-ordners-importieren/.

2. Kann man einstellen, dass jeder weitere importierte Bereich (aus jeder weiteren Datei) je bei einer bestimmten Zelle anfängt? Also dass man zum Beispiel in Spalte A in die Zellen A2, A202, A402,... den Namen der auszulesenden Datei schon vorher reinschreibt und Excel dann die importierten Bereiche daneben in den Zellen B2, B202, B402,... starten lässt?

Ein klares "Jein", denn dazu gehört schon "etwas" Erfahrung. Was aber problemlos geht: Wenn die zu importierenden Bereiche mit Bereichsnamen versehen sind, dann ist das recht einfach. PQ kann auch benannte Bereiche ansprechen. Wenn der Aufbau der zu importierenden Files sogar immer identisch ist, sollte eine pragmatische Lösung möglich sein. Laut nachgedacht: Alle Files importieren (lassen), zusammenfügen und dann den Zähl-Algorithmus aus der/meiner Muster-Lösung anwenden.

Ich weiß, praktisch ein Fullquote, aber so fällt es mir (trotz des sehr warmen Wetters) leichter, sinnvoll zu antworten.