Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1452to1456
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Daten in andere Datei exportieren, wenn Datei gesc

Daten in andere Datei exportieren, wenn Datei gesc
28.10.2015 14:11:28
Anja
Hallo Excelfüchse,
ich brauche noch einmal Eure Hilfe, weil ich nicht weitekomme. Ich sitze jetzt seit mehreren Tagen hier und habe alles Mögliche ausprobiert, ohne Erfolg.
Ich will Daten in eine Datei exportieren. Von Excel zu Excel. Es gibt eine Hauptdatei, in die von verschiedenen Unter-Dateien Daten übertragen werden sollen. Diese Unterdateien werden ständig geändert.
Leider habe ich das Problem, dass es bei einem normalen Import sehr lange dauert, weil die Unterdateien Makros ausführen und Pivot-Tabellen aktualisieren wenn Sie geöffnet oder geschlossen werden. Das ist auch unbedingt notwendig, weil z.B. beim Schließen das aktuelle Datum eingetragen wird, welches ich benötige, wenn ich Daten in die Hauptdatei importiere, damit der bereits ältere vorhandene Datensatz erkannt und gelöscht wird.
Ich würde es gerne so lösen, dass man sozusagen den umgekehrten Weg geht. Also nicht die Hauptdatei öffnen und die Unterdateien zu importieren, sondern wenn Änderungen in einer Unterdateien erfolgt sind, soll beim Schließen eine Meldung erscheinen, ob die Daten in die Hauptdatei exportiert werden sollen.
Wunschcode:
Unterdatei wurde bearbeitet
Beim Schließen Meldung: Sollen die Änderungen in die Lagerliste übernommen werden?
Auswahlmöglichkeit: Ok oder Abrechen.
Beim Abrechen: Abbruch / Unterdatei bleibt geöffnet
Bei OK: Daten in Hauptdatei übertragen / Unterdatei bleibt geöffnet
Unterdatei:
Dateiformat .xlsm
Tabellenblatt Statistik
Spalten A-S ab Zeile 13
Prüfen wo Daten vorhanden sind und dann auch nur diese Daten übertragen und nicht die leeren Zeilen unterhalb. Nur Werte und Formate. (keine Formeln, keine Namen, keine Verknüpfungen)
Hauptdatei
Dateiformat xlsm.
Wenn die Nachfrage in der Unterdatei mit Ok bestätigt wurde, soll der Nutzer die Möglichkeit haben diese Datei auszuwählen. Das macht mehr Sinn, wenn die Hauptdatei mal unbenannt wird.
Prüfen ob Datei bereits geöffnet ist:
Wenn ja Meldung und Abbruch
Wenn nein Makro weiter ausführen
Daten einfügen in:
Spalten A-S ab Zeile 19
Prüfen, wo letzter Datensatz steht und die neuen Daten darunter einfügen.
Ich habe einen Code geschrieben, der prüft, ob der Datensatz bereits vorhanden ist und dann den älteren Datensatz löscht.
Die Hauptdatei soll nach Übertrag geöffnet bleiben, und muss nicht geschlossen werden.
Wenn jemandem etwas einfällt wäre ich unendlich dankbar.
Liebe Grüße und Dank im Voraus
Anja

6
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Grundlagen der Programmierung ist,
29.10.2015 13:46:52
Michael
Anja,
Probleme in Teilprobleme zu zerlegen.
Ad 1: "Wunschcode": so, wie Du es darstellst, ist es unmöglich, die Datei überhaupt zu schließen.
Ich würde eine globale Variable "gespeichert" beim Öffnen der Datei mit "false" vorbelegen und beim Speichern auf "true" setzen. Beim erneuten Schließen kannst Du die Variable abfragen, und die Datei schließen, wenn "true". Damit ist aber noch nicht sichergestellt, daß etwaige, nach dem Speichern erfolgte Änderungen auch übernommen werden, was man umgehen könnte, indem man bei Change-Ereignissen "gespeichert" wieder auf "false" setzt.
Ad 2: "Unterdatei": mit VBA=gut solltest Du wissen, wie man die unterste Zeile mit vorhandenen Daten ermittelt. Die zweite Angabe könnte man mißverständlich auslegen: copy .. paste valuesandformats oder wirklich überprüfen, ob in jeder einzelnen Zelle eine Formel steht oder ein Wert?
Ad 3: "Hauptdatei": Prüfen, wo letzter Datensatz steht und die neuen Daten darunter einfügen.
Ich habe einen Code geschrieben, der prüft, ob der Datensatz bereits vorhanden ist und dann den älteren Datensatz löscht.

