We help IT Professionals succeed at work.

VB Script: Delete, copy, rename files

Luis Diaz
Luis Diaz asked
on
Hello experts,

I am looking for a vbscript that do the following:

1-Delete xls file with the following name "test.xls” located in Current Directory in which the vbscript is launched
2-Copy a the most recent file from a folder to Current directory begin with the following prefix "AD_report" (Every day there are two reports generated with the same prefix the most recent file need to be copy)
3-Rename in the Current Directory the report "AD_report" as "test.xls"

Warning the Current Directory should be dynamically set up with
CurrentDirectory = objFSO.GetAbsolutePathName(".")
strDestFolder = CurrentDirectory & "\"

Logs requirements:

1-Log file should be named as following log-delete-copy.txt
2-if the file test.xls doesn't exist log output should “"test.xls" doesn't exist””
3-If the folder set up for copy the most recent files doesn't exist log output “ the folder  variable doesn't exist”
4-If any files with the following prefix ""AD_report" doesn't exist the log output should "Any report with the follwing prefix has been found unable to copy it”.


Thank you in advance
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2014

Commented:
Hi, I think this should cover all of your requirements.

Regards,

Rob.

strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
strFileCopy = strCurrentFolder & "Test.xls"
strReportFolder = strCurrentFolder & "Reports\"
strLogFile = strCurrentFolder & "Log-Delete-Copy.txt"
strReportPrefix = "AD_Report"

Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.OpenTextFile(strLogFile, ForAppending, True)
If objFSO.FileExists(strFileCopy) = False Then
	objLogFile.WriteLine Now & ": " & strFileCopy & " does not exist."
Else
	objFSO.DeleteFile strFileCopy, True
	objLogFile.WriteLine Now & ": " & strFileCopy & " has been deleted."
End If
If objFSO.FolderExists(strReportFolder) = False Then
	objLogFile.WriteLine Now & ": " & strReportFolder & " does not exist."
Else
	strLatestReport = ""
	dteLatestReport = CDate("01-01-01901")
	For Each objFile In objFSO.GetFolder(strReportFolder).Files
		If Left(LCase(objFile.Name), LCase(strReportPrefix)) And CDate(objFile.DateLastModified) > dteLatestReport Then
			strLatestReport = objFile.Path
			dteLatestReport = objFile.DateLastModified
		End If
	Next
	If strLatestReport = "" Then
		objLogFile.WriteLine Now & ": " & "No file was found in " & strReportFolder & " starting with " & strReportPrefix
	Else
		objLogFile.WriteLine Now & ": " & "Copying " & objFile.Path & " to " & strFileCopy
		objFSO.CopyFile objFile.Path, strFileCopy, True
	End If
End If

Open in new window

Luis DiazIT consultant

Author

Commented:
Hello,
Thank you for this proposal, I will test it tomorrow.
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
I see Rob has joined in, but since I had this 90% earlier in the day and didn't get to finish it until just now I'll post it as well.  I did some testing and I think it meets all your needs.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject") 

' Define folders to use
strDestDir = objFSO.GetAbsolutePathName(".") & "\"
strBaseDir = "B:\ee\EE28627485\base\"
strCopyPrefix = "AD_report"
strDestFile = "test.xls"
strLogFile = "log-delete-copy.txt"

' Open log file
Set objLogFile = objFSO.OpenTextFile(strLogFile, ForWriting, True)

' Make sure folder exists
If Not objFSO.FolderExists(strBaseDir) Then
   objLogFile.WriteLine "ERROR - base folder """ & strBaseDir & """ does not exist."
   Wscript.Quit
End If

' Delete target file if it exists
If objFSO.FileExists(strDestDir & strDestFile) Then
   objFSO.DeleteFile(strDestDir & strDestFile)
Else
   objLogFile.WriteLine "WARNING - file """ & strDestFile & """ does not exist."
End If

' Look for the newest desired file, copy if it exists (renaming to new name)
strCopyFile = FindNewestFile(strBaseDir, strCopyPrefix)
If strCopyFile <> "" Then
   objFSO.CopyFile strBaseDir & strCopyFile, strDestDir & strDestFile
Else
   objLogFile.WriteLine "ERROR - no files matching  """ & strCopyPrefix & """ found in base folder """ & strBaseDir & """."
End If

' Wrap up
objLogFile.Close


' Function to find the latest matching file in a folder
Function FindNewestFile(strDir, strFilter)
   ' Access the folder
   Set objDir = objFSO.GetFolder(strDir)
   Set objNewest = Nothing
   FindNewestFile = ""

    ' Look at all files in this folder
    For Each objFile In objDir.Files
        ' Make sure it matches the files we are looking for
        If LCase(Left(objFile.Name, Len(strFilter))) = LCase(strFilter) Then
            ' Assume very first file is newest until we find a newer one
            If objNewest Is Nothing Then
                Set objNewest = objFile
                FindNewestFile = objNewest.Name
            ' Otherwise see if this file is the newest of all see so far
            Else
               If (objFile.DateLastModified > objNewest.DateLastModified) Then
                  Set objNewest = objFile
                  FindNewestFile = objNewest.Name
               End If
            End If
        End If
    Next

   ' Clean up objects
   Set objNewest = Nothing
   Set objDir = Nothing
End Function

Open in new window

~bp
Luis DiazIT consultant

Author

Commented:
@Rob I got an error message in line 22 char 3 "Incompatible type" [string]
@Bill: your code works, however it will ge great if the log file display a Success message when all is ok.
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Okay, added a "SUCCESS" log entry.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject") 

' Define folders to use
strDestDir = objFSO.GetAbsolutePathName(".") & "\"
strBaseDir = "B:\ee\EE28627485\base\"
strCopyPrefix = "AD_report"
strDestFile = "test.xls"
strLogFile = "log-delete-copy.txt"

' Open log file
Set objLogFile = objFSO.OpenTextFile(strLogFile, ForWriting, True)

' Make sure folder exists
If Not objFSO.FolderExists(strBaseDir) Then
   objLogFile.WriteLine "ERROR - base folder """ & strBaseDir & """ does not exist."
   Wscript.Quit
End If

' Delete target file if it exists
If objFSO.FileExists(strDestDir & strDestFile) Then
   objFSO.DeleteFile(strDestDir & strDestFile)
Else
   objLogFile.WriteLine "WARNING - file """ & strDestFile & """ does not exist."
End If

' Look for the newest desired file, copy if it exists (renaming to new name)
strCopyFile = FindNewestFile(strBaseDir, strCopyPrefix)
If strCopyFile <> "" Then
   objFSO.CopyFile strBaseDir & strCopyFile, strDestDir & strDestFile
   objLogFile.WriteLine "SUCCESS - file """ & strBaseDir & strCopyFile & """ copied to """ & strDestDir & strDestFile & """."
Else
   objLogFile.WriteLine "ERROR - no files matching  """ & strCopyPrefix & """ found in base folder """ & strBaseDir & """."
End If

' Wrap up
objLogFile.Close


' Function to find the latest matching file in a folder
Function FindNewestFile(strDir, strFilter)
   ' Access the folder
   Set objDir = objFSO.GetFolder(strDir)
   Set objNewest = Nothing
   FindNewestFile = ""

    ' Look at all files in this folder
    For Each objFile In objDir.Files
        ' Make sure it matches the files we are looking for
        If LCase(Left(objFile.Name, Len(strFilter))) = LCase(strFilter) Then
            ' Assume very first file is newest until we find a newer one
            If objNewest Is Nothing Then
                Set objNewest = objFile
                FindNewestFile = objNewest.Name
            ' Otherwise see if this file is the newest of all see so far
            Else
               If (objFile.DateLastModified > objNewest.DateLastModified) Then
                  Set objNewest = objFile
                  FindNewestFile = objNewest.Name
               End If
            End If
        End If
    Next

   ' Clean up objects
   Set objNewest = Nothing
   Set objDir = Nothing
End Function

Open in new window

~bp
Luis DiazIT consultant

Author

Commented:
It works perfectly! Thank you again Bill for your help!
Luis DiazIT consultant

Author

Commented:
Excellent!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Oh, that's what I get for not having enough time to test!  Here is my corrected version, although you won't need it anymore! LOL!

Rob.

strCurrentFolder = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
strFileCopy = strCurrentFolder & "Test.xls"
strReportFolder = strCurrentFolder & "Reports\"
strLogFile = strCurrentFolder & "Log-Delete-Copy.txt"
strReportPrefix = "AD_Report"

Const ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.OpenTextFile(strLogFile, ForAppending, True)
If objFSO.FileExists(strFileCopy) = False Then
	objLogFile.WriteLine strFileCopy & " does not exist."
Else
	objFSO.DeleteFile strFileCopy, True
	objLogFile.WriteLine strFileCopy & " has been deleted."
End If
If objFSO.FolderExists(strReportFolder) = False Then
	objLogFile.WriteLine strReportFolder & " does not exist."
Else
	strLatestReport = ""
	dteLatestReport = CDate("01-01-01901")
	For Each objFile In objFSO.GetFolder(strReportFolder).Files
		If Left(LCase(objFile.Name), Len(strReportPrefix)) = LCase(strReportPrefix) And CDate(objFile.DateLastModified) > dteLatestReport Then
			strLatestReport = objFile.Path
			dteLatestReport = objFile.DateLastModified
		End If
	Next
	If strLatestReport = "" Then
		objLogFile.WriteLine "No file was found in " & strReportFolder & " starting with " & strReportPrefix
	Else
		objLogFile.WriteLine "Copying " & strLatestReport & " to " & strFileCopy
		objFSO.CopyFile strLatestReport, strFileCopy, True
	End If
End If

Open in new window

Luis DiazIT consultant

Author

Commented:
Hello Rob,

Thank you for this revised version. I will test it tomorrow and if it works I will ask Bill to reopen the question to assign assisted solution for your last code.
Most Valuable Expert 2012
Top Expert 2014

Commented:
Thanks, but it's no big deal.  I can re-open the question as well, but I don't think we need to.  I'm not fussed about whether my solutions are accepted these days....I just like writing code, and getting things right ;-)
Luis DiazIT consultant

Author

Commented:
Ok, thank you for this, in any case I will test it tomorrow as I don't have Windows at home, thank you again for your help!