Microsoft Excel

Herbers Excel/VBA-Archiv

Array Redimensionierung schlägt fehl.


Betrifft: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 13:55:51

Hallöchen
Eins vorab, da es sich bei den hier verarbeiteten Dateien um den Export meiner Warenwirtschaftssysteme handelt, habe ich keine Dateien angehängt.
Wenn Hilfe nicht ohne Datei möglich ist, werde ich aber natürlich gerne eine entsprechende Datei mit ausgedachten Werten (im gleichen Format) zur Verfügung stellen.
Aber ich behaupte mal das geht auch ohne :)

Folgendes soll mein Makro tun:
Öffnen des Exports aus meinem Warenwirtschafssystem (Format.dbf) und hier bestimmte Spalten in zwei Dateien kopieren (die kopierten Spalten sind dabei in beiden Dateien andere).
Da es sich um zwei Warenwirtschafsysteme (mit identisch dem gleichen Namen) handelt, kann ich diese leider nicht gleichzeitig öffnen.

Vor habe ich folgendes:
Die benötigten Spalten in ein Array schreiben und dieses dann in meine beiden Dateien schreiben (in der Hoffnung, dass dies schneller geht, als ein direktes kopieren).

Mein Makro gibt mir bei der Redimensionierung (im Code gekennzeichnet) den Fehler:
"Index außerhalb des gültigen Bereiches".
Warum tirtt dieser Fehler auf?

Modul: MODExport

Option Explicit
'Makro zum Erstellen der Zoo Dateien
Public Sub ZooExport()
Dim strPfadLadenEho As String, strPfadAngelEho As String, strPfadZooDateien As String,  _
strDateinameEho As String
Dim strZoo As String, strZoomitEk As String
Dim btEAN As Byte, btArtNr As Byte, btName1 As Byte, btName2 As Byte, btVK As Byte, btEK As  _
Byte
Dim btVe As Byte, btNotiz As Byte, btMwst As Byte
Dim lngLetzteZeileZoo As Long, lngletzteZeileAngel As Long, lngletztezeile As Long
Dim i As Long, j As Long
Dim Datenarray() As String

    TurnOff
    'Festlegen der Variablen für Dateien
    strPfadLadenEho = Left$(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\")) & "Ladeneho\"
    strPfadAngelEho = Left$(ThisWorkbook.Path, InStrRev(ThisWorkbook.Path, "\")) & "Angeleho\"
    strDateinameEho = "Artikel.dbf"
    strPfadZooDateien = ThisWorkbook.Path & "\"
    strZoo = "zoo.xlsm"
    strZoomitEk = "zoomitEK.xlsm"
    'Festlegen der Spaltennummern in Export Dateien
    btEAN = 69
    btArtNr = 1
    btName1 = 2
    btName2 = 3
    btVK = 9
    btEK = 6
    btVe = 29
    btNotiz = 18
    btMwst = 10
    
    'LadenEho
    Workbooks.Open (strPfadLadenEho & strDateinameEho), ReadOnly:=True
    With Workbooks(strDateinameEho).Worksheets("ARTIKEL")
        lngLetzteZeileZoo = .Cells(Rows.Count, 1).End(xlUp).Row
        ReDim Datenarray(2 To lngLetzteZeileZoo, 1 To 10)
        'befüllen des Arrays
        For i = 2 To lngLetzteZeileZoo
            Datenarray(i, 1) = .Cells(i, btEAN).Value
            Datenarray(i, 2) = .Cells(i, btArtNr).Value
            Datenarray(i, 4) = .Cells(i, btName1).Value
            Datenarray(i, 5) = .Cells(i, btName2).Value
            Datenarray(i, 6) = .Cells(i, btVK).Value
            Datenarray(i, 7) = .Cells(i, btEK).Value
            Datenarray(i, 8) = .Cells(i, btVe).Value
            Datenarray(i, 9) = .Cells(i, btNotiz).Value
            Datenarray(i, 10) = .Cells(i, btMwst).Value
        Next i
    End With
    Workbooks(strDateinameEho).Close Savechanges:=False
    'AngelEho
    Workbooks.Open (strPfadAngelEho & strDateinameEho), ReadOnly:=True
    With Workbooks(strDateinameEho).Worksheets("ARTIKEL")
        lngletzteZeileAngel = .Cells(Rows.Count, 1).End(xlUp).Row
        'Array vergrößern
        ReDim Preserve Datenarray(2 To lngLetzteZeileZoo + lngletzteZeileAngel, 1 To 10)
        j = lngLetzteZeileZoo
        'befüllen des Arrays
        For i = 2 To lngletzteZeileAngel
            j = j + 1
            Datenarray(j, 1) = .Cells(i, btEAN).Value
            Datenarray(j, 2) = .Cells(i, btArtNr).Value
            Datenarray(j, 3) = .Cells(i, btName1).Value
            Datenarray(j, 4) = .Cells(i, btName2).Value
            Datenarray(j, 5) = .Cells(i, btVK).Value
            Datenarray(j, 7) = .Cells(i, btEK).Value
            Datenarray(j, 8) = .Cells(i, btVe).Value
            Datenarray(j, 9) = .Cells(i, btNotiz).Value
            Datenarray(j, 10) = .Cells(i, btMwst).Value
        Next i
    End With
    Workbooks(strDateinameEho).Close Savechanges:=False
    'Öffnen der Zoo Liste
    Workbooks.Open (strPfadZooDateien & strZoo)
    With Workbooks(strZoo).Worksheets("Liste")
        .Cells.ClearContents
        .Range("A2") = "0"
        .Range("B2") = "Scanner"
        .Range("C2") = "EAN Code"
        .Range("D2") = "Art. Nr."
        .Range("E2") = "Name 1"
        .Range("F2") = "Name 2"
        .Range("G2") = "Preis"
        .Range("H2") = "Menge"
        .Range("C3:G" & UBound(Datenarray, 2)) = Datenarray
    End With
    
    TurnOn
End Sub
Modul Funktionen:
Option Explicit
'Funktionen aus
Public Sub TurnOff()
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False
Application.ScreenUpdating = False
End Sub
'Funktionen ein
Public Sub TurnOn()
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 14:04:46

Mist, nun habe ich das markieren der Fehlerstelle vergessen.

ReDim Preserve Datenarray(2 To lngLetzteZeileZoo + lngletzteZeileAngel, 1 To 10)

ist die ÜbeltäterStelle.

und eventuell auch noch von belang:
In der zuerst geöffneten Datei (LadenEho) ist lngletzteZeile ca. 9500
in der zweiten Datei (Angeleho) ist lngletzteZeile ca. 4000

Ich hatte das Array zuerst falsch herum:
Anstelle von
ReDim Datenarray(2 To lngLetzteZeileZoo, 1 To 10)
habe ich
ReDim Datenarray(1 To 10, 2 To lngLetzteZeileZoo) verwendet.
Die Ausgabe in meine Datei war natürlich falsch - aber das Redim hat wunderbar funktioniert.


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: yummi
Geschrieben am: 17.07.2017 14:08:33

Hallo florian,

Du kannst mehrdimensionale Arrays nur in der letzten Dimension redimensionieren, aber es gibt eienen Trick Schau mal hier:
http://www.vbarchiv.net/tipps/tipp_479-mehrdimensionale-arrays-redimensionieren.html

Gruß
yummi


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 14:56:07

Hmm.
Ich habe gehört, dass ReDim aus Geschwindigkeitssicht eher nicht angewendet werden soll.
Wenn ich jetzt aber eine Funktion brauche, um die Daten in ein anderes Array zu schaufeln, machts das glaube ich echt nicht besser :)

Wäre es da nicht besser das Array von vornerein auf gut Glück etwas größer zu dimensionieren?
Oder gibt es eine Möglichkeit zwei Dateien mit dem gleichen Namen gleichzeitig zu öffnen, damit ich die letzen Zeilen suchen kann?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 15:50:55

Hi

Schleifen über Arrays sind sehr schnell.
so ist bspw die doppelte Schleife zum Transponieren eines Arrays schneller als Worksheetfunktion.Transpose.

ansonsten könntest du ja hier erstmal auch die Werte aus beiden Dateien in ein Array laden und dann mit diesen Arrays anstatt mit den Daten in den Zellen arbeiten (das ist auch schneller)

dann kannst du auch die Daten aus beiden Dateien gleichzeitig in unterschiedlichen Arrays speichern, bevor du mit der eigentlichen Arbeit anfängst und kannst da dann die benötigte Größe des Zielarrays berechnen.

Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 16:38:59

Hallo Daniel
Eine wirkliche Arbeit gibt es nicht.
Ich schaufel das ganze nur in ein Array, um die Spalten nicht kopieren zu müssen.
Da müsste ich ja dann (aufgrund des gleichen Namens der Dateien, aus denen die Daten kommen), permanent zwischen meinen Workbooks hin und her springen.
(Es gibt 2 "Import" Dateien und 2 Export Dateien)

ALso ändere ich mein Makro wie folgt.
Statt einem Array erstelle ich also zwei Arrays (eins für jede ImportDatei)
Redim Array (2 to lngletztezeile, 1 to 10)
und schreibe dann mit der gleichen Methode wir vorher
.Range("C3:G" & UBound(Array1)) = Array1
letztezeile suchen
zweites Array an letztezeile + 1 einfügen

Das ist dann eleganter(Codemäßig besser), als die Reimensionierung?

Dann eine weitere Frage:
Ich muss noch den "Zeichensatz umwandeln", Umlaut und Sonderzeichen, werden nicht korrekt angezeigt.
Hierfür habe ich folgendes aus dem MAkrorekorder:

    'Umwandeln des Zeichensatzes
    Cells.Replace What:="³", Replacement:="ü", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="÷", Replacement:="ö", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="õ", Replacement:="ä", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:="Ç", Replacement:="€", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=ChrW(9600), Replacement:="ß", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Cells.Replace What:=ChrW(9617), Replacement:="°", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Dies würde ich auf dem Arbeitsblatt machen - also nachdem ich das Array einkopiert habe, da es im Array keine geeignete Funktion für ein Replace gibt, ist das so korrekt, oder geht das im Array schneller als auf dem Sheet?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 17:11:00

?
Warum postest du zwei mal das gleiche?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 17:51:21

Ist ein Problem der Forensoftware, welche nicht erkennt, dass der selbe Beitrag 2x hintereinander gesendet wurde.
Das Senden wird auch dann automatisch ausgelöst, wenn du in deinem Browser durch die Historie gehst und dann über die Seite kommst.

Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 16:36:03

Hi

Schleifen über Arrays sind sehr schnell.
so ist bspw die doppelte Schleife zum Transponieren eines Arrays schneller als Worksheetfunktion.Transpose.

ansonsten könntest du ja hier erstmal auch die Werte aus beiden Dateien in ein Array laden und dann mit diesen Arrays anstatt mit den Daten in den Zellen arbeiten (das ist auch schneller)

dann kannst du auch die Daten aus beiden Dateien gleichzeitig in unterschiedlichen Arrays speichern, bevor du mit der eigentlichen Arbeit anfängst und kannst da dann die benötigte Größe des Zielarrays berechnen.

Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 17:58:55

Ah ok :)

Und was sagst du hierzu?

Hallo Daniel
Eine wirkliche Arbeit gibt es nicht.
Ich schaufel das ganze nur in ein Array, um die Spalten nicht kopieren zu müssen.
Da müsste ich ja dann (aufgrund des gleichen Namens der Dateien, aus denen die Daten kommen), permanent zwischen meinen Workbooks hin und her springen.
(Es gibt 2 "Import" Dateien und 2 Export Dateien)

ALso ändere ich mein Makro wie folgt.
Statt einem Array erstelle ich also zwei Arrays (eins für jede ImportDatei)
Redim Array (2 to lngletztezeile, 1 to 10)
und schreibe dann mit der gleichen Methode wir vorher
.Range("C3:G" & UBound(Array1)) = Array1
letztezeile suchen
zweites Array an letztezeile + 1 einfügen

Das ist dann eleganter(Codemäßig besser), als die Reimensionierung?

Dann eine weitere Frage:
Ich muss noch den "Zeichensatz umwandeln", Umlaut und Sonderzeichen, werden nicht korrekt angezeigt.
Hierfür habe ich folgendes aus dem MAkrorekorder:
'Umwandeln des Zeichensatzes
Cells.Replace What:="³", Replacement:="ü", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="÷", Replacement:="ö", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="õ", Replacement:="ä", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="Ç", Replacement:="€", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=ChrW(9600), Replacement:="ß", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:=ChrW(9617), Replacement:="°", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Dies würde ich auf dem Arbeitsblatt machen - also nachdem ich das Array einkopiert habe, da es im Array keine geeignete Funktion für ein Replace gibt, ist das so korrekt, oder geht das im Array schneller als auf dem Sheet?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 18:42:39

weiß ich nicht.
ich hab mir deine Aufgabenstellung auch nicht so genau angeschaut und bin auch zu faul, deinen code diesbezüglich zu analysieren, ohne die Datei zu kennen.

ich weiß nur, dass du dir aus zwei Dateien mit gleichem Namen irgendwas zusammenlesen willst.

bisher machst du das offentsichtlich in dieser Form:
1. Datei1 öffnen
2. Daten ins Ergebnisarray
3. Datei1 schließen
4. Datei2 öffnen
5. Ergebnisarray redimensioneren
6. Daten aus Datei2 ins Ergebnisarray dazuspielen
7. Datei2 schließen
8. Ergebnisarray auslesen

das würde aber auch so gehen.
1. Datei1 öffnen
2. Daten aus Datei1 ins Array1 lesen
3. Datei1 schließen
4. Datei2 öffnen
5. Daten aus Datei2 ins Array2 lesen
6. Datei2 schließen
7. mit Array1 und Array2 die notwendige Größe des Ergebnisarrays berechnen
8. Daten aus Array1 ins Ergebnisarray
9. Daten aus Array2 ins Ergebnisarray
10. Ergebnisarray ausgeben.

ob das Ersetzen der Zeichen im Array schneller geht als im Sheet, kann ich dir auch nicht sagen, das müsstest du ausprobieren.
Im Array musst du halt noch die Schleifen programmieren, da du ja jeden Wert einzeln umwandeln musst.
Ist also etwas aufwendiger. ob schneller weiß ich nicht, das Ersetzen im Blatt ist ja auch sehr schnell.

Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 19:42:50

Hmm.
Nicht ganz. Bisher mache ich das so:
1. TemporärDatei (welches das Makro enthält) leeren.
2. Datei1 zum import öffnen.
3. Spalte für Spalte die Werte, welche mich interessieren reinkopieren
4. das gleiche mit Importdatei2
5. Exportdatei 1 öffnen und leeren
6. da reinkopieren
7. das gleiche mit Exportdatei 2

Das funktioniert zwar ist ja aber scheiße.

Mein letzter Post - soll das gleiche ausdrücken, was du hier so schön formatiert hast,
nur dass ich nicht auf die Idee gekommen bin, meine beiden Arrays in eins umzuladen um dieses dann auszugeben.
Bei mir wäre das eine Reihe von letztezeilesuchen und Ubounds addieren um beide Arrays unternander hinzubringen.
Aber dein Vorschlag gefällt mir besser als mein Murks :)

Dann nur noch eine letzte Frage zu meinem Replace.
Ich habe mir das ganze mal im Einzelschritt angesehen.
Die immer gleiche Operation (Das suchen des entsprechenden Wertes und ersetzen) wird ja für jedes Zeichen einzeln durchlaufen.
Gibt es hier die Möglichkeit, dass alles in einem Schritt zu machen?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 19:48:44

HI
was ist an diesem Ablauf "scheiße"?
der hört sich doch vernünftig an.
Wenn du Daten zusammenbringen willst, bist du mit Copy-Paste im Tabellenblatt genauso schnell wie mit Arrays und hast ggf weniger programmieraufwand.
Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 17.07.2017 21:10:47

Nein ich denke das stimmt so nicht.
Mein altes Makro - mit dem Kopieren in ein Temporäres Sheet dauert 8 Sekunden um die Daten nur ins TempSheet zu bringen.
In meinem neuen Makro mit den Arrays habe ich (wenn ich das Array manuel groß genug dimensioniere), nach 5 Sekunden die Daten bereits da wo sie hin sollen.

Dann nur noch eine letzte Frage zu meinem Replace.
Ich habe mir das ganze mal im Einzelschritt angesehen.
Die immer gleiche Operation (Das suchen des entsprechenden Wertes und ersetzen) wird ja für jedes Zeichen einzeln durchlaufen.
Gibt es hier die Möglichkeit, dass alles in einem Schritt zu machen?


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Daniel
Geschrieben am: 17.07.2017 21:33:47

nö, du musst jede Zeichenfolge seperat ersetzen.
um dir die Programmierung zu erleichtern, kannst du AltTexte und NeuTexte in jeweils ein Array schreiben um dann das ganze mit einer Schleife zu erledigen.

Gruß Daniel


  

Betrifft: AW: Array Redimensionierung schlägt fehl. von: Florian
Geschrieben am: 18.07.2017 17:33:00

Alles klar.
Ich habe mir nun zwei Arrays mittels Split erstellt - eins mit den zu ersetzenden Zeichen und eins mit den die neu kommen - die acker ich dann in einer Schleife ab.

Ich habe darüber hinaus mal eine Zeitmessung eingebaut.
Die Variante mit kopieren in ein Temp Sheet braucht 18 Sekunden.
Die Variante mit dem Array (zur Zeit noch ein statisches Array - ich werde aber meine beiden Warenwirtschafsysteme zusammenfassen, damit sich das Problem mit dem gleichen Dateinamen nicht mehr stellt), benötigt für die gleiche Arbeit 6 Sekunden.

Also alles schick - alles läuft - danke für deine Hilfe :)


Beiträge aus den Excel-Beispielen zum Thema "Array Redimensionierung schlägt fehl."