• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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
0
LD16
Asked:
LD16
  • 6
  • 3
  • 2
1 Solution
 
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 PrewCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
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 PrewCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now