Ich sehe hier eine gewisse Verwirrung zwischen "Bereich" kopieren und "dem vorhandenen Datensatz".
Eine weitere Bearbeitung durch uns ist eigentlich nur sinnvoll, wenn Du uns anonymisierte Beispieldatein zur Verfügung stellst, mit dem Code, der bereits vorhanden ist - am besten gezipt, dann bleiben die Dateinamen erhalten.
Schöne Grüße,
Michael
P.S.: bei welchem *Teilproblem* hast Du eigentlich Schwierigkeiten?

Anzeige
AW: Grundlagen der Programmierung ist,
29.10.2015 15:59:16
Anja
Hallo Michael,
Danke für Deine Antwort.
Programmbeschreibung
Unterdateien = Archivliste.
Ich versuche ein Lagerprogramm für die Firma zu schreiben, in der ich arbeite. Wir investieren viel Arbeit mit dem Suchen und Finden von Ordnern und Unterlagen. Jetzt soll jeder die Möglichkeit haben Ordner in eine Liste einzutragen, wenn ein neuer Auftrag kommt. Das ist pro Klient eine eigene Datei, weil es vorkommt dass bis zu 800 Ordner vorhanden sind. In dieser Datei kann man auch eintragen, wenn jemand einen Ordner aus dem Archiv holt oder wann sie vernichtet werde können.
Sie ist sehr komplex, daher können nicht alle Klienten in eine Liste eingetragen werden. Daher gibt es in dieser Liste es das Tabellenblatt Statistik, in dem alle eingetragenen Ordner zusammenfasst werden. Das heißt, es werden pro Stellplatz nicht alle Ordner im Einzelnen angezeigt, sondern nur Ort=Kellerraum, Stellplatz, Anzahl der Ordner und Ordnernummer von 1-? für den entsprechenden Klienten. Die Daten dieses Tabellenblatts Statistik sollen in die Hauptliste übergetragen werden.
Hauptliste = Lagerwirtschaft
In dieser Hauptliste sollen die Daten aller Unterlisten stehen. Und zwar im Tabellenblatt Übertrag. Dieses Tabellenblatt ist von Spalte A-R genau so aufgebaut wie das Tabellenblatt Statistik in der Unterdatei.
In der Hauptliste kann auch gesehen werden, welche Stellplätze belegt sind, wo noch Platz für neue Ordner ist.
Erläuterungen zum Wunschcode
Mein erster Ansatz war ein Import:
Der nachfolgende Code klappt super für den Import. Das heißt, wenn ich die Hauptdatei geöffnet habe, dann kann ich dort eine oder auch mehrere Unterdateien importieren. Leider sind die Unterdateien aber so komplex, dass es einfach zu lange dauert, bis sie importiert werden. Grund: Es wird Code beim Öffnen und Schließen ausgeführt.
Daher habe ich mit gedacht, dass es vielleicht sinnvoller wäre, die Daten nach Änderung in der Unterdatei direkt in die Hauptdatei zu übertragen. Das könnte man sicherlich auch einfach mit Copy und Paste machen, aber es arbeiten viele Menschen mit diesen Listen und es gibt „Einige“, die dann immer die Dateien zerstören, weil sie sich den Ablauf „Bereich A-R kopieren und Werte einfügen am Ende der Tabelle“ nicht merken können. Ein weiteres Problem ist, dass sie es auch vergessen die Daten in der Hauptliste zu aktualisieren, so dass diese nicht auf dem neusten Stand ist. Was wiederum dazu führt, dass die Ordner wieder nicht gefunden werden können.
Deshalb will ich das automatisieren.
Also wenn etwas geändert wird, soll der Benutzer darauf aufmerksam gemacht werden, es in die Hauptliste einzutragen. Hat er nur etwas nachgesehen, soll er die Datei ohne Übertrag schließen können. Wenn die Änderungen übertragen werden sollen, dann soll das eben automatisch erfolgen.
Das komplizierte Öffnen und Schließen der Dateien resultiert daraus, dass manchmal mehrere Benutzter die Hauptdatei geöffnet haben, um dort etwas nachzusehen. Dann würde es wahrscheinlich ein Problem mit dem Code geben. Da wir immer ein Backup vom Vortag machen, ändert sich täglich der Name der Hauptdatei. (Dateiname + Datum).
Code für den Import, den ich mir aus 2 vorhandenen Codes zusammengebastelt habe:
 Dim wbQuelle As Workbook, Quelle As Worksheet, Ziel As Worksheet
