Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Nachtrag zu Datei mit festem und variablen Namenst

Betrifft: Nachtrag zu Datei mit festem und variablen Namenst von: Frank Berends
Geschrieben am: 31.07.2014 17:26:39

Hallo zusammen,
hallo Luschi,

da ich es mit der Antwortfunktion noch nicht so wirklich auf die Reihe bekommen (wie genau geht das und gibt es da ein Zeitfenster?), hier ein neuer Beitrag. (Ich weiß, gehört sich eigentlich nicht so.)

Ich hatte hier (https://www.herber.de/forum/archiv/1368to1372/t1371706.htm) bereits meine Anfrage gestellt. Leider kann ich mit der Antwort so noch nichts anfangen. Auf unseren PCs können Skripe nicht so einfach ausgeführt werden. Außerdem liegt die Datei auf einem externen Server.

Was mir bei der Anfrage eher vorschwebte war, dass

a) "mein" Excel beim Starten in den Ordner schaut und die neueste Datei mit dem Namen Kundenstammdaten*.xlsx aufruft und als Kundenstammdaten.xlsx wieder speichert. Dabei soll die vorhandene Datei ohne Nachfragen überschrieben werden.

oder

b) Excel bei Abfragen wie dieser hier:

=WENNFEHLER(INDEX('Q:\rz\bank21-Reporting\[Kundenstammdaten.xlsx]page'!$A:$BA;
VERGLEICH(Personendaten_Kundennr.;'Q:\rz\bank21-Reporting\[Kundenstammdaten.xlsx]page'!$A:$A;0);
VERGLEICH("Telefon privat";'Q:\rz\bank21-Reporting\[Kundenstammdaten.xlsx]page'!$1:$1;0));"")


nicht sturr auf "Kundenstammdaten.xlsx" schaut, sonder die neueste Datei mit dem Bestandteil "Kundenstammdaten" auswählt.

Geht das so, wie ich mir das vorstellt. Lösung b) wäre die praktischere von beiden für mich.

  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: fcs
Geschrieben am: 01.08.2014 12:04:24

Hallo Frank,

Variante b) funktioniert mit variablen Dateinamen nicht.

Für Variante a) könnte man in die persönlche Makroarbeitsmappe entsprechende Makros einbauen, die beim Starten von Excel automatisch starten. Das Makro öffnet die Dateien nicht sondern sucht nach der neuesten Datendatei, löscht dann die alte Stammdatei, und kopiert die neueste gefundene als Stammdatei.
Verzeichnis und Dateinamen musst du ggf. anpassen

Gruß
Franz

'Erstellt unter Excel2010 , Windows 7
'#######                                                     ######
'Code in der Persönlichen Makroarbeitsmappe unter "DieseArbeitsmappe"
Private Sub Workbook_Open()
  Call UpdateKundenstamm
End Sub


'######                                                      ######
'Code in der Persönlichen Makroarbeitsmappe in einem allgemeinen Modul
Sub UpdateKundenstamm()
  'Neueste Kundenstammdaten-Datei setzen
  Dim strDatei As String
  Dim strNeu, strDatum As String
  Dim strVerzeichnis As String
  Dim strName As String
  
  'Verzeichnis mit den Kundenstammdaten -  anpassen !!!
  strVerzeichnis = "D:\Test\Daten"
  
  'Dateiname bis zum Beginn des Datums    -  ggf. anpassen !!!
  strName = "Kundenstammdaten.xlsx-de-de."
  
  'neue Stammdatendatei(en) suchen
  strDatei = Dir(strVerzeichnis & "\" & strName & "*.xlsx")
  If strDatei <> "" Then
    'neueste Stammdatendatei ermitteln
    strNeu = strDatei
    strDatum = Mid(strDatei, Len(strName) + 1, 8)
    Do
      strDatei = Dir
      If strDatei = "" Then Exit Do
      If strDatum < Mid(strDatei, Len(strName) + 1, 8) Then
        strNeu = strDatei
        strDatum = Mid(strDatei, Len(strName) + 1, 8)
      End If
    Loop
    'Vorhandene Kundenstammdaten-Datei löschen
    If Dir(strVerzeichnis & "\Kundenstammdaten.xlsx") <> "" Then
      VBA.Kill strVerzeichnis & "\Kundenstammdaten.xlsx"
    End If
    'neue Datei als Stammdatendatei kopieren
    VBA.FileCopy strVerzeichnis & "\" & strNeu, strVerzeichnis & "\Kundenstammdaten.xlsx"
  End If
  
End Sub



  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: Frank Berends
Geschrieben am: 01.08.2014 14:52:06

Hallo Franz,

das Makro läuft, besten Dank.

Ich habe den Code von Dir etwas geändert und auf FileDateTime abgestellt sowie (bis auf weiteres) eine Sicherheitsabfrage eingebaut. Man will sich im Echtbetrieb ja nicht gleich die Daten zerschießen ;-).

