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