Acc2013 - Excelprozedur aus Access sehr langsam!

[Gizzy3001]

Hallo zusammen,

ich habe es geschafft mit Hilfe eines Buttons den FileDialog aus Access zu starten, um eine Exceldatei auszuwählen. Nach der Auswahl wird eine Prozedur in dieser Exceldatei ausgeführt. Das Problem an der ganzen Sache ist die Dauer der Prozedur.

Die Prozedur fügt diverse Spalten hinzu, löscht Zeilen, fügt Werte ein etc. Wenn ich die Prozedur aus Excel öffnen ist es in nullkommanix fertig. Wenn ich jedoch über Access die Prozedur starte, dauert es mehrere Minuten. Fakt ist, dass ich keinen manuellen Aufwand mehr habe, jedoch würde ich mich echt freuen, wenn ich das Ergebnis deutlich schneller bekomme

Der FileDialog:

PHP-Code:

Public Function DateiAuswaehlen(Optional strPfad As String) As String
    Dim antw As String, int1 As Integer, Fd As FileDialog
    
    Set Fd = Application.FileDialog(msoFileDialogFilePicker)
    
    With Fd
        .AllowMultiSelect = False
        .Title = "Importdatei auswählen"
        .ButtonName = "Auswählen"
        
        If Len(strPfad) > 0 Then
            .InitialFileName = strPfad
        End If
        
        int1 = .Show
        
        If int1 <> 0 Then
            antw = .SelectedItems(1)
        Else
            antw = ""
        End If
    End With
    
    DateiAuswaehlen = antw
End Function 


Prozedur für das Öffnen der Exceldatei:

PHP-Code:

Private xlApp As Object
Private xlWB As Object
Private xlWS As Object

Sub ExcelAusAccess()

Dim strDatei As String

    Set xlApp = CreateObject("Excel.Application")
    
    strDatei = DateiAuswaehlen
    
    If Len(strDatei) = 0 Then: Exit Sub

    Set xlWB = xlApp.Workbooks.Open(strDatei)
    Set xlWS = xlWB.Worksheets(1)
    xlApp.Visible = True
    
    BereinigungDaten

    xlApp.Quit
    Set xlApp = Nothing

End Sub 


In der Prozedur "BereinigungDaten" werden die einzelnen Spalten, Zeilen und Zellen mithilfe von xlWS.rows, xlWS.columns bzw. xlWS.cells angesprochen. Ich glaube hier liegt auch das Problem. Die Dauer der Prozedur erhöht sich, da immer wieder der Pfad der Datei bis hin zur Spalte, Zeile, Zelle berücksichtigt wird. Kann es daran liegen?

Hat jemand einen Tipp für mich, wie ich mein Problem lösen kann?

Vielen lieben Dank!

[Nouba]

Poliere BereinigungDaten und lasse die Automatisierung versteckt ablaufen. Beim Polieren können Dir vermutlich die Leute im Excel-Forum am besten helfen.

[Gizzy3001]

Wenn ich die Auotmatisierung versteckt laufen lasse bleibt das Problem bestehen.

Natürlich könnte man die Prozedur noch polieren, jedoch kann ich nicht nachvollziehen warum die selbe Prozedur aus Excel heraus innerhalb von Sekunden durchläuft und aus Access mehrere Minuten.

[markusxy]

Zitat: von Gizzy3001

jedoch kann ich nicht nachvollziehen warum die selbe Prozedur aus Excel heraus innerhalb von Sekunden durchläuft und aus Access mehrere Minuten.

Der Unterschied ist aber gewaltig.
Vielleicht kannst du die Prozedur nach Excel verlegen und mittels application.Run von Access aus aufrufen.

Was natürlich negativ ist - du deklarierst die Excel Objekte als Object - verwendest also LateBinding - das kostet bei jedem Schritt zusätzlich Zeit - aber das sollte nicht so viel ausmachen.
Du könntest aber mal auf EarlyBinding umstellen um den Unterschied zu testen.

[ebs17]

Während beim offenen Excel Excel bereits geladen ist, wird beim Start über Early Binding Excel mit Start der Anwendung geladen, womit der Programmstart an sich länger dauert. Bei Late Binding (hier verwendet) wird Excel erst mit Aufruf gestartet, da hat man die benötigte Zeit dafür voll zu durchlaufen, wenn nicht gerade Reste aus einem vorherigen Aufruf noch im Cache liegen. Und das Excelobjekt ist kein kleines, da hat man schon ein paar Sekunden zu warten.

Ein weiterer Punkt könnte sein: Bei VBA-Maßnahmen schaltet man gerne so Sachen wie automatisches Neuberechnen von Zellen vorübergehend aus, damit Excel sich nicht in unnötiger Selbstbeschäftigung verliert.

Der gezeigte Filedialog hat nun mit den Laufzeitproblemen gar nichts zu tun, da musst Du nur schneller eintippen/klicken.

[Gizzy3001]

Mmh ... habe es jetzt mit dem EarlyBinding versucht. Leider kein Erfolg Dauert genauso lange ...

PHP-Code:

Private xlApp As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Sub ExcelAusAccess()

Dim strDatei As String

    Set xlApp = New Excel.Application
    strDatei = DateiAuswaehlen
    
    If Len(strDatei) = 0 Then: Exit Sub

    Set xlWB = xlApp.Workbooks.Open(strDatei)
    Set xlWS = xlWB.Worksheets(1)
    xlApp.Visible = True
    
    BereinigungDaten

    MsgBox "Fertig"
    xlApp.Quit
    Set xlApp = Nothing

End Sub 


[markusxy]

Hast du die Hinweise von Eberhard berücksichtigt?
Wie lange dauert das Laden der Datei -> Hast du mal eine Zeitnehmung gemacht?

Da wir den wesentlichen Code nicht sehen, wissen wir nicht mal wovon wir reden.
Grundsätzlich kann ich mir nicht vorstellen, dass der Code aus Access länger dauert - wenn er genau gleich ist, da der Code ja in jedem Fall von Excel verarbeitet wird - auch wenn er in Access läuft.

[ebs17]

Code:

BereinigungDaten

Man könnte auch erwarten, dass einige Zeit vergeht, weil der Accesscode darüber nachsinnen muss, was das denn für eine Prozedur ist, so ganz ohne Referenz auf die geöffnete Mappe, ganz zu schweigen von den vielfältigsten Sachen, die da drinn stecken mögen.

[Gizzy3001]

Sorry, kam erst jetzt dazu eine Nachricht zu schreiben. Der VBA-Code ist nicht der Beste, jedoch geht es hier um die Geschwindigkeit, welche vorhanden ist, wenn man den Prozedur aus Excel heraus startet:

PHP-Code:

Sub BereinigungDaten()

Dim intLetzteZeile As Integer, i As Integer, j As Integer

    xlWS.Columns("A:A").Insert Shift:=xlToRight
    xlWS.Cells(3, 1).Value = "Nummer"
    
    intLetzteZeile = xlWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    i = 0
    j = 3
    
    Do
        i = i + 1
        If xlWS.Cells(i, 2).Value = "Name" Then
            Do
                j = j + 1
                xlWS.Cells(j, 1).Value = xlWS.Cells(i, 3).Value
            Loop While Not (xlWS.Cells(j, 3).Value = "" Or j = intLetzteZeile)
        End If
        
    Loop While Not i = intLetzteZeile
    xlWS.Rows("1:2").Delete
    
    i = 1
    j = 1
    
    Do
        i = i + 1
        If xlWS.Cells(i, 2).Value = "Name" Then
            xlWS.Rows(i & ":" & i + 2).Delete
            i = i - 4
        End If
    Loop While Not i = intLetzteZeile
    
    xlWS.Columns("E:E").Replace " ", ""
    
    i = 0
        
    Do
        i = i + 1
        If xlWS.Cells(i, 3).Value = "" And xlWS.Cells(i + 1, 3).Value = "" And xlWS.Cells(i + 2, 3).Value = "" And xlWS.Cells(i, 1) = "" Then: Exit Sub

        If xlWS.Cells(i, 3).Value = "" Then
            xlWS.Rows(i & ":" & i).Delete
            i = i - 10
        End If
    Loop While Not i = intLetzteZeile

End Sub 


Habe mir nochmal den Code angeschaut. Ich glaube es liegt ggfs. an der folgenden Zeile: xlWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row

Ich werde versuchen eine Do-Loop-Schleife einzubauen, um meine Vermutung auszuschließen.

[Gizzy3001]

Hab es soeben mit einer Do-Loop-Schleife ausprobiert, um die letzte leere Zeile in der Excel zu finden. Das ganze dauert noch länger. Da der gewünschte Output erzielt wird und es aktuell nur an der Dauer liegt, werde ich das Projekt so weiterführen. Ggfs. werde ich mich zu einem späteren Zeitpunkt dem Problem wieder widmen. Danke für die Hilfe!

[Josef P.]

Hallo!

Wie hast du die Laufzeit gemessen?
Meiner Meinung nach sollte es ziemlich egal sein, ob die Prozedur in Access-VBA oder Excel-VBA durchgeführt wird.

Allg. Tipp zum Programmcode:
Ich vermeide globale Variablen in Standardmodulen und übergib die benötigte Referenz als Parameter in die Prozedur. Damit ist sichergestellt, dass nicht von anderer Stelle eine andere Instanz eingestellt wird.

