We have a manual process at work of copying two files every Monday out of one folder, and into another. I have been asked if we could automate that procedure using vbscript and email the result to a few individuals.
Both files have the time and date stamp on them, so it is nor possible for me to know what the exact filename would be. I can use the constant part of the filename, and I can even parse through the date. But from there, I run into a timestamp. Both files are excel (xls) files.
Filename 1 is named "PreDocketbyUser_COC003_"<
followed by the year, mm, dd> then an underscore (_) and then the time stamp, followed by the extension>. So Mondays file would look like "PreDocketbyUser_COC003_20
ls . The second file is similar except for the constant name. It is "PendingEFileSummaryByUser
_COC005_" <with the same parameters as the first.
The script needs to parse through the files in the directory, and if those files exist, copy them out to another directory, and report to my email subroutine. I've got most of it down except for the above issue. Could someone please help me complete this task? Listed below is my script;
Dim oNet, oFso, oShell, sDir, dDir, strMsg, strNotice
Set oNet = CreateObject("wscript.netw
Set oShell = CreateObject("wscript.shel
Set oFso = CreateObject("scripting.fi
'Variables for copy process. Change here for global changes.
'NOTE: Script must be run AFTER the files are placed in the Source folder, sometime during the same day.
sDir = "\\COSCSFS01P\itg$\QA\Lorn
dDir = "\\COSCSFS01P\itg$\QA\Lorn
sFile1 = "PreDocketbyUser_COC003_" & Year(Date) & Right("0" & Month(Date),2) & Right("0" & Day(Date),2) & "_*.xls"
sFile2 = "PendingEFileSummaryByUser
_COC005_" & Year(Date) & Right("0" & Month(Date),2) & Right("0" & Day(Date),2) & "_*.xls"
'Execution of Script
' Begin Subroutines
'Copy the Documents
' < dictionary routine perhaps? >
oFso.CopyFile sDir & sFile1, dDir
strMsg = strMsg & sFile1 & "was copied over to " & dDir & vbcrlf
oFso.CopyFile sDir & sFile2, dDir
strMsg = strMsg & sFile2 & "was copied over to " & dDir & vbcrlf
'Clean up variables
Set oNET = nothing
Set oSHELL = nothing
set oFso = nothing
strNotice = vbCrLf & vbCrLf & "This email is automatically generated and is for your information only. This email address is not monitored." &_
" Please do not reply to this email.
Const cdoSendUsingPort = 2 'Send the message using the network (SMTP over the network).
Const cdoBasic = 1 'basic (clear-text) authentication
Set oMsg = CreateObject("CDO.Message"
oMsg.Subject = "COC File Copy - " & sFile1 & " and " & sFile2
oMsg.From = """PSP-DCMS Data [Auto-Email]"" <PSP-DCMSdata@xxxxxxxxx.zz
'oMsg.BCC = "email@example.com"
oMsg.To = "firstname.lastname@example.org"
oMsg.TextBody = strMsg & strNotice
") = cdoSendUsingPort
") = "mailbox.xxxxxxxxxx.zzz"
") = cdoBasic
") = "PSP-DCMSdata@xxxxxxxxx.zz
") = ""'"yourpassword"
") = 25
") = False
") = 60