Was meinst Du dazu?

Gruß
Frank

Sub UpdateKundenstamm()
  'Neueste Kundenstammdaten-Datei setzen
  Dim strDatei As String
  Dim strNeu As String
  Dim strDatum As Date
  Dim strVerzeichnis As String
  Dim strName As String
  
  'Verzeichnis mit den Kundenstammdaten -  anpassen !!!
  strVerzeichnis = "Q:\rz\bank21-Reporting"
  
  'Dateiname bis zum Beginn des Datums    -  ggf. anpassen !!!
  strName = "Kundenstammdaten.xlsx-de."
  
  'neue Stammdatendatei(en) suchen
  strDatei = Dir(strVerzeichnis & "\" & strName & "*.xlsx")
  If strDatei <> "" Then
    'neueste Stammdatendatei ermitteln
    strNeu = strDatei
    strDatum = FileDateTime(strDatei)
    Do
      strDatei = Dir
      If strDatei = "" Then Exit Do
      If strDatum < FileDateTime(strDatei) Then
        strNeu = strDatei
        strDatum = FileDateTime(strDatei)
      End If
    Loop
    
    Frage = MsgBox("Ist dies die aktuellste Datei: " & strNeu, vbOKCancel)

        If Frage = vbCancel Then
            MsgBox ("Vorgang wird beendet")
            Exit Sub
        End If

    'Vorhandene Kundenstammdaten-Datei löschen
    If Dir(strVerzeichnis & "\Kundenstammdaten.xlsx") <> "" Then
      VBA.Kill strVerzeichnis & "\Kundenstammdaten.xlsx"
    End If
    'neue Datei als Stammdatendatei kopieren
    VBA.FileCopy strVerzeichnis & "\" & strNeu, strVerzeichnis & "\Kundenstammdaten.xlsx"
  End If
  
End Sub



  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: fcs
Geschrieben am: 01.08.2014 15:56:54

Hallo Frank,

die Verwendung des Speicherdatums der Datei als Kriterium ist auch ok.

Hier gibt es das kleine Risko, dass eine ältere Datendatei mal geöffnet und gespeichert wird. Dann findet das Makro ggf. die falsche Datei. Aber dafür hast du ja die MsgBox. Außerdem bleiben die Datendateien ja erhalten, so dass eigentlich nichts kaputt gehen kann.

Ich hatte ursprünglich noch eine Version in der die "Kundenstammdaten.xlsx" immer umbenannt wird indem das aktuelle Datum eingebaut wird. So konnte man prüfen, ob am aktuellen Tag schon eine neue Kundenstammdaten.xlsx angelegt wurde. Aber dabei werden dann im Laufe der Zeit viele Old-Dateien als Datenmüll angelegt. Das hab ich dann wieder verworfen.

Mit der jetzigen Version kann es zwar vorkommen, dass, wenn Excel mehrmals am Tag neu gestartet wird, die "Kundenstammdaten.xlsx" mehrmals neu angelegt wird, aber das stört ja nicht weiter.

Gruß
Franz


  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: Frank Berends
Geschrieben am: 01.08.2014 16:07:37

Hallo Franz,

das Thema Old-Dateien ist vielleicht auch noch interessant. Kannst Du Excel dazu bewegen, immer die zweitälteste Datei zu löschen?

Vom Ablauf der Dateien sieht es so aus, dass jede Nacht automatisch eine Kundenstammdaten(...).xlsx erstellt und gespeichert wird. Die Dateien werden so nicht manuell angefasst, sondern nur in der von Dir erstellen Form in Kundenstammdaten.xlsx.

Mit der Zeit kommt da etwas an Müll zusammen, den ich sonst manuell von Zeit zu Zeit löschen würde. Kann Excel mir das in dem Zusammenhang abnehmen?

Gruß und schönes Wochenende

Frank


  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: fcs
Geschrieben am: 02.08.2014 16:05:20

Hallo Frank,

mit den Anpassungen werden vom Makro alle Datendateien außer der neuesten im Verzeichnis gelöscht.

Gruß
Franz

Sub UpdateKundenstamm()
    'Neueste Kundenstammdaten-Datei setzen
    Dim strDatei As String
    Dim strNeu As String
    Dim datDatum As Date
    Dim strVerzeichnis As String
    Dim strName As String, intD As Integer, arrDatei() As String
    Dim Frage As Variant
    'Verzeichnis mit den Kundenstammdaten -  anpassen !!!
    strVerzeichnis = "Q:\rz\bank21-Reporting"
    
    'Dateiname bis zum Beginn des Datums    -  ggf. anpassen !!!
    strName = "Kundenstammdaten.xlsx-de."
    
    'neue Stammdatendatei(en) suchen
    strDatei = Dir(strVerzeichnis & "\" & strName & "*.xlsx")
    If strDatei <> "" Then
      'Stammdatendateien in Liste erfassen und neueste Datei ermitteln
      Do
        intD = intD + 1
        ReDim Preserve arrDatei(1 To intD)
        arrDatei(intD) = strVerzeichnis & "\" & strDatei
        If datDatum < FileDateTime(arrDatei(intD)) Then
          strNeu = arrDatei(intD)
          datDatum = FileDateTime(arrDatei(intD))
        End If
        strDatei = Dir
      Loop Until strDatei = ""
      
      Frage = MsgBox("Ist dies die aktuellste Datei: " _
                    & Mid(strNeu, InStrRev(strNeu, "\") + 1), vbOKCancel)
  
          If Frage = vbCancel Then
              MsgBox ("Vorgang wird beendet")
              Exit Sub
          End If
  
      'ältere Datei(en) löschen
      For intD = 1 To UBound(arrDatei)
        If arrDatei(intD) <> strNeu Then
          VBA.Kill arrDatei(intD)
        End If
      Next
      Erase arrDatei
      
      'Vorhandene Kundenstammdaten-Datei löschen
      If Dir(strVerzeichnis & "\Kundenstammdaten.xlsx") <> "" Then
        VBA.Kill strVerzeichnis & "\Kundenstammdaten.xlsx"
      End If
      'neue Datei als Stammdatendatei kopieren
      VBA.FileCopy strNeu, strVerzeichnis & "\Kundenstammdaten.xlsx"
    End If
    
End Sub



  

Betrifft: AW: Nachtrag zu Datei mit festem und variablen Namenst von: Frank Berends
Geschrieben am: 04.08.2014 08:29:06

Hallo Franz,

jetzt habe ich, was ich haben wollte.

Besten Dank dafür.

Gruß
Frank


 

Beiträge aus den Excel-Beispielen zum Thema "Nachtrag zu Datei mit festem und variablen Namenst"