VBA-script extrem langsam

Bild

Betrifft: VBA-script extrem langsam
von: Stephan
Geschrieben am: 23.10.2015 01:02:36

Guten Abend zusammen,
ich habe ein VBA-Script, welches mir CSV-Dateien aus einem Ordner in meinen Excel-Sheet importiert. Klappt soweit auch alles. Problem ist nur, wenn ich das Makro auf ein Tabellenblatt loslasse, welches diese Daten dann in anderen Tabellenblättern verarbeitet geht gar nichts mehr. Wenn ich ein neues Blatt anfüge und dort die Dateien importiere geht es, auch wenn es etwas langsam ist.
Hier mal ein Link zum Excel-file. Es ist größer als 300kb, deshalb ein Dropbox-Link:
https://www.dropbox.com/s/p1axloj7ubl447j/Probe_2_Spektrenauswertung_normiert.xlsm?dl=0
Und hier das Makro:


 Sub import_text()
            Dim fd As FileDialog
            Dim strFolder As String
            Dim strName As String
            Dim intCol As Integer
            Dim rngCell As Range
            Dim ws As Worksheet
            Dim varArr As Variant
            Dim i As Long
           Application.ScreenUpdating = False
            Set ws = ThisWorkbook.Sheets(1)
           
            Set fd = Application.FileDialog(msoFileDialogFolderPicker)
            If fd.Show <> -1 Then Exit Sub
           
            strFolder = fd.SelectedItems(1) & "\"
            strName = Dir(strFolder & "*.csv")
            Set rngCell = ws.Cells(2, Columns.Count)
           
            While Len(strName) > 0
               If IsEmpty(rngCell.End(xlToLeft).Value) Then
                   intCol = 1
               Else:
                   intCol = rngCell.End(xlToLeft).Column + 1
               End If
               Workbooks.OpenText Filename:=strFolder & strName, Local:=True
               ActiveSheet.UsedRange.Copy ws.Cells(2, intCol)
               ws.Cells(1, intCol).Value = strName
               ActiveWorkbook.Close SaveChanges:=False
               strName = Dir
               
               For i = 2 To ws.Cells(Rows.Count, intCol).End(xlUp).Row
                    varArr = Split(ws.Cells(i, intCol).Value, " ")
                    ws.Cells(i, intCol).Value = varArr(0)
                    ws.Cells(i, intCol + 1).Value = varArr(1)
               Next i
            Wend
           
            Set ws = Nothing
            Set fd = Nothing
            Set rngCell = Nothing
           
    Application.ScreenUpdating = True
    End Sub
Mein Excel-Level würde ich auf Durchschnittlich setzen. Mit VBA kann ich nur über den Recorder.
Vielen Dank schonmal für die Hilfe.
Viele Grüße!

Bild

Betrifft: AW: VBA-script extrem langsam
von: Daniel
Geschrieben am: 23.10.2015 01:17:01
Hi
ich vermute mal, dass die For-Next-Schleife in welcher du den Text noch mal splittest, die Bremse ist.
schmeiss die For-Schleife raus und wende stattdessen die Menüfunktion DATEN - DATENTOOLS - TEXT IN SPALTEN an, um den Text auf zwei spalten aufzuteilen.
damit kannst du die ganze Spalte in einem Schritt bearbeiten und das geht wesentlich schneller.
ums als VBA zu programmieren, musst du halt mal das von Hand ausführen und aufzeichnen.
Gruss Daniel

Bild

Betrifft: AW: VBA-script extrem langsam
von: Stephan
Geschrieben am: 23.10.2015 01:21:01
Hallo,
Du meinst das von Hand aufgezeichnete in das genante Script einfügen?


For i = 2 To ws.Cells(Rows.Count, intCol).End(xlUp).Row
Hier dann das aufgezeichnete Text in Spalten?              
               Next i
vielen Dank für die schnelle Antwort

Bild

Betrifft: AW: VBA-script extrem langsam
von: Daniel
Geschrieben am: 23.10.2015 01:27:10
jo, aber das For-Next muss natürlich auch noch weg.
und das aufgezeichneter Code immer der Überarbeitung bedarf ist dir ja hoffentlich klar.
Gruss Daniel

Bild

Betrifft: AW: VBA-script extrem langsam
von: Stephan
Geschrieben am: 23.10.2015 01:37:02
Und da geht's los:
Ich habe im Prinzip keine Ahnung wie ich das einbauen soll. Und da ja alle Daten aus einem Ordner eingelesen werden sollen, kann ich mir auch nicht vorstellen wie das ohne Schleife funktionieren soll. es muss ja nach jedem Einleseschritt die Text in Spalten funktion einen neuen Spaltenwert bekommen.
Das Spuckt mir der Recorder aus. Alles schön und gut, aber welche Variable jetzt wohin muss ist mir ein bisschen rätselhaft.
Vielleicht erbarmt sich ja jemand und hilft mir etwas auf die Sprünge.


                   Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Viele Grüße

Bild

Betrifft: AW: VBA-script extrem langsam
von: Daniel
Geschrieben am: 23.10.2015 07:29:01
Hi
Du hast bislang zwei Schleifen im Code
1. Die while-Schleife über alle Dateien im Ordner
2. Die For-Next-Schleife über alle Zeilen einer Datei
Die erste Schleife über die Dateien bleibt ja weiterhin erhalten, du ersetzt nur die Schleife über jede Zeile, weil du mit dem Text-In-Spalten alle Zeilen gleichzeitig mit einem Befehl bearbeiten kannst.
Statt der fixen Spalte A musst du im Code eben die Spalte variabel über deine Spaltennummer angeben:
Statt Columns("A:A") dann Columns(intCol) und beim Ziel (Destination) dann das gleiche mit Cella und wiefad geht siehst du ja in deinem eigenen Code.
Gruß Daniel

Bild

Betrifft: AW: VBA-script extrem langsam
von: Luschi
Geschrieben am: 23.10.2015 06:11:34
Hallo Stephan,
man sollte die folgenden Excel-Geschwindigkeitsbremsen abschalten, bevor man in Excelzellen Werte per Vba schreibt:
- Bildschirmaktualisierung
- Ereignisroutinen
- automatische Berechnung
- Cursoranzeige
Dafür gibt es in Excelkreisen folgende Prozedur:


Sub getMoreSpeed(bDoIt As Boolean)
    Application.ScreenUpdating = Not (bDoIt)
    Application.EnableEvents = Not (bDoIt)
    Application.Calculation = IIf(bDoIt, xlCalculationManual, xlCalculationAutomatic)
    Application.Cursor = IIf(bDoIt, 2, -4143)
End Sub

Rufe diese Sub wie folgt auf:
- zu Beginn:  Call getMoreSpeed(True)
- am Schluss: Call getMoreSpeed(False)
Ersetze also 2 mal Application.ScreenUpdating gegen diesen Aufruf.
Gruß von Luschi
aus klein-Paris

Bild

Betrifft: AW: VBA-script extrem langsam
von: Stephan
Geschrieben am: 23.10.2015 11:18:42
Vielen dank Luschi!
Das alleine beschleunigt die Sache schon enorm, bzw macht es jetzt erst möglich.
Die Geschichte von Daniel bekomme ich leider nicht hin.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "VBA-script extrem langsam"