Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Daten durch VBA filtern und in neue Datei kopieren

Daten durch VBA filtern und in neue Datei kopieren
26.07.2013 07:51:02
Julia
https://www.herber.de/forum/archiv/1320to1324/t1321094.htm
Gibt es denn keine Möglichkeit den Code aus dem alten Thread oben so einzustellen, dass ich bestimmte Abteilungen die ich vorher weiß in einen anderen Ordner abspeichere bzw. nur diese Filtere und in eine neue Datei kopiere?
Danke für die Hilfe.
VG Julia

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
27.07.2013 21:21:17
fcs
Hallo Julia,
in Excel geht vieles. Man muss halt nur genau spezifizieren, was gewünscht ist.
'Du kannst die systematische Festlegung der Abteilungsordner in Zeile
        'Ordner der Abteilung
strOrdnerAbt = strOrdner & "\Abteilung_" & arrAbt(intAbt) '"Abteilung_"ggf. anpassen

'ersetzen durch eine individuelle Festlegung oder auch Mischform.
        'Ordner der Abteilung
Select Case arrAbt(intAbt)
Case "A", "B"
strOrdnerAbt = strOrdner & "\Meine Lieblinge"
Case "C"
strOrdnerAbt = "C:\Users\Public\Department C"
Case "D"
strOrdnerAbt = "T:\Abteilung D"
Case "E", "F", "F1", "F2"
strOrdnerAbt = strOrdner & "\Abteilung_" & arrAbt(intAbt) '"Abteilung_"ggf.  _
anpassen
Case Else
'alle anderen Abteilungen nicht filtern
GoTo Naechste_Abt
End Select

Zusätzlich muß du dann die Sprungadresse noch hier einfügen:
        'alle Daten in Hauptdatei anzeigen
.ShowAllData
Naechste_Abt: 'Sprungadresse, wenn Abteilung nicht gefiltert werden soll
Next intAbt
Gruß
Franz

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
29.07.2013 09:12:31
Julia
Hi,
habe den Code für den Hauptordner raus gelöscht und deinen Code eingefügt und plötzlich kommt Laufzeitfehler '-2147221080 (800401a8)' Automatisierungsfehler bei
'Alle Daten im Abteilungsblatt löschen
.UsedRange.Clear
:(

AW: Daten durch VBA filtern und in neue Datei kopieren
29.07.2013 12:20:34
Julia
Hi,
also hatte den Fehler jetzt gefunden. Da die Dateien aber noch so groß waren, habe ich dann aber noch den Code für .UsedRange.Clear mit .UsedRange.EntireRow.Delete shift:=xlShiftUp ersetzt. Und da kommt jetzt Laifzeitfehler '1004' Die Delete-MEthode des Range-Objektes konnten icht ausgeführt werden.
sorry das ich dich so lange damit beschäftige

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
30.07.2013 06:10:04
fcs
Hallo Julia,
den Fehler kann ich nicht nachvollziehen. Er tritt eigentlich nur auf
- wenn Tabellen geschützt sind
- manchmal, wenn verbundene Zellen vorhanden sind
Evtl. gibt es auch Probleme, wenn extrem viele Daten/Zellen gelöscht werden müssen, und Excel gibt entsprechende Fehlermeldungen aus. Zumindest beimmanuellen Löschen kommen ja gelegentlich Meldungen wie "Änderung kann nicht rückgäng gemacht werden" oder ähnliches, wenn Aktionen durchgeführt werden, in die größe Zellbereiche einbezogen wrden.
Mit deiner relativ kleinen Testdatei gibt es bei mir jedenfalls keine Probleme.
Ich hab deine Testdatei jetzt auch mal vergrößert (über 100000 Datenzeilen + Zeilen, die nur Formate enthalten).
Dateigröße dann über 7 MByte.
Dann gab es allerdings Probleme mit der Laufzeit bei der Ermittlung der in Spalte I eingetragenen Abteilungen und auch das kopieren der gefilterten Zeilen für die Abteilungen dauert sehr lang.
Lösung: Daten nach den Abteilungen sortieren und Abteilungen anders ermitteln.
Die Dateigröße der erzeugten Abteilungs-Dateien ist dann bei ca. 1000 Datenzeilen ca. 75 kByte. Bei mehr Datenzeilen steigt die Dateigröße entsprechend.
Dabei funktioniert dann alles relativ flott, bis auf das Erstellen einer neuen Abteilungs-Datei wenn diese im Zielordner noch nicht vorhanden ist. Hier wird zur Zeit ja erst einmal das Blatt mit allen Buchungen in die neue Datei kopiert und dann werden alle Datenzeilen gelöscht.
Lösung: Es wird eine fast leere Musterdatei erstellt, die ein Tabellenblatt enthält mit den Formatierungen für die Spaltenbreiten und den Einstellungen unter "Seite einrichten".
(= Kopie von dem Blatt mit den Hauptbuchungen und dann alle Zeilen löschen).
Diese Musterdatei (Name = Vorlage.xlsx) wird im gleichen Verzeichnis gespeichert, wie die Datei mit den Hauptbuchungen. Die Größe der Datei ist dann ca. 20 bis 30 kByte. Statt das Blatt mit allen Hauptbuchungen in eine neue Datei zu kopieren wird vom Makro für eine neue Abteilungsdatei dann diese Vorlage verwendet.
In der Textdatei findest du jetzt das Makro optiniert in diese Richtung.
https://www.herber.de/bbs/user/86627.txt
Wenn die erzeugten Abteilungsdateien immer noch extrem groß sind, dann müssen wir uns noch den Zellbereich rechts der eigentlichen Daten vornehmen, ob hier Datenmüll schlummert.
Ich gehe mal davon aus, dass im Datenbereich der Buchungen (Spalten A bis J) sparsam mit Zellformatierunen gearbeitet wird. Viele verschiedene Zellformatierungen blähen auch die Dateigröße auf.
Gruß
Franz

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
30.07.2013 08:40:54
Julia
Hi,
danke für die Hilfe. Also meine Originaldatei ist ca. 10 MB, da dort auch sehr viele Pivots in separaten Tabellenblättern drin sind. In dem Tabellenblatt welches die Buchungen enthält, sind wir auch bei ca. 15.000 Zeilen a 48 Spalten. Dort sind halt auch SVerweise und bedingte Formatierungen enthalten, welche in der Originaldatei notwendig sind. In den Kopien sind die Verweise ja dann nicht mehr drin, allerdings die bedingten Formatierungen nimmt er trotz Vorlagendatei (31KB)wieder mit. Ich habe auch schon eine extra Spalte angelegt gehabt, in der ich die Anzahl der Abteilungen reduziere. Eigentlich habe ich 26 (sind halt teilweise nur kleine Projekte)beschreibe es nur mit Abteilungen. In meiner zusätzlichen Spalte habe ich jetzt nur noch die 5 relevanten die ich für Pivottabellen in den Projektkalkulationen brauche und den Rest mit "-", so dass das sicher schneller geht als wenn er 26 durchgeht.
Allerdings ist die Dateigröße von der alten Variante von ca. 2MB pro Datei auf 8 - 40 MB angestiegen. Ich glaub da bekomme ich Ärger mit dem Admin :). Ansonsten klappt es auch beim wiederholen. Vorher hattest du Recht kam bei einer Datei die Meldung, dass Excel es mit den Ressourcen nicht schafft.
Wenn ich in der 40MB Datei STRG+Ende drücke lande ich in der letzten Zeile mit Inhalt, bei den Spalten bin ich allerdings bei XFA statt AS. Wenn ich die Spalten manuell lösche mit STRG+"-" dann komme ich auf 250KB in der Datei.

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
30.07.2013 09:02:08
Julia
Hab grad festgestellt, dass der Code mir zum einen meine Datenfilter in der ersten Zeile mit Überschriften in der Originaldatei entfernt, muss ich erst wieder anklicken und zum anderen die Sortierung die er macht, mir die Daten komplett durcheinander schmeißt. Da die Buchhaltung natürlich ihre Reihenfolge braucht, geht das leider nicht. Die Originaldatei darf nicht verändert werden. Entweder müssten wir doch ohne Sortierung auskommen oder diese rückgängig machen. Es gibt auch eine Spalte mit laufender Nummer anhand derer ich das jetzt zurück sortiert habe.
Wir erstellen auch täglich eine Kopie der Datei, damit in der Original wirklich nur die Buchhaltung arbeitet. Werde wohl für den Export der Daten auch in die Kopie wechseln. Befürchte sonst Fehler die passieren wenn die Daten falsch sortiert sind.

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
31.07.2013 07:00:43
fcs
Hallo Julia,
ich habe das Ganze jetzt mal so anpassen, dass das Makro für die Aktioen eine Arbeitskopie der Datei erstellt.
https://www.herber.de/bbs/user/86642.txt
In der Kopie werden im Blatt "Hauptbuchungen" dann die bedingten Formatierungen gelöscht, die Formeln durch ihre Werte ersetzt und der Datenmüll jenseits von Spalte AS gelöscht.
Danach erfolgt dann das Filtern der Daten und die Erstellung/Bearbeitung der Abteilungsdateien.
Zum Schluss wird die Arbeitskopie wieder gelöscht.
Evtl. sollten die Kollegen/Kolleginnen der Buchaltung ihre Datei gelegentlich aich mal bereinigen. D.h, den Datenmüll ab Spalte AT löschen und die Bedingten Formatierungen löschen und neu aufsetzen.
Durch das Kopieren / Anfügen von Zeilen entsteht nämlich ein riesiger Flickenteppich von Formatierungen. Das wirkt sich dann auch positiv auf das Arbeitstempo mit der Datei aus.
Gruß
Franz

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
31.07.2013 08:33:39
Julia
Hi Franz,
also mit der Kopie arbeitet er deutlich schneller, sehr gut. Aber zum einen kommt immer bei jeder Abteilung die Meldung Die Formel die eingefügt werden soll. enthält einen Namen, der bereits im Zielarbeitsblatt vorhanden ist... soll die vorhandene Definition verwendet werden. Habe jetzt schon alle Namensdefintionen gelöscht, da die meines Wissens nach eh nicht mehr verwendet werden. Trotzdem fragt er das noch :(.
Außerdem sind die Dateien immer noch so groß, was ich nicht verstehe da ja weder bedingte Formatierungen noch Formeln enthalten sind.
VG Julia

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
31.07.2013 08:44:53
Julia
Habe gerade gesehen, dass immer noch bei STRG+Ende nicht bei AS gelandet wird, sondern noch bei XFA. ALso shceint der Teil nicht zu funktionieren. In der Hauptbuchhaltung habe ich das aber schon mal angepasst, da wir Probleme hatte, dass die DAtei immer langsamer und größer wurde. Da ist das Spalten Ende wo es sein soll. In der Voralge passt es auch auch. Der Datenmüll entsteht also erst in der neuen Datei.

AW: Daten durch VBA filtern und in neue Datei kopieren
31.07.2013 16:08:34
fcs
Hallo Julia,
ich verstehe jetzt nicht mehr, wie sich der Datenmüll aufbaut.
Probiere mal folgendes:
    'Datenmüll  löschen
.Range(.Columns(.Range("AS1").Column + 1), .Columns(.Columns.Count)).Clear
.Range(.Rows(Zeile_H), .Rows(Rows.Count)).Clear
.Range(.Rows(Zeile_H), .Rows(Rows.Count)).EntireRow.Delete
.Range(.Columns(.Range("AS1").Column + 1), .Columns(.Columns.Count)).EntireColumn.Delete

Das Problem mit den Namen könnte auch von ausgeblendeten Namen herrühren oder Namen mit Formeln.
Evtl sollte während der Makroausführung einfach vorübergehend die Anzeige von Warnmeldungen deaktiviert werden.

Anzeige
AW: Daten durch VBA filtern und in neue Datei kopieren
01.08.2013 07:39:11
Julia
Super, jetzt sind sie maximal 294KB groß. Das mit dem Namen verstehe ich trotzdem nicht, ich kann mir in der neuen Datei ja auch angucken welchen Namen er genommen hat, aber in der Originaldatei habe ich diesen ja gelöscht. Wenn ich die Warnmeldungen deaktiviere, befürchte ich nicht mitzubekommen wenn irgendwas mal nicht geht und dann habe ich evtl. nicht alle Daten kopiert.
Ich habe auch schon nach den Namen gesucht, auch in Formeln, aber ich finde ihn nicht. Wie kann ich den finden?
Vielen lieben Dank für deine Hilfe.
VG Julia

Namen in Datei auflisten, Namen löschen
01.08.2013 13:36:34
fcs
Hallo Julia,
das Deaktivieren der Alarm-Hinweise, kann auch direkt auf die Kopieranweisung beschränkt werden.
        'gefilterte Daten der Abteilung kopieren
Application.DisplayAlerts = False
.Range(.Rows(1), .Rows(Zeile_H)).Copy wksAbt.Cells(1, 1)
Application.DisplayAlerts = True

Dann sollte keine Rückfrage bezüglich der Namen kommen.
Warum Namen mit kopiert werden, obwohl du die Namen angeblich gelöscht hast weiss ich nicht.
Nachfolgend zwei Makros, mit denen ich Infos zu Namen aus einer Exceldatei auslesen kann bzw. radikal alle Namen lösche.
Gruß
Franz
Sub Datei_Namen_listen()
'Alle Namen in der aktiven Arbeitsmappe werden mit Zusatzinformation _
in einer Tabelle in einer neuen Arbeitsmappe gelistet.
Dim objName As Name, wbAktiv As Workbook, wbZiel As Workbook, wksZiel As Worksheet
Dim lngZei As Long
On Error Resume Next
Set wbAktiv = ActiveWorkbook
If wbAktiv.Names.Count = 0 Then
MsgBox "Keine Namen in Datei """ & wbAktiv.Name & """", vbInformation + vbOKOnly, _
"Namen auslesen"
GoTo Beenden
End If
'Neue Arbeitsmappe für Namens-Liste anlegen
Set wbZiel = Workbooks.Add(Template:=xlWBATWorksheet)
Set wksZiel = wbZiel.Worksheets(1)
Application.ScreenUpdating = False
With wksZiel
lngZei = lngZei + 1
.Cells(lngZei, 1).Value = "Liste der Namen in Datei"
lngZei = lngZei + 1
.Cells(lngZei, 1).Value = wbAktiv.Name
'Spaltentitel
lngZei = lngZei + 1
.Cells(lngZei, 1).Value = "Name"
.Cells(lngZei, 2).Value = "Name Local"
.Cells(lngZei, 3).Value = "Refers to Local"
.Cells(lngZei, 4).Value = "Refers to R1C1Local"
.Cells(lngZei, 5).Value = "Visible"
.Cells(lngZei, 6).Value = "Parent"
.Cells(lngZei, 7).Value = "Category"
.Cells(lngZei, 8).Value = "MacroType"
Cells(lngZei + 1, 2).Select
Application.ActiveWindow.FreezePanes = True
For Each objName In wbAktiv.Names
lngZei = lngZei + 1
.Cells(lngZei, 1).Value = "'" & objName.Name
.Cells(lngZei, 2).Value = "'" & objName.NameLocal
.Cells(lngZei, 3).Value = "'" & objName.RefersToLocal
.Cells(lngZei, 4).Value = "'" & objName.RefersToR1C1Local
.Cells(lngZei, 5).Value = objName.visible
With .Cells(lngZei, 6)
If objName.Parent.Name = wbAktiv.Name Then
.Value = "Datei: "
Else
.Value = "Tabelle: "
End If
.Value = .Value & objName.Parent.Name
End With
.Cells(lngZei, 7).Value = objName.Category
.Cells(lngZei, 8).Value = objName.MacroType
Next
.Range(.Columns(1), Columns(8)).AutoFit
End With
wbZiel.Activate
Beenden:
Application.ScreenUpdating = True
Set wbAktiv = Nothing: Set wbZiel = Nothing: Set wksZiel = Nothing: _
Set objName = Nothing
End Sub
Sub Datei_Namen_Loeschen()
'Erstellt 2012-06-02
'löscht in der aktiven Arbeitsmappe alle definierten Namen (auch unsichtbare!) _
- außer MS-geschützter Namen für Tabellen-Funktionen
On Error GoTo Fehler
Dim objName As Name
If ActiveWorkbook.Names.Count = 0 Then
MsgBox "In der aktiven Arbeitsmappe sind keine Namen vorhanden.", _
vbInformation + vbOKOnly, _
"Namen löschen"
GoTo Beenden
End If
If MsgBox("Alle Namen in aktiver Arbeitsmappe löschen?", _
vbQuestion + vbOKCancel, _
"Namen löschen") = vbCancel Then GoTo Beenden
For Each objName In ActiveWorkbook.Names
If LCase(Left(objName.Name, 6))  "_xlfn." Then
objName.Delete
End If
Next
Fehler:
With Err
Select Case .Number
Case 0 'Alles OK
Case 1004
MsgBox "Name """ & objName.Name & """ kann nicht gelöscht werden", _
vbInformation + vbOKOnly, "Namen in Mappe löschen"
Resume Next
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbLf & .description
End Select
End With
Beenden:
Set objName = Nothing
End Sub

Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge