Herbers Excel-Forum - das Archiv

VBA, Datenfeld aus mehereren Tabellenblättern

Bild

Betrifft: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter Eckel

Geschrieben am: 08.01.2007 21:06:12
Hallo Leute,
gestern habe ich Euer Forum intensivst durchforstet, aber nichts passendes gefunden.
Problem: Ich möchte alle Tabellenblätter meiner Exceldatei in ein Datenfeld einlesen. Bisher habe ich das folgendermaßen gemacht (Auszug):
ZeileAnz=5000
For BlattZähler = 0 To BlattAnz
With Worksheets("BlattZähler")
' Bereich je Blatt festlegen
SpalteAnz = .Cells(1, Columns.Count).End(xlToLeft).Column
Bereich = .Range(.Cells(1, 1), .Cells(ZeileAnz, SpalteAnz))
For ZeileZähler = 1 To ZeileAnz
For SpalteZähler = 1 To SpalteAnz
' Werte einlesen
ARRAY(ZeileZähler, SpalteZähler + SpalteGesamt) = _
Bereich(ZeileZähler , SpalteZähler)
Next SpalteZähler
Next ZeileZähler
End With
SpalteGesamt = SpalteGesamt + SpalteAnz - 1
Next BlattZähler
Das ganze dauer allerdings bei ca. 20 Blättern mit den beiden geschachtelten Schleifen eine ganze Zeit (ca. 20 sekunden).
Frage: Wie geht das ganze schneller???
Mit dem Union-Befehl die Bereiche zusammenfügen und den gesamten Bereich einem Datenfeld zuweisen klappt nicht, da sich der Union-Befehl wohl nur auf ein Tabellenblatt beziehen darf...
Aber das muss doch irgendwie schneller machbar sein...
Bin für jede Hilfe dankbar
Peter
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Ramses

Geschrieben am: 08.01.2007 21:49:29
Hallo
das ganze ist ja recht hübsch, ... aber völlig unübersichtlich und konfus.
Du solltst dir zwingend angewöhnen mit "Option Explicit" zu arbeiten.
Was soll das mit dem Bereich ?
"...Ich möchte alle Tabellenblätter meiner Exceldatei in ein Datenfeld einlesen...."
Was willst du denn damit eigentlich erreichen ?
Gruss Rainer
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter

Geschrieben am: 08.01.2007 22:06:25
Hallo,
das mit Option Explicit mach ich auch - ich habe eben nur einen Teil aus dem Makro rauskopiert (deshalb habe ich AUSZUG davorgeschrieben...) .
Ich habe in den verschiedenen Tabellenblättern Werte aus verschiedenen Jahren stehen (je 1 Jahr = 1 Tabellenblatt).
Um das Finden und Auswerten ausgeählter Daten zu vereinfachen, habe ich versucht das ganze in ein Datenfeld einzulesen und die gesuchten Werte später in ein neues Tabellenblatt zu schreiben. Auf diese neue Tabellenblatt verweisen dann auch alle Diagramme...
Also je Tabellenblatt das Range einem Bereich zuweisen und dann diesen Bereich für älle Zeilen und alle Spalten in das Datenfeld übernehmen - dann zum nächsten Datenblatt gehen und das Ganze wiederholen...
Vielleicht ist es jetzt etwas klarer - vielleicht auch nicht -besser gebt's kaum...
Markus
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Ramses

Geschrieben am: 08.01.2007 22:35:46
Hallo
"...das mit Option Explicit mach ich auch ..:"
Glaub ich ehrlich gesagt nicht, weil EXCEL sonst bei dieser Zeile
Bereich = .Range(.Cells(1, 1), .Cells(ZeileAnz, SpalteAnz))
meckern würde.
Das ist eine Object-Zuweisung und müsste regulär zumindest lauten
Set Bereich = .Range(.Cells(1, 1), .Cells(ZeileAnz, SpalteAnz))
Und bei dieser Zeile
Bereich(ZeileZähler , SpalteZähler)
Wird der Bereich (Range) wieder neu definiert ?
Wie ist das Datenfeld "Array" deklariert ?
Du gehst doch durch keine Worksheets, sondern immer nur durch das Tabellenblatt
With Worksheets("BlattZähler")
bzw. die aktive Tabelle. Es wird doch auf kein anderes Tabellenblatt in deinem AUSZUG verwiesen.
Tut mir leid ich blick bei deinem Code-AUSZUG leider nicht durch.
Daher die Frage auf offen.
Grundsätzlich stelle ich die Frage:
Warum den komplizierten Konstrukt mit einem Array,... den ich ehrlich gesagt nicht verstehe....
ARRAY(ZeileZähler, SpalteZähler + SpalteGesamt)
Da müsste das Array ja in etwa so deklariert sein
Array(5000,1,1,1,1,1,1. usw.) bis 255 Spalten
...und kein direktes kopieren des Bereiches in das Zieltabellenblatt
Sub Demo()
Dim wksArray()
Dim wks As Worksheet, homeWks As Worksheet
Dim i As Integer, startCol As Integer, endCol As Integer
Dim rowCounter As Long
startCol = 1
wksArray = Array("Tabelle1", "Tabelle2")
Set homeWks = Worksheets("Blattzähler")
'Berechnung abschalten
Application.Calculation = xlCalculationManual
For i = 0 To UBound(wksArray)
MsgBox Worksheets(i + 1).Cells(1, 1)
'den Bereich direkt kopieren
With Worksheets(i + 1)
.Range(.Cells(1, startCol), .Cells(1, endCol)).Copy homeWks.Cells(rowCounter, 1)
End With
rowCounter = rowCounter + 1
Next
'Berechnung wieder einschalten
Application.Calculation = xlCalculationAutomatic
End Sub

Das geht mit Sicherheit schneller als die unzähligen Schleifen in deinem Code
Meine Schleife ist nicht korrekt, du müsstest deine Variablen "Zeilenzähler" und "Spaltenzähler" natürlich noch einbauen für das kopieren
Gruss Rainer
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter

Geschrieben am: 08.01.2007 23:06:55
Danke für die Mühe
Vielleicht mach ich auch alles falsch - ich hab das GESAMTE Makro nochmal rauskopiert - vielleicht wird dann einiges klarer (siehe unten).
In Deinem Makro verstehe ich
.Range(.Cells(1, startCol), .Cells(1, endCol)).Copy homeWks.Cells(rowCounter, 1)
nicht. Aber ich glaube wir kommen der Sache näher. Deine Fragen sind mir ein wenig zu kompliziert - ich verstehe sie teilweise nicht. Deswegen hier also nochmal das GESAMTE Makro (Da ich so was das erste Mal mache bitte ich um Nachsicht - man lernt schließlich aus seinen Fehlern...)
Danke nochmal...
Sub M_280a(AusgRück As Variant, Druck As Boolean, Fehler As Boolean, AoR As String)
' Anzahl der Blätter für Ausgabe- bzw Rücknahmewerte ermitteln
Dim BlattZähler As Variant
Dim BlattAnz As Integer
For Each BlattZähler In Worksheets
If IsNumeric(Mid(BlattZähler.Name, 2)) = True Then BlattAnz = BlattAnz + 1
Next BlattZähler
BlattAnz = BlattAnz / 2
' Anzahl der Zeilen im letzten Rücknahmeblatt ermitteln
Dim ZeileAnz As Integer
ZeileAnz = Worksheets(CStr(AoR) + "0" + CStr(BlattAnz - 1)).Cells(Rows.Count, 1).End(xlUp).Row
' Anzahl der Spalten über alle Aus- bzw- Rücknahmeblätter ermiteln
Dim SpalteAnz As Integer
For BlattZähler = 1 To BlattAnz
With Worksheets(CStr(AoR) + "0" + CStr(BlattZähler - 1))
SpalteAnz = SpalteAnz + .Cells(9, Columns.Count).End(xlToLeft).Column - 1
End With
Next BlattZähler
' Feld erstellen
ReDim AusgRück(9 To 10, SpalteAnz)
' Alles einlesen
Dim Bereich
Dim ZeileZähler As Integer
Dim SpalteZähler As Integer
Dim SpalteGesamt As Integer
SpalteGesamt = 0
For BlattZähler = 0 To BlattAnz - 1
With Worksheets(CStr(AoR) + "0" + CStr(BlattZähler))
' Bereich je Blatt festlegen
SpalteAnz = .Cells(9, Columns.Count).End(xlToLeft).Column
Bereich = .Range(.Cells(9, 1), .Cells(ZeileAnz, SpalteAnz))
' Datum einlesen
For SpalteZähler = 2 To SpalteAnz
AusgRück(9, SpalteZähler - 1 + SpalteGesamt) = Bereich(1, SpalteAnz - SpalteZähler + 2)
Next SpalteZähler
For ZeileZähler = 11 To ZeileAnz
' WKN einlesen
AusgRück(ZeileZähler, 0) = Bereich(ZeileZähler - 8, 1)
' Werte einlesen
For SpalteZähler = 2 To SpalteAnz
AusgRück(ZeileZähler, SpalteZähler - 1 + SpalteGesamt) = Bereich(ZeileZähler - 8, SpalteAnz - SpalteZähler + 2)
Next SpalteZähler
Next ZeileZähler
weiter2:
End With
SpalteGesamt = SpalteGesamt + SpalteAnz - 1
Next BlattZähler
If Druck <> False Then Call M_80Druck(AoR, BlattAnz, AusgRück)
End Sub

Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Ramses

Geschrieben am: 08.01.2007 23:27:07
Hallo
"...Da ich so was das erste Mal mache bitte ich um Nachsicht ..."
Das steht im krassen Widerspruch zum Aufbau dieses Makros
Auch die Aussage
"...Mit dem Union-Befehl die Bereiche zusammenfügen..."
findet man normalerweise nicht im Vokabular eines Anfängers.
Das ganze ist nur sehr schwierig nachzuvollziehen und ohne Vorlage fast unverständlich was dort alles zusammengetragen wird.
Probiers doch einfach mal mit
Application.Calculation = xlCalculationManual
am Anfang deines Codes und
Application.Calculation = xlCalculationAutomatic
am Ende deines Codes. Das sollte schon mal Zeit sparen.
Ausserdem wird mit dem ganzen Array in deinem Code nichts gemacht sondern an
If Druck <> False Then Call M_80Druck(AoR, BlattAnz, AusgRück)
an die Prozedur "M_80Druck" übergeben und dort weiterverarbeitet.
Also müsste man sich den GANZEN Code anschauen und prüfen wo man optimieren kann.
Gruss Rainer
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter

Geschrieben am: 08.01.2007 23:43:54
Hallo,
1. Das ist wirklich meine Erste Datei in der ich Makros schreibe (allerdings jetzt schon ein paar Wochen...
2. Das mit dem Union- Befehl habe ich gestern in diesem Forum gelesen und versucht auszuprobieren - hat aber nicht geklappt...
3. Das mit
Application.Calculation = xlCalculationManual
und
Application.Calculation = xlCalculationAutomatic
beschleunigt das Makro auf die Hälfte der zeit -das ist schon super...
4. Dieses Makro wird von verschiedenen andern Makros aufgerufen und dient eigentlich nur zum Auslesen der Werte aus den Tabellenblättern. "Ausgeben" mit Call M_80Druck hab ich nur zur Kontrolle eingebaut, ob die Werte richtig eingelesen wurden. Normalerweise werden die Daten an das "übergeordnete" Makro zurückgegeben und weiterverarbeitet/verändert und ggf in einem anderen Tabellenblatt ausgegeben.
Die Hälfte der Zeit gespart - das reicht mir erst mal...
Geht's denn aber noch schneller???
VIELEN DANK und GUTE NACHT
Peter
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Ramses
Geschrieben am: 09.01.2007 08:04:55
Hallo
Wenn du es noch weiter beschleunigen willst, dann versuch doch mal meine Schleife anzupassen die ich dir im vorherigen Beitrag gezeigt habe.
Das ist mit Sicherheit dann nochmals minimum um 50% schneller.
Gruss Rainer
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter

Geschrieben am: 09.01.2007 10:09:25
Hallo,
Dein Makro habe ich zwischenzeitlich verstanden. Das funktioniert.
Nur kopierst Du ja das Range der einzelnen Tabellenblätter in homeWks rein.
Ich wollte eigentlich das Range in ein Datenfeld haben. Beispiel:
Tabellenblatt1
A B C
1 123 568 987
2 456 987 561
3 789 896 348
Tabellenblatt2
A B C
1 1 2 3
2 4 5 6
3 7 8 9
Das Ergebnis soll ein Datenfeld (Dim AusgRück(1 To 3, 1 to 6)) sein, das folgendermaßen aussieht:
1 2 3 4 5 6
1 123 568 987 1 2 3
2 456 987 561 4 5 6
3 789 896 348 7 8 9
Die Tabellenblätter umfassen ca. 250 Spalten und 500 Zeilen. Ich habe gelesen, das das
neue Excel 2007 16000 Spalten spalten. Dann könnte ich alles hintereinanderkopieren - klar...
Aber so???
Gruß
Peter
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Ramses

Geschrieben am: 09.01.2007 11:16:34
Hallo
Die Frage ist:
Was machst du mit den Daten im Array ?
Du schreibst sie doch wohin,.. oder ? Die Frage ist wohin.
Der Kopiervorgang geht ohne Schleifen. Deine Schleifen (5000 Zeilen + x Spalten) multiplizieren sich gewaltig.
Anstelle von vielleicht 10 Kopiervorgängen (Range("A1:IV500").Copy) machst du bei nur 10 Spalten bereits 50'000 !!! Durchgänge.
Gruss Rainer
Bild

Betrifft: AW: VBA, Datenfeld aus mehereren Tabellenblättern
von: Peter

Geschrieben am: 09.01.2007 11:27:41
Hallo,
Die Daten im Array werden weiterverarbeitet (miteinander mutipliziert, addiert usw) und nur ein paar Werte (Ergebnisse aus den v.g. Berechnungen) werden dann ausgegeben.
Ja, Kopiervorgänge gehen wesentlich schneller - das habe ich verstanden. Wenn ich alle Daten hintereinanderkopieren könnte wäre das toll. Aber meine Excel-Version hat eben nur 256 Spalten - und da passen meine 10 Blätter mit ca. 250 Spalten und 5000 Zeilen eben nicht rein.
Ich brauch das Datenfeld eben zum "organisieren" und Ausfiltern der Daten...
Gruß
Peter
 Bild
Excel-Beispiele zum Thema "VBA, Datenfeld aus mehereren Tabellenblättern"
Wert in Array suchen, ohne jedes Datenfeld abzufragen