VB Script: Delete, copy, rename files

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
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RobSampsonCommented:
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

0
LD16Author Commented:
Hello,
Thank you for this proposal, I will test it tomorrow.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

LD16Author 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.
0
Bill PrewIT / Software Engineering ConsultantCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
It works perfectly! Thank you again Bill for your help!
0
LD16Author Commented:
Excellent!
0
RobSampsonCommented:
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

0
LD16Author 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.
0
RobSampsonCommented:
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 ;-)
0
LD16Author 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!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.