Dim Datei As Variant, varDateien
Dim Zeile_Z As Long, Zeile_Q1 As Long, Zeile_Q2 As Long
Dim rngZelle As Range
Dim objName As Name, varLinks As Variant, i As Integer
On Error GoTo Fehler
'Dialog "Datei öffnen" anzeigen
varDateien = Application.GetOpenFilename("Excel Dateien (*.xls; *.xlsx; *.xlsm)," & _
"*.xls; *.xlsx; *.xlsm", _
Title:="Bitte zu importieren Datei(en) auswählen", MultiSelect:=True)
'Abbrechen falls keine Datei ausgewählt
If Not IsArray(varDateien) Then
MsgBox "keine Datei ausgewählt", , "Abbruch"
Exit Sub
End If
Application.ScreenUpdating = False
Set Ziel = ThisWorkbook.Worksheets("Übertrag")
With Ziel
'Startzeile setzen
Set rngZelle = .Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlValues, _
lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious)
If rngZelle Is Nothing Then
Zeile_Z = 19
Else
Zeile_Z = rngZelle.Row
End If
If Zeile_Z = Zeile_Q1 Then
'Formeln im Quell-Tabellenblatt durch Werte ersetzen
With .UsedRange
.Value = .Value
End With
'Namen in Quelle löschen
For Each objName In wbQuelle.Names
If objName.Visible = True Then
objName.Delete
End If
Next
'Links in Quelle löschen
varLinks = wbQuelle.LinkSources(xlExcelLinks)
If Not IsEmpty(varLinks) Then
For i = 1 To UBound(varLinks)
wbQuelle.BreakLink varLinks(i), xlExcelLinks
Next i
End If
'kopieren und einfügen (nur Formeln und Formate)
'kopieren und einfügen
Application.DisplayAlerts = False
'     .Range(.Rows(Zeile_Q1), .Rows(Zeile_Q2)).Copy
.Range(.Cells(Zeile_Q1, 1), .Cells(Zeile_Q2, 19)).Copy
Application.DisplayAlerts = False
'        Ziel.Cells(Zeile_Z, 1).PasteSpecial Paste:=xlPasteFormats
Ziel.Cells(Zeile_Z, 1).PasteSpecial Paste:=xlPasteValues
Application.DisplayAlerts = True
Application.CutCopyMode = False 'Zwischenablage leerräumen
'nächste Einfügezeile
Zeile_Z = Zeile_Z + Zeile_Q2 - Zeile_Q1 + 1
End If
End With
NextDatei:
wbQuelle.Close savechanges:=False
'Speicher freigeben
Set Quelle = Nothing
Set wbQuelle = Nothing
Next Datei
Application.ScreenUpdating = True
Fehler:
With Err
Select Case .Number
Case 0 'alles OK
Case Else
If Not wbQuelle Is Nothing Then wbQuelle.Close savechanges:=False
MsgBox "FehlerNr.: " & .Number & vbNewLine & vbNewLine _
& "Beschreibung: " & .Description, _
vbCritical, "Fehler"
End Select
End With
'Speicher freigeben
Set Quelle = Nothing
Set wbQuelle = Nothing
Set Ziel = Nothing
' +++++ Meldung wie viele Dateien eingefügt wurden  ++++++++
MsgBox "Es werden " & UBound(varDateien) & " Dateien eingefügt.", 64
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Call Makro_Formeln_Ergänzen
End Sub

Das kriege ich nicht hin:
Ich schaffe es einfach nicht den Code so umzuschreiben, dass er sozusagen umgekehrt funktioniert.
Die Übertragung soll nicht von der Hauptdatei erfolgen, sondern das Ereignis soll von der Unterdatei ausgelöst werden.
Alle Codes die ich gefunden haben z.B. immer feste Dateinamen oder Pfadangaben, und ich kann es einfach nicht so um basteln, dass es „allgemeiner“ wird. Wie gesagt die Hauptdatei ändert täglich ihren Namen. Ich will auch keine festen Pfadangaben, weil es sein kann, dass jemand die Dateien „umorganisiert“.
Das hätte ich gerne:
Ich habe jetzt erst einmal eine Zwischenlösung programmiert:
Beide Dateien sind geöffnet und zwar nicht schreibgeschützt, weil jemand anderes an der Datei arbeitet.
Arbeitsweise:
Unterdatei: Benutzer klickt auf einen Button mit dem Code:
Private Sub cmdKopierenfürÜbertrag_Click()
With Sheets("Statistik")
.Range(.Cells(13, 1), .Cells(13, 19).End(xlDown)).Copy
'             Sheets("Statistik").Cells(1, 1)
End With
End Sub
Hauptdatei: Er wechselt zu Hauptdatei und klickt einen Button mit folgendem Code:
Private Sub cmdÜbertragEinfügen_Click()
Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
Call Makro_Formeln_Ergänzen
End Sub
Das ist im Prinzip genau das was ich gerne automatisch in einem Vorgang hätte. Mit einem Hinweis beim Schließen der Unterdatei, damit nicht vergessen wird, die Daten in der Hauptdatei zu aktualisieren.
Ich erhoffe mir eine Menge Zeitersparnis, weil nicht eine Person jeden Tag alle Unterdateien aktualisieren muss, sondern jeder Einzelne Mitarbeiter es dann macht, wenn auch tatsächlich Änderungen in der Unterdatei erfolgt sind. Wie bereits gesagt, dauert der Import einer UnterDatei bereit ca. 2 Minuten (auch wenn Berechnung ausgeschaltet ist). Da kann man sich ausrechnen, wie lange es dauern würde 50 Dateien täglich zu aktualisieren.
Jetzt ist meine Beschreibung doch sehr umfangreich geworden, aber ich hoffe es hilft der „Völkerverständigung“
Danke für Eure Mühe
Anja