Prinzip:

Code:

' Private xlApp As Object  ... nicht global definieren
' Private xlWB As Object
' Private xlWS As Object

Sub ExcelAusAccess()

dim xlApp As Excel.Application 'early binding möglich, wenn Verweis gesetzt ist, sonst bei "as Object bleiben
dim xlWB As Excel.Workbook
dim xlWS As excel.Worksheet

Dim strDatei As String

    strDatei = DateiAuswaehlen    
    If Len(strDatei) = 0 Then: Exit Sub

    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open(strDatei)
    Set xlWS = xlWB.Worksheets(1)
    xlApp.Visible = True
    
Debug.Print "Start:"; now()
    BereinigungDaten  xlWS
Debug.print "Ende:";Now()
' ... für diese Laufzeit sollte es meiner Meinung nach ziemlich egal sein, ob der Code aus Access oder Excel kommt.

    'Muss nicht gespeichert werden?
    '...

    xlWB.Close
    xlApp.Quit
    Set xlApp = Nothing

End Sub

Sub BereinigungDaten(byval xlWS as Excel.worksheet)

Dim intLetzteZeile As Integer, i As Integer, j As Integer

    xlWS.Columns("A:A").Insert Shift:=xlToRight
    xlWS.Cells(3, 1).Value = "Nummer"
    
    intLetzteZeile = xlWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    i = 0
    j = 3
    
    Do
        i = i + 1
        If xlWS.Cells(i, 2).Value = "Name" Then
            Do
                j = j + 1
                xlWS.Cells(j, 1).Value = xlWS.Cells(i, 3).Value
            Loop While Not (xlWS.Cells(j, 3).Value = "" Or j = intLetzteZeile)
        End If
        
    Loop While Not i = intLetzteZeile
    xlWS.Rows("1:2").Delete
    
    i = 1
    j = 1
    
    Do
        i = i + 1
        If xlWS.Cells(i, 2).Value = "Name" Then
            xlWS.Rows(i & ":" & i + 2).Delete
            i = i - 4
        End If
    Loop While Not i = intLetzteZeile
    
    xlWS.Columns("E:E").Replace " ", ""
    
    i = 0
        
    Do
        i = i + 1
        If xlWS.Cells(i, 3).Value = "" And xlWS.Cells(i + 1, 3).Value = "" And xlWS.Cells(i + 2, 3).Value = "" And xlWS.Cells(i, 1) = "" Then: Exit Sub

        If xlWS.Cells(i, 3).Value = "" Then
            xlWS.Rows(i & ":" & i).Delete
            i = i - 10
        End If
    Loop While Not i = intLetzteZeile

End Sub

mfg
Josef

[Gizzy3001]

Hey Josep,

danke für den Tipp. Hab es jetzt mit meinem und deinem Code probiert:

Die Laufzeit von meinem Code beträgt: 2:47 Minuten.
Dein Code ist etwas schneller mit 1:49 Minuten. Beim zweiten Versuch brauchte der Code 2:02 Minuten.

Hab bei deinem Code die xlApp.Visible auf false gesetzt. Dann dauert es 1:56 Minuten.

Die BereinigungsProzedur direkt aus Excel dauert keine Sekunde:

Start:14.10.2018 13:30:42
Ende:15.10.2018 13:30:43


P.S. Ich speicher die Datei noch nicht, damit ich beim Rumprobieren immer die Ausgangsdatei habe.

[Josef P.]

Hallo!

Hast du schon einmal mit xlApp.Interactive = False getestet?
Nach dem Codedurchlaufe wieder auf true setzen.
Calculation auf xlCalculationManual setzen kann auch nicht schaden.

Code:

Dim CurrentCalculation As XlCalculation

Debug.Print "Start:"; now()
    xlApp.Interactive = false
    CurrentCalculation = xlApp.Calculation ' aktuelle Einstellung zwischenspeichern
    xlApp.Calculation = xlCalculationManual
    BereinigungDaten  xlWS
    xlApp.Calculation = CurrentCalculation 
    xlApp.Interactive = true
Debug.print "Ende:";Now()

Das sollte aber nicht den großen Laufzeitunterschied zw. Aufruf aus Access und Aufruf aus Excel ausmachen.

Kann es sein, dass ein Virenscanner dazwischenfunkt?

mfg
Josef

[Gizzy3001]

Hab es nochmal mit deinen Tipps ausprobiert, leider ohne Erfolg Antivir komplett aus und den Code mit den oben geposteten Zeilen erweitert:

Start:14.10.2018 17:21:37
Ende:14.10.2018 17:22:53