AW: Dateien Verschieben - Vorgaben in Tabellenblatt
25.07.2019 10:07:16
Nepumuk
Hallo Frank,
teste mal:
Option Explicit
Private Declare PtrSafe Function MoveFileA Lib "kernel32.dll" ( _
ByVal lpExistingFileName As String, _
ByVal lpNewFileName As String) As Long
Public Sub Start()
Dim avntValues As Variant
Dim ialngIndex As Long
Dim strFilename As String, strOldPath As String, strNewPath As String
With Worksheets("Tabelle1") 'Tabellennamen anpassen !!!
avntValues = .Range(.Cells(4, 1), .Cells( _
.Cells(.Rows.Count, 2).End(xlUp).Row, 40)).Value
End With
For ialngIndex = LBound(avntValues, 1) To UBound(avntValues, 1)
If Not IsEmpty(avntValues(ialngIndex, 2)) Then
strFilename = avntValues(ialngIndex, 2) & "." & avntValues(ialngIndex, 40)
strOldPath = avntValues(ialngIndex, 36)
If Right$(strOldPath, 1) "\" Then strOldPath = strOldPath & "\"
strNewPath = avntValues(ialngIndex, 35)
If Right$(strNewPath, 1) "\" Then strNewPath = strNewPath & "\"
If Dir$(PathName:=strOldPath & strFilename) vbNullString Then
If Dir$(PathName:=strNewPath & strFilename) vbNullString Then
If MsgBox("Die Datei '" & strFilename & _
"' existiert bereits im Zielverzeichniss." & _
vbLf & vbLf & "Überschreiben?", vbQuestion Or _
vbYesNo, "Abfrage") = vbYes Then
Call Kill(PathName:=strNewPath & strFilename)
Call MoveFile(strOldPath, strNewPath, strFilename)
End If
Else
Call MoveFile(strOldPath, strNewPath, strFilename)
End If
Else
Call MsgBox("Die Datei '" & strFilename & _
"' wurde nicht gefunden.", vbExclamation, "Hinweis")
End If
End If
Next
End Sub
Private Sub MoveFile(ByVal pvstrOldPath As String, _
ByVal pvstrNewPath As String, pvstrFileName As String)
Dim lngReturnValue As Long
On Error GoTo err_exit
lngReturnValue = MoveFileA(pvstrOldPath & pvstrFileName, _
pvstrNewPath & pvstrFileName)
If lngReturnValue = 0 Then Call Err.Raise(Number:=1003, _
Description:="Verschieben fehlgeschlagen")
Exit Sub
err_exit:
Call MsgBox("Fehler: " & CStr(Err.Number) & vbLf & vbLf & _
Err.Description, vbCritical, "Fehlermeldung")
Resume Next
End Sub
Gruß
Nepumuk