Hallo zusammen,
ich habe ein kleines Problem:
Um ein konsistentes Versions-Management zu gewährleisten, soll das Workbook bei größeren Veränderungen automatisiert (also mit einem vorgegebenen Namen) abgespeichert werden.
Hierzu mein (gesamter) Code:
Option Explicit
Public ExportDate As Date
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
On Error Resume Next
Dim Canc As Boolean
Datenstand = Range("Datum")
Application.DisplayAlerts = False
If Datenstand <> ExportDate Or FileUser <> Application.UserName Then
Canc = Application.Dialogs(xlDialogSaveAs).show(DateiName)
If Canc = False Then End
Else: Application.Save
End If
Application.DisplayAlerts = True
End Sub
Private Sub Workbook_Open()
ExportDate = Range("Datum")
End Sub
____________________________________________________________________________________
Option Explicit
Dim SheetName As String, stateStr As String
Dim ExportDatum As Date, DatenDatum As Date
Dim projekt As String
Public Property Let stateText(str As String)
stateStr = str
End Property
Public Property Get stateText() As String
stateText = stateStr
End Property
Public Property Let Datenstand(i As String)
ExportDatum = i
End Property
Public Property Get Datenstand() As String
Datenstand = ExportDatum
End Property
Public Property Get DNRS() As String
DNRS = Left(ThisWorkbook.name, InStr(ThisWorkbook.name, ".") - 1)
DNRS = Right(DNRS, Len(DNRS) - (InStr(DNRS, "_")))
DNRS = Left(DNRS, InStrRev(DNRS, "_") - 1)
End Property
Public Property Get ExportTag() As String
ExportTag = Left(Datenstand, InStr(Datenstand, ".") - 1)
End Property
Public Property Get ExportMonat() As String
ExportMonat = Left(Right(Datenstand, InStrRev(Datenstand, ".") + 1), InStr(Right(Datenstand, _
_
InStrRev(Datenstand, ".") + 1), ".") - 1)
End Property
Public Property Get ExportJahr() As String
ExportJahr = Right(Datenstand, InStr(Datenstand, ".") + 1)
End Property
Public Property Get ProjektStr() As String
If Worksheets(newSheet).Range("projekt") Like "*AU*" Then
ProjektStr = "Projekt1"
Else: ProjektStr = "Projekt2"
End If
End Property
Public Property Get FileUser() As String
FileUser = Right(Left(ThisWorkbook.name, InStr(ThisWorkbook.name, ".") - 1), Len(Left( _
ThisWorkbook.name, InStr(ThisWorkbook.name, ".") - 1)) - InStrRev(Left(ThisWorkbook.name, InStr( _
_
ThisWorkbook.name, ".") - 1), "_"))
End Property
Public Property Get FileProjektStr() As String
FileProjektStr = Right(Left(DNRS, InStrRev(DNRS, "_") - 1), Len(Left(DNRS, InStrRev(DNRS, "_" _
_
) - 1)) - InStr(DNRS, "_"))
End Property
Public Property Get FileStarter()
FileStarter = Left(DNRS, InStr(DNRS, "_") - 1)
End Property
Public Property Get FileVersion() As String
FileVersion = Right(Right(DNRS, Len(DNRS) - InStr(DNRS, "_")), Len(Right(DNRS, Len(DNRS) - _
_
InStr(DNRS, "_"))) - InStr(Right(DNRS, Len(DNRS) - InStr(DNRS, "_")), "_"))
End Property
Public Property Get DateiName() As String
DateiName = ExportJahr & ExportMonat & ExportTag & "_" & _
FileStarter & "_" & ProjektStr & "_" & FileVersion & "_" & Application.UserName & ".xls"
End Property
Leider erstellt Excel immer wieder eine resume.xlw-Datei (dachte eigentlich, das wäre mit Application.DisplayAlerts=False ausgeschaltet), wäre ja grundsätzlich egal, aber da der Nutzer gefragt wird, ob er die vorhandene ersetzen will, ziemlich lästig...
Kann mir jemand helfen?
Danke schonmal,
Brigitte |