MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Access & Datenbanken > Microsoft Access
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 08.10.2018, 15:59   #1
Gizzy3001
Neuer Benutzer
Neuer Benutzer
Standard Acc2013 - Excelprozedur aus Access sehr langsam!

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 Stringint1 As IntegerFd 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!

Geändert von Gizzy3001 (08.10.2018 um 16:04 Uhr).
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 08.10.2018, 16:03   #2
Nouba
MOF Guru
MOF Guru
Standard

Poliere BereinigungDaten und lasse die Automatisierung versteckt ablaufen. Beim Polieren können Dir vermutlich die Leute im Excel-Forum am besten helfen.
Nouba ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.10.2018, 08:01   #3
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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.
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.10.2018, 08:26   #4
markusxy
MOF Meister
MOF Meister
Standard

Zitat: von Gizzy3001 Beitrag anzeigen

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.
markusxy ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 09.10.2018, 08:45   #5
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}
Dein Dankeschön: DBWiki=>Spende

Geändert von ebs17 (09.10.2018 um 08:48 Uhr).
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.10.2018, 19:03   #6
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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 
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.10.2018, 19:38   #7
markusxy
MOF Meister
MOF Meister
Standard

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.
markusxy ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 11.10.2018, 20:21   #8
ebs17
MOF Guru
MOF Guru
Standard

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.

__________________

Ein freundliches Glück Auf!

Eberhard

Abfrageperformance ist kein Geheimnis
SQL ist leicht: {0}:{1}:{2}:{3}:{4}:{5}:{6}:{7}:{8}:{9}:{10}:{11}
Dein Dankeschön: DBWiki=>Spende
ebs17 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 13.10.2018, 21:57   #9
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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 IntegerAs IntegerAs Integer

    xlWS
.Columns("A:A").Insert Shift:=xlToRight
    xlWS
.Cells(31).Value "Nummer"
    
    
intLetzteZeile xlWS.UsedRange.SpecialCells(xlCellTypeLastCell).Row
    
    i 
0
    j 
3
    
    
Do
        
1
        
If xlWS.Cells(i2).Value "Name" Then
            
Do
                
1
                xlWS
.Cells(j1).Value xlWS.Cells(i3).Value
            Loop 
While Not (xlWS.Cells(j3).Value "" Or intLetzteZeile)
        
End If
        
    
Loop While Not i intLetzteZeile
    xlWS
.Rows("1:2").Delete
    
    i 
1
    j 
1
    
    
Do
        
1
        
If xlWS.Cells(i2).Value "Name" Then
            xlWS
.Rows(":" 2).Delete
            i 
4
        End 
If
    
Loop While Not i intLetzteZeile
    
    xlWS
.Columns("E:E").Replace " """
    
    
0
        
    
Do
        
1
        
If xlWS.Cells(i3).Value "" And xlWS.Cells(13).Value "" And xlWS.Cells(23).Value "" And xlWS.Cells(i1) = "" Then: Exit Sub

        
If xlWS.Cells(i3).Value "" Then
            xlWS
.Rows(":" i).Delete
            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 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.10.2018, 10:49   #10
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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!
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.10.2018, 11:48   #11
Josef P.
MOF Guru
MOF Guru
Standard

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
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.10.2018, 12:33   #12
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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.
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.10.2018, 16:09   #13
Josef P.
MOF Guru
MOF Guru
Standard

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
Josef P. ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 14.10.2018, 16:30   #14
Gizzy3001
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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
Gizzy3001 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 01:22 Uhr.


Partner und Co.
Access-Paradies -Alles rund um die Datenbank Microsoft Access -Code -Programme-Tools -Tipps   Kostenlose Tipps & Tricks, Downloads und Programme   www.kulpa-online.com - Tipps - Tricks - Tutorials - Meinungen - Downloads uvm...   vb@rchiv · Willkommen in der Welt der VB Programmierung   Access-Garhammer - Hier finden Sie jede Menge Beispiel-Datenbanken zu Access und mehr ...   mcseboard.de   Die Top Seite für Excel-VBA-Makros uvm.

Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.