Hilfe! - Komplexes Vorgehen bei auto Bestellung

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Hilfe! - Komplexes Vorgehen bei auto Bestellung
von: Jamie
Geschrieben am: 01.06.2015 13:03:07

Liebe Community
Ich habe vor eine weitere Excel Liste zu automatisieren. Es geht hierbei um ein File welches eine Bestellung generiert für über 100 Filialen. Das Ziel hierbei so viel es geht per Knopfdruck umzusetzen. Im einfachen Sinne ist das Excel wie folgt aufgebaut:

  • Filialliste

  • Masterliste

  • Sollmenge

  • Bestand DE

  • Bestellung

  • Filialliste:
    Im Grundsatz einfach nur eine Zusammenstellung inkl. Adressestamm der einzelnen Filialen.
    Masterliste:
    Hierbei wird der tägliche Bestand per Knopfdruck eingelesen (.csv File)
    Der Bestand wird benötigt um ein Abgleich über die Sollmenge zu erstellen.
    Sollmenge:
    Hier wird festgelegt wie viel Stück pro Artikel pro Filiale am Haken liegt.
    Hier haben wir bereits sämtliche verknüpfungen mit Formeln hinterlegt.
    Bestand DE:
    Hierbei wird per Knopfdruck der Bestand von DE eingelesen. (Noch nicht umgesetzt)
    Bestellung:
    Das hier ist unser "Herzstück"! Von hier aus sollte alles automatisiert werden.
    Per Knopfdruck generiert es eine Bestellung und erstellt ein weiteres Tabellenblatt mit der Bestellung.
    Ebenfalls sollte das Tabellenblatt Datum&ViVDE benannt werden.
    Wir konnten bereits vieles umsetzen, jedoch das einlesen vom Bestand sowie der Part von der Bestellung benötige ich Hilfe von Fachleuten.
    Beim generieren der Bestellung müssen zwingend folgende Punkte umgesetzt werden, damit Sie für unser System brauchbar ist.
  • Alle NEGATIV Zahlen sowie Nullen müssen entfernt werden

  • Jede Filiale die kumuliert kleiner als 3 Artikel aufweisen werden gar nicht bei der Bestellung berücksichtigt

  • Nun hoffe ich auf kompetente Hilfe, bin gerne bereit eine Spende dem Forum beizugeben!
    Leider kann ich die Datei hier nicht hochladen, aus diesem Grund habe ich einen Download Link erstellt.
    FTP:
    ftp.CitrusLine.ch
    herbers@CitrusLine.ch
    Passwort NKQTgoCg
    Würde mich sehr freuen!
    Beste Grüsse
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 01.06.2015 13:52:53
    https://www.herber.de/bbs/user/97962.zip
    HIer noch das .csv vom Bestand DE.
    Der Pfad ist immer der gleiche: U://BestandDE/150601.csv
    Gruss
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: fcs
    Geschrieben am: 01.06.2015 15:21:24
    Hallo Jamie,
    ich hab mir deine Datei mal angesehen.
    Die Schritte zur Erzeugubg des Blattes "Datum VivDE" aus dem Blatt "Bestellung" sind mir noch nicht 100% klar.
    Schrittfolge, wie ich sie verstehe:
    1. Blatt "Bestellung" kopieren und umbenennen in "Datum VivDE"
    2. Alle Formeln durch Werte ersetzen, außer in Spalten DJ (Summe), DN (Verfügbar Au), DO (Differenz) und in der Zeile 165 (Summe der Artikel-Stückzahl)
    3. Bei den Artikeln/Filialen alle Bestellanzahlen <=0 löschen
    4. Bei allen Filialen, die kumuliert kleiner als 3 Artikel aufweisen, die Bestellwerte löschen.
    Was bedeutet "kumuliert kleiner als 3 Artikel aufweisen"? Kleiner gleich 3 verschiedene Artikel oder Gesamtstückzahl zu bestellender Artikel <=3
    5. Alle Artikel-Zeilen, die in der Summe 0 Artikel Bestellanzahl haben löschen.
    6. Restliche Formeln durch Werte ersetzen
    7. Schaltflächen löschen falls vorhanden
    Blatt "Bestellung" - Formeln in Spalten DN und DO.
    Die Formeln in diesen Spalten liefern Fehler. Das musst du noch bereinigen.
    ..., bin gerne bereit eine Spende dem Forum beizugeben!
    Ein bischen Taschengeld für einen Hobbyprogrammierer ist nie verkehrt.
    Gruß
    Franz

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 01.06.2015 15:37:25
    Hallo Franz
    1. Ja genau, perfekt!
    2. ebenfalls genau richtig
    3. korrekt!
    4. Wenn eine Filiale weniger als 3 Artikel (Gesamthaft) hat lohnt sich der Versand nicht mehr.
    5 - 7. absolut korrekt
    Was noch fehlt ist der Import für den Bestand, ansonsten absolut richtig!
    Für die Spende schick mir deine Bankdaten!
    Gruss & ein Riesen Dankeschön!
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: fcs
    Geschrieben am: 02.06.2015 07:36:55
    Hallo Jamie,
    in der Textdatei findest du Makros
    - für das Erstellen einer Bestellung
    - das Laden der Daten aus der CSV-Datei ins Blatt "Bestand DE"
    https://www.herber.de/bbs/user/97977.txt
    Wegen Taschengeld:
    Schick mir bitte eine E-Mail an die E-Mail-Adresse in meinem Profil, dann kommen in der Antwort meine Konto-Daten.
    Gruß
    Franz

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 02.06.2015 08:26:08
    Guten Morgen Franz
    Als erstes möchte ich dir vorab mein Dank aussprechen.
    Zum Zweiten sende ich dir gleich eine E-Mail.
    Nun habe ich noch kurz zwei drei Fragen :)
    Beim Bestand DE einlesen, nimmt er jeweils die aktuellste Datei im Ordner?
    Kann mann noch ein Popup einbauen wo man sehen kann, welches File gerade eingelesen wurde?

    Sub Bestand_Einlesen()
        
        Dim varPfad, wkbCSV As Workbook, wksCSV As Worksheet
        Dim wksBestand As Worksheet
        Dim Zeile_L As Long
        Dim varTXT
        
        On Error GoTo Fehler
        
        If MsgBox("Blatt ""Bestand DE"" mit Daten aus CSV-Datei aktualisieren?", _
                vbQuestion + vbOKCancel, _
                "Daten aus CSV-Datei in ""Bestand DE"" einlesen") = vbCancel Then Exit Sub
        'Makrobremsen lösen
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            StatusCalc = .Calculation
            .Calculation = xlCalculationManual
        End With
        
        varPfad = "U://BestandDE/150601.csv" 'Verzeichnis-Trennzeichen ggf. anpassen!!
    '    varPfad = "C:\Users\Public\Test\Jamie\150601.csv"
        
        varTXT = VBA.Replace(varPfad, ".csv", ".txt")
        
        'CSV-Datei temporär als TXT-Datei kopieren - erforderlich, da zumindest mit deutschen _
                Systemeinstellungen die Daten sonst nicht korrekt in Excel geladen werden
        VBA.FileCopy varPfad, varTXT
        
        Set wksBestand = ActiveWorkbook.Worksheets("Bestand DE")
        
        
        'Altdaten in "Bestand DE" löschen
        With wksBestand
            Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
            If Zeile_L >= 3 Then
                .Range(.Rows(3), .Rows(Zeile_L)).ClearContents
            End If
        End With
        
        Application.Workbooks.OpenText Filename:=varTXT, origin:=xlWindows, DataType:=xlDelimited,  _
    _
            Tab:=False, Semicolon:=True, Comma:=False, Space:=False, Other:=False, _
            Fieldinfo:=Array(Array(1, 1), Array(2, 2), Array(3, 2), Array(4, 1)), _
            DecimalSeparator:=".", ThousandsSeparator:="'"
        
        Set wkbCSV = ActiveWorkbook
        Set wksCSV = wkbCSV.Worksheets(1)
        With wksCSV
            Zeile_L = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range(.Cells(3, 1), .Cells(Zeile_L, 4)).Copy Destination:=wksBestand.Cells(3, 1)
        End With
        
        wkbCSV.Close savechanges:=False
        'temporäre Textdatei wieder löschen
        VBA.Kill varTXT
    Fehler:
    'Fehlerbehandlung
        With Err
          Select Case .Number
            Case 0 'kein Fehler
            Case 76
              MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description & vbLf & vbLf & varPfad
            Case Else
              MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description
          End Select
        End With
        
        'Makrobremsen zurücksetzen
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = StatusCalc
        End With
    End Sub
    Ich habe bei der Bestellung festgestellt, dass ich von drei verschiedenen Lager den Bestand ziehen muss.
    leider ist jedes File davon anders aufgebaut.
    Gruss & Danke
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: fcs
    Geschrieben am: 02.06.2015 12:59:05
    Hallo Jamie,
    Beim Bestand DE einlesen, nimmt er jeweils die aktuellste Datei im Ordner?
    NEIN - Ich das Makro angepasst
    Kann mann noch ein Popup einbauen wo man sehen kann, welches File gerade eingelesen wurde?
    JA - Ich hab das Makro angepasst. Statt der einfachen MsgBox wird jetzt ein Dateiauswahldialog angezeigt mit der gefundenen neuesten Datei als Vorgabewert.
    Ich habe bei der Bestellung festgestellt, dass ich von drei verschiedenen Lager den Bestand ziehen muss.
    leider ist jedes File davon anders aufgebaut.

    Dann benötigst du entweder 3 verschiedenen Einlese-Makros oder in einem Makro gibt es eine zuzätzliche Ausahlmöglichkeit für das Lager. Ich hab im angepassten Makro mal die 2. Variante eingebaut.
    Das Makro kopiert dann je nach Lager nur bestimmte Spalten aus der CSV in die "Bestand DE", so dass die mit "Bestand DE" verknüpften Formeln korrekt funktionieren.
    Gruß
    Franz
    https://www.herber.de/bbs/user/97985.txt

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 02.06.2015 13:22:57
    Hallo Franz
    Ich habe das File wie es nun aussehen würde auf den FTP gestellt.
    Ich erhalte so jedoch eine Fehlermeldung.
    Müsste ich den Code auf einen Button konfigurieren?
    Wie genau kann ich die Zellen bestimmen sowie Lager A, B, C ?
    Beste Grüsse
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: fcs
    Geschrieben am: 03.06.2015 08:14:41
    Hallo Jamie,
    ich hab dir eine E-Mail mit einer angepassten Datei geschickt.
    Ich erhalte so jedoch eine Fehlermeldung.
    Welche Fehlermeldung?
    Müsste ich den Code auf einen Button konfigurieren?
    Nein einfacher ist hier für jedes Lager eine eigene Schaltfläche mit separatem Makro.
    Gleichartige Funktionen in den Makros sollten dann aberin Functions/Subs ausgelagert werden.
    Wie genau kann ich die Zellen bestimmen sowie Lager A, B, C ?
    Wenn jedes Lager ein eigenes Verzeichnis für die CSVs hat ist dadzrch das Lager schon festgelegt.
    Die Zellbereiche müssen dann im Code in den entsprechenden Zeilenfür Löschen-Altdaten und Kopieren der neuen CSV-Daten angepasst werden.
    Gruß
    Franz

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 03.06.2015 08:36:24
    Lieber Franz
    Ich habe noch kein E-Mail von dir erhalten, für die Spende und gemäss deiner angepassten Datei.
    Kann es sein das es zu gross war? Soll ich dir eine andere E-Mail Adresse angeben?
    Daher wäre also eine Schaltfläche zu einem Modul verknüpft.
    z.B. Schaltfläche Bestand DE - Modul (BestandDE)
    Dort kann ich dann sagen, welche Zahlen er aktualisieren muss?
    Was passiert, wenn in DE ein neuer Artikel eingelagert wird? erkennt dann das Modul das ein neuer Artikel hinzugekommen ist?
    Dann habe ich noch eine Frage.
    Wir haben ja bereits gelöst, dass jede Filiale welche weniger als 3 Artikel aufweist bei der Bestellung nicht berücksichtig wird.
    Nun haben wir bei Spalte DJ jeweils die Menge welche benötigt wird.
    Gehen wir davon aus, verschiedene Filialen benötigen kumuliert 200 Stück. Laut Lager haben wir jedoch nur 197 Stück.
    kann man im Modul (VBA Script) einbauen, dass die Verteilung automatisch stattfindet? In diesem Fall wären es z.B. 3 Filialen welche jeweils 1 Stück weniger erhalten.
    Lieber Gruss
    Jamie

    Bild

    Betrifft: AW: Hilfe! - Komplexes Vorgehen bei auto Bestellung
    von: Jamie
    Geschrieben am: 03.06.2015 08:55:07
    Servus Frank
    könnte ich auch diesen Script verwenden für den import der Lagerbestände?
    der Pfad des jeweiligen .csv ist immer der Selbe. Somit müsste ich jeweils den Pfad anpassen oder?
    Mann müsste sicher die Range auf die neuen Files anpassen..
    Dies wäre nur alternativ zu deiner Lösung. Aber ich warte mal ab :)
    LG

    Option Explicit
    Sub Lese_CSV()
    Dim sFileName$
    On Error GoTo ErrorHandler:
    Const sPath$ = "\\DBSRV01\FTP_Extern\Fust\bestand\"
    sFileName = Format(Date, "yymmdd") & "_bestand"
    Events_ False
    With Tabelle4
        .UsedRange.ClearContents
        With .QueryTables.Add(Connection:="TEXT;" & sPath & sFileName & ".csv", Destination:=.Range( _
    "$A$1"))
            .Name = sFileName
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 1250
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .Refresh BackgroundQuery:=False
        End With
        If Not .AutoFilterMode Then .UsedRange.Rows(3).AutoFilter
    End With
    Events_ True
    MsgBox sFileName & ".csv wurde eingelesen"
    Exit Sub
    ErrorHandler:
    Events_ True
        MsgBox Err.Description, _
               vbExclamation + vbMsgBoxSetForeground + vbMsgBoxHelpButton, _
               "Error: " & Err.Number, Err.HelpFile, Err.HelpContext
    End Sub
    Sub Makro4()
    '
    ' Makro4 Makro
    '
    '
        Range("C2:C3").Select
        Range("C3").Activate
    End Sub
    
    


    Bild

    Betrifft: Komplexes Vorgehen bei Bestellung
    von: fcs
    Geschrieben am: 03.06.2015 10:25:24
    Hallo Jamie,
    Ich habe noch kein E-Mail von dir erhalten, für die Spende und gemäss deiner angepassten Datei.
    Kann es sein das es zu gross war? Soll ich dir eine andere E-Mail Adresse angeben?

    bei mir ist die Email raus.
    Datei-Anhang ca. 2,2 MB - bereitet normal bei mir keine Probleme.
    Ich versuch es heute Abend noch einmal.
    ggf. getrennt und nur Makros als Text-Datei(en).
    Daher wäre also eine Schaltfläche zu einem Modul verknüpft.
    z.B. Schaltfläche Bestand DE - Modul (BestandDE)
    Dort kann ich dann sagen, welche Zahlen er aktualisieren muss?

    Ja für jedes Lager eine Schaltfläche und ein Haupt-Makro plus ein paar Untermakros, die mit Parametern gefüllt werden für:
    - Suche neueste CSV im Verzeichnis
    - Anzeige/Auswahl neueste/gefundene CSV-Datei im Datei-Dialog
    - Löschen der Alt-Daten
    Es ist nicht unbedingt je Schaltfläche ein Modul erforderlich, aber es erhöht ggf. die Übersicht im VBA-Projekt.
    Was passiert, wenn in DE ein neuer Artikel eingelagert wird? erkennt dann das Modul das ein neuer Artikel hinzugekommen ist?

    Ja, WorkBooks.OpenText... lädt immer die Daten aus allen Zeilen der Text/CSV-Datei und es werden auch immer alle Zeilen unterhalb der Titel-Zeile in die Bestandsblätter kopiert.

    Dann habe ich noch eine Frage.
    Wir haben ja bereits gelöst, dass jede Filiale welche weniger als 3 Artikel aufweist bei der  _
    Bestellung nicht berücksichtig wird.
    Nun haben wir bei Spalte DJ jeweils die Menge welche benötigt wird.
    Gehen wir davon aus, verschiedene Filialen benötigen kumuliert 200 Stück. Laut Lager haben wir  _
    jedoch nur 197 Stück.
    kann man im Modul (VBA Script) einbauen, dass die Verteilung automatisch stattfindet? In diesem  _
    Fall wären es z.B. 3 Filialen welche jeweils 1 Stück weniger erhalten. 
    

    Das kann man umsetzen.
    Da müssten aber noch weitere Randbedingungen definiert werden:
  • Bei welchen Filialen soll abgezogen werden?
    Die, die eine große Anzahl benötigen/anfordern, oder die, die wenig anfordern/benötigen.

  • Soll diese Reduzierung der Auslieferung bis zu beliebigem Fehlbestand im Lager durchgeführt werden? Oder nur bis zu einem bestimmten Prozentsatz des Lagerfehlbestands? Es macht ja wenig Sinn die Verteilung zu reduzieren wenn 200 Stück angefordert werden aber der Bestand nur 20 Stück sind - dann enden die meisten Filialen bei 1 oder 0

  • Im Extremfall sind hierbei bei einzelnen Filialen die Mindestest-Artikelzahl auf unter gleich 3, was dann?

  • Die Alternative, Laden der Daten per Abfrage, ist grundsätzlich machbar. Du musst dann aber mal prüfen, ob sich das mit den Formeln im Blatt Bestellung verträgt.
    Außerdem musst du mal prüfen, ob die Zahlenwerte mit 1000er-Trennzeichen korrekt eingelesen werden und wie die Artikel-Nummern interpretiert werden (Text oder Zahlen). Wenn als Text, dann muss man nachbereiten, damit die SVERWEIS-Funktionen in "Bestellung" korrekt funktionieren.
    der Pfad des jeweiligen .csv ist immer der Selbe. Somit müsste ich jeweils den Pfad anpassen oder?
    Mann müsste sicher die Range auf die neuen Files anpassen..
    Wenn die Dateinamen nicht mit dem zu ladenden Datum beginnen, dann müsste man meine Lösung für die Suche nach der neuesten CSV einbauen.
    Die Range braucht man eigentlci nicht anzupassen, das macht Excel alles automatisch.
    Nur beim Auto-Filter muss man ggf. etwas nachhelfen, damit Excel das macht, was man möchte.
    Gruß
    Franz

    Bild

    Betrifft: AW: Komplexes Vorgehen bei Bestellung
    von: Jamie
    Geschrieben am: 03.06.2015 11:32:36
    Hallo Franz
    ich habe soeben deine E-Mail erhalten und bereits ein Feedback gegeben! :)

    Das kann man umsetzen.
    Da müssten aber noch weitere Randbedingungen definiert werden:
    Bei welchen Filialen soll abgezogen werden?
    Die, die eine große Anzahl benötigen/anfordern, oder die, die wenig anfordern/benötigen.
    Filiale welche mehr als 1 benötigen oder wenn eine Filiale mehr als 5 Artikel bestellt.
    Soll diese Reduzierung der Auslieferung bis zu beliebigem Fehlbestand im Lager durchgeführt werden? Oder nur bis zu einem bestimmten Prozentsatz des Lagerfehlbestands? Es macht ja wenig Sinn die Verteilung zu reduzieren wenn 200 Stück angefordert werden aber der Bestand nur 20 Stück sind - dann enden die meisten Filialen bei 1 oder 0
    Das dürfte eigentlich nicht passieren. Aber selbst dann müsste die Verteilung passieren wie oben geschrieben.
    Im Extremfall sind hierbei bei einzelnen Filialen die Mindestest-Artikelzahl auf unter gleich 3, was dann?
    Sollte dies passieren, dann werden wir dafür keine Bestellung generieren, weil sich der Versand nicht mit dem Profit decken würden. In der Regel hat jedoch eine Filiale mind. 10 Artikel im Durchschnitt da wir nur einmal pro Woche liefern.
    Nun kam gleich noch zwei weitere Punkte auf, welches direkt beim betätigen des „Bestellung erstellen“ Button integriert werden sollte.
    pro Lager eine Bestellung
  • Bestand DE (Datum&VivDE - [Titel Tabellenblatt])

  • Bestand CH (Datum&VivCH - [Titel Tabellenblatt])

  • Bestand Strax (Datum&Strax - [Titel Tabellenblatt])

  • Pro Bestellung ein Email öffnen mit dem Betreff = Tabellenblatt Name und ein .csv generieren mit dem Tabellenblatt Name
    Zeilen:

  • Zeile 1 (Lieferadresse)

  • Zeile 2 (Rechnungsadresse)

  • Zeile 3 (Leer)

  • Artikel & Menge pro Filiale (Wie Bestellung)

  • Spalten:
  • D-G müssen gelöscht werden

  • DJ-DO müssen gelöscht werden


  • Bild

    Betrifft: AW: Komplexes Vorgehen bei Bestellung
    von: fcs
    Geschrieben am: 03.06.2015 16:14:24
    Hallo Jamie,
    lass uns die weiteren Anpassungen jeweils per E-Mail austauschen. Ich werde dann jeweils Abends checken, ob es was neues gibt.
    Kurze Frage noch: Welches E-Mail-Programm wird für den Versand benutzt?
    Bei Outlook könnte ich wahrscheinlich die Mail-komplett per Makro für den Versand vorbereiten. Bei anderen E-Mail-Programmen ist es etwas schwieriger.
    Gruß
    Franz

    Bild

    Betrifft: AW: Komplexes Vorgehen bei Bestellung
    von: Jamie
    Geschrieben am: 04.06.2015 07:44:42
    Hallo Franz
    Es betrifft in unserem Fall das Outlook 2010.
    Per Mail passt mir gut!
    LG

     Bild

    Beiträge aus den Excel-Beispielen zum Thema "Hilfe! - Komplexes Vorgehen bei auto Bestellung"