Anzeige
AW: Grundlagen der Programmierung ist,
30.10.2015 01:00:45
Jürgen
Hallo Anja,
liegt die Hauptdatei im gleichen Verzeichnis wie die Unterdateien?
Wenn die Hauptdatei täglich aktualisiert wird, könntest Du ja nach Filedatum suchen.
Sub Dateidatum()
Dim strPath As String
Dim strFile As String
strPath = activeworkbook.path       'Hier kann auch der Pfad fest hinterlegt werden.
strFile = dir(strPath & "\" & "Mapp*.xlsm") 'Dateiname entsprechend anpassen!
Do Until strFile = ""
If format(FileDatetime(strFile), "ddmmyy") = format(date, "ddmmyy") Then
workbooks.Open strFile
exit do
End If
strFile = dir
Loop
End Sub
  • Der Pfad der Unterdatei wird ausgelesen.

  • Alle Dateien mit dem hinterlegten Namen werden durchsucht.

  • Hat eine Datei das Datum von Heute() wird die Datei geöffnet.

  • Dies setzt aber voraus, dass morgens die Hauptdatei immer aktualisiert wird! ;-)
    Eine Alternative wäre evtl. ein Arry in dem die Namen und das Filedatum hinterlegt werden, in dem man nach dem neuesten Datum sucht.
    (Thema Array sortieren)
    Gruß
    Jürgen

    Anzeige
    umfangreich...
    30.10.2015 14:51:49
    Michael
    Hi zusammen,
    das grundlegende Problem sehe ich darin: Beide Dateien sind geöffnet und zwar nicht schreibgeschützt, weil jemand anderes an der Datei arbeitet.
    Das ist eben das, was nicht sichergestellt werden kann. Insofern würde ich an Deiner Stelle ernsthaft darüber nachdenken, ob nicht eine Datenbanklösung vorteilhafter wäre, sei es mit Access oder einer "freien" DB wie LibreOffice oder auch intranet-mäßig mit mysql und PHP oder so was.
    Die Logik ist doch, daß Du eigentlich nur *einen* Datenbestand benötigst, den Leute ändern bzw. ansehen können. Da es mit Excel nicht ohne weiteres geht, daß mehrere Leute in *einer* Datei (bzw. mehreren, gleichzeitig geöffneten Instanzen) Änderungen vornehmen, wurde der Umweg über die Unterdatein realisiert, die aber das gleiche logische Problem aufweisen.
    Man bräuchte quasi *ein* Hauptmakro, das alle Daten/Datein "schaukelt": reine "Ansichten" ohne Änderungsmöglichkeit, eine große "Datendatei", die nur geändert wird, wenn einzelne Änderungen eingegeben wurden, uns zwar in einer Art "Eingabemaske".
    In einer Datenbank ist das kein Problem, in Excel schon. Hm, hm. Es gibt da was, ADO:
    http://www.online-excel.de/excel/singsel_vba.php?f=135
    Laß mich mal spinnen:
    DatenAnsicht.xlsm: zunächst "leere" Datei, die beim Öffnen oder auf Knopfdruck die aktuellen Daten aus der eigentlichen Datendatei importiert, also eine Kopie zur reinen Ansicht bzw. Info des Anwenders.
    Diese Datei wird beim Schließen *nicht* gespeichert.
    AlleDaten.xlsx: reine Datendatei, die von DatenAnsicht.xlsm ausgelesen wird. "Auslesen" heißt in diesem Zusammenhang, schlicht das Blatt mit Daten zu kopieren; das geht schnell und quasi "mit einem Befehl".
    AlleDaten.xlsx ist immer nur "kurz" geöffnet, wenn das Tabellenblatt zu DatenAnsicht.xlsm kopiert wird.
    So ist "meistens" ein Schreibzugriff möglich, weil die reine "Datenansicht" mit DatenAnsicht.xlsm erfolgt.
    Unterdatei1..Unterdatei50.xlsm: schreiben wie von Dir konzipiert (wenn es denn funktionieren würde).
    Grundsätzlich wäre es sinnvoll (vor allem, wenn das Makro sich wie im ursprünglichen Konzept in der Hauptdatei befindet), eine Aktualisierungsliste zu führen, in der alle importierten Unterdateien samt Datum gelistet sind. Dann müßte nämlich die "eine Person" nicht 50x2 Minuten warten, sondern Du kannst in der Schleife das Datum (zuletzt geändert/gespeichert) der jeweiligen Unterdatei abfragen und mußt sie nur öffnen, wenn sie sich seit dem letzten Import geändert hat.
    Zu Deinem Codeschnipsel: mit
    Range("A" & Rows.Count).End(xlUp).PasteSpecial xlPasteValues
    

    springst Du in die unterste Zeile, die Daten enthält, d.h., daß diese bei Deinem Makro überschrieben wird!
    Abhilfe z.B. mit einer weiteren Variablen, etwa so:
    Private Sub cmdÜbertragEinfügen_Click()
    Dim zeile as Long
    zeile=Range("A" & Rows.Count).End(xlUp).row + 1
    Range("A" & zeile).PasteSpecial xlPasteValues
    Call Makro_Formeln_Ergänzen
    End Sub
    
    Übrigens habe ich hier noch was gefunden, damit beim Öffnen der Unterdatein *keine* Makros ausgeführt werden: https://www.herber.de/forum/archiv/940to944/942724_Datei_oeffnen_ueber_VBA_ohne_Makros_zu_aktivieren.html
    Überfliege vielleicht generell mal https://www.herber.de/xlfaq/index.html
    insbesondere zu https://www.herber.de/mailing/Daten_aus_geschlossenen_Arbeitsmappen_listen.htm
    Schöne Grüße,
    Michael
    P.S.: Du hast zwar gesagt: Ich habe einen Code geschrieben, der prüft, ob der Datensatz bereits vorhanden ist und dann den älteren Datensatz löscht.
    Davon ist in Deinem Code aber nichts zu sehen.

    Anzeige
    AW: umfangreich...Nachtrag
    01.11.2015 15:55:36
    Michael
    Hi Anja,
    recherchiere doch mal: excel mehrere nutzer gleichzeitig
    Es gibt prinzipiell schon die Möglichkeit, daß mehrere Nutzer gleichzeitig "schreiben", aber das ist durchaus nicht trivial, und Du wirst immer wieder lesen, daß eine "Datenbank" das Mittel der Wahl ist.
    Ich sehe noch zwei Möglichkeiten, um das in Excel wenn schon nicht zu "lösen", aber vielleicht bedienbar zu machen:
    a) siehe oben unter "laß mich spinnen": wie wäre es, nach dem Import neuer Daten in die Datendatei einfach ein "Abdruck" zu erstellen, indem Du alle Daten in eine PDF schreibst? Die kann dann von beliebig vielen Personen lesend geöffnet werden, *ohne* daß ein Zugriff auf die Datendatei erfolgt.
    b) man könnte die Unterdatein, die ja ohnehin Makros enthalten, um eine Log-Funktion erweitern, die mitprotokolliert, was seit dem Öffnen geändert wurde, und die *nur* die geänderten Daten (als Kopie) in eine weitere Datei schreibt. Also: Unterdatei1.xlsm schreibt geänderte Daten in Unterdatei1_Aenderungen.xlsx statt in die Hauptdatei.
    Diese geänderten Daten kannst Du ohne die zeitfressende Makroproblematik in die Hauptdatei importieren und anschließend leeren.
    Auch so könnte es sein, daß Du just zu dem Zeitpunkt löschen willst, in dem ein Anwender was eingibt, aber in der Praxis wird man wahrscheinlich damit leben können, denn die .xlsx ist nicht während der ("lange andauernden") Bearbeitung durch einen Nutzer geöffnet, sondern nur "kurz", wenn sie gespeichert wird.
    Andererseits dürfte das Importieren und löschen der Änderungsdatein pro Datei in Millisekunden erledigt sein, so daß Du wahrscheinlich niemanden behinderst, wenn Du dreimal täglich in die Hauptdatei importierst.
    Trotzdem: alles nur Krücken, eine DB ist je nach Datenvolumen auf lange Sicht wahrscheinlich besser.
    Schöne Grüße,
    Michael

    Anzeige
    AW: umfangreich...Nachtrag
    04.11.2015 12:54:50
    Anja
    An alle Mitdenker,
    ich will mich erst einmal für Eure Mühe bedanken. Da ich noch am Ausprobieren Eurer Lösungsvorschläge kann ich vorerst noch keine "positive" Rückmeldung geben. Ich werde mich aber melden, wenn ich durch bin.
    Danke noch einmal!!!!
    Anja

    309 Forumthreads zu ähnlichen Themen

    Anzeige
    Anzeige
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige