Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1396to1400
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Performance verbessern?

Performance verbessern?
09.12.2014 14:01:14
Precog
Hallo,
ich habe gerade mit der VBA-Programmierung angefangen und mein erstes Skript fertiggestellt. Es tut genau das, was es soll, allerdings sehr langsam. Insgesamt sind ca. 1000 Dateien einzulesen, Daten in neue Dateien zu kopieren und wieder abzuspeichern. Gibt es für euch auf den ersten Blick "Problempunkte" in diesem Skript, die den Ablauf wesentlich verlangsamen?
Vielen Dank für die Hilfe!

Sub createLists()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim startDate As Date
Dim endDate As Date
Dim dateLooper As Date
Dim currDate As String
'Set date range for existing files
startDate = #1/1/2012#
endDate = #9/12/2014#
'Array, which contains names for new worksheets as strings
Dim tsN(1 To 12) As String
tsN(1) = "AA11"
tsN(2) = "AA22"
tsN(3) = "AA33"
tsN(4) = "AA44"
tsN(5) = "AA55"
tsN(6) = "AA66"
tsN(7) = "BB11"
tsN(8) = "BB22"
tsN(9) = "BB33"
tsN(10) = "BB44"
tsN(11) = "BB55"
tsN(12) = "BB66"
Dim w1 As Workbook
Dim w2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim localPath As String
localPath = ThisWorkbook.path
'Check for folder "Lists", create if non-existend
Dim fso, folderN
Set fso = CreateObject("Scripting.FileSystemObject")
folderN = localPath & "\Lists\"
If fso.FolderExists(folderN) = False Then MkDir folderN
For dateLooper = startDate To endDate
currDate = Format(dateLooper, "yyyy-mm-dd")
'Open / create workbook objects
Set w1 = Workbooks.Open(Filename:=localPath & "\roh\daten" & currDate & ".CSV", Local:=  _
_
_
_
_
True)
Set w2 = Workbooks.Add()
'Array, which contains worksheet-objects which will reference new worksheets
Dim ts(1 To 12) As Worksheet
'Create new file and add/name new worksheets, set references to array ts
For i = 1 To 12
With w2.Sheets.Add()
.Name = tsN(13 - i)
.Activate
End With
If i = 1 Then w2.Worksheets(2).Delete
Set ts(13 - i) = ActiveSheet
Next i
'Copy data:
Set ws1 = w1.Sheets(1)
'Iterate through products and copy corresponding data to seperate sheets in prev.  _
created new file
For i = 1 To 12
Set ws2 = ts(i)
'Filter data for product
ws1.Range("A1:H1").AutoFilter Field:=2, Criteria1:="=" & tsN(i)
ws1.Range("A1:H1").AutoFilter Field:=7, Criteria1:="=ja"
'Select range and copy
Dim lastRow As Long
lastRow = ws1.UsedRange.Rows.Count
ws1.Range("A1:H" & lastRow).Copy ws2.Range("A1:H1")
'Sort copied data
With ws2
.Range("A1").Sort Key1:=.Range("D1"), Order1:=xlAscending, DataOption1:= _
xlSortNormal, Header:=xlYes
End With
ws1.AutoFilterMode = False
Next i
'Save newly created file
w2.SaveAs Filename:=localPath & "\Lists\Lists-" & currDate & ".xls", FileFormat:= _
xlNormal
w2.Close
w1.Close
Next dateLooper
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

3
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Performance verbessern?
09.12.2014 14:11:28
Daniel
Hi
eine Optimerungsmöglichkeit wäre, das Blatt ws1 vor der Schleife nach den Filterspalten zu sortieren, so dass die in der Schleife gefilterten und kopierten Zeilen einen lückenlos zusammenhängenden Block bilden.
Dann geht das Kopieren schneller, als wenn die Zeilen auseinander stehen.
Wenn du gleichzeitig nach Spalte D sortierst, kann das Sortieren in den Eingefügten Daten auf ws2 entfallen (dh du musst dann auch viel weniger sortieren, weil das Sortieren ausserhalb der Schleife passierern sollte)
Gruß Daniel

AW: Performance verbessern?
09.12.2014 15:05:15
fcs
Hallo,
weitere Optimierung ergänzend zu Daniels:
die Datei mit den 12 leeren Tabellenblättern wird nicht in jedem Drchlauf neu zusammengebastelt, sondern zu Beginn einmal als fertiges Muster gespeichert und in der Schleife wird dann immer jeweils das Muster geöffnet.
Auf das Array
Dim ts(1 To 12) As Worksheet
mit den Worksheets in w2 kann man verzichten. Die Zieltabelle kann hier auch wie folgt gesetzt werden:

Set ws2 = w2.Worksheets(tsN(i))
statt
Set ws2 = ts(i)
Formeln spielen in deinen Dateien -soweit ich das erkennen konnte- keine Rolle. Somit gewinnt man wahrscheinlich keine Geschwindigkeit, wenn der Berechnungsmodus wärend der Ausführung des Makros auf "manuell" gesetzt wird.
Ansonsten:
1000 Dateien sind halt auch eine Menge Zeug. Bei 1 bis 2 Sekunden pro Datei hast du schon mal ca. 30 Minuten Laufzeit für die Bearbeitung.
Gruß
Franz

Anzeige
zwar nicht zur Performance ...
09.12.2014 15:47:57
Rudi
Hallo,
aber Variablendeklarationen immer komplett an den Anfang des Codes.
Erhöht die Übersichtlichkeit.
Gruß
Rudi

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige