asked on

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

RobSampson

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 Diaz

ASKER

Hello,
Thank you for this proposal, I will test it tomorrow.

Bill Prew

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 Diaz

ASKER

@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.

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

It works perfectly! Thank you again Bill for your help!

Luis Diaz

ASKER

Excellent!

RobSampson

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 Diaz

ASKER

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.

RobSampson

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 Diaz

ASKER

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!