Parsing a filename without knowing the entire filename

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.
Filenames:
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_20130916_<timestamp>.xls . 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.network")
 Set oShell = CreateObject("wscript.shell")
 Set oFso = CreateObject("scripting.filesystemobject")

'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.
 
 'Source Directory

 sDir = "\\COSCSFS01P\itg$\QA\Lorne\TEMP_TEST_FileTransfer\SOURCE\"
 
 'Destination Directory

 dDir =  "\\COSCSFS01P\itg$\QA\Lorne\TEMP_TEST_FileTransfer\eFiling_Pending_Docket_Reports\"
 
 'Source Files
 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

FileCopy()
SendMail()
WScript.Quit

' Begin Subroutines

Sub FileCopy()

 '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

End Sub
 
Sub SendMail()

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.zzz>"
'oMsg.BCC = "yyyyyyl@xxxxxxxxxxxx.zzz"
oMsg.To = "yyyy@xxxxxxxxxxx.zzz"
oMsg.TextBody = strMsg & strNotice

oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mailbox.xxxxxxxxxx.zzz"
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusername") = "PSP-DCMSdata@xxxxxxxxx.zzz"
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendpassword") = ""'"yourpassword"
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
oMsg.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
oMsg.Configuration.Fields.Update

oMsg.Send

End Sub
Akish1960Asked:
Who is Participating?
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.

piattndCommented:
You need to attach to the folder and load all the files so you can go through them:

Set objServiceConfigFolder = fs.GetFolder("somefolderpath\somefolder")
	Set colFiles = objServiceConfigFolder.Files

Open in new window


Once you have the files, go through them one at a time and look at the name

For Each file In colFiles
          strFileName = file.Name
          if instr(strFileName,"SomeTextYouWant") then
                'this is a file you're looking for
          else if instr(strFileName,"Anothersetoftextyouwant) then
                'this is another file you're looking for
          end if
next

Open in new window

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
piattndCommented:
So basically, to use the code above, you need to formulate as much of the file name as you know will be present, or expect to be present.

For example, you say you're looking for:

PreDocketbyUser_COC003_{4digityear}{2digitmonth}{2digitday}

Then you just establish that as a variable at the beginning of your script just as you have and use that in your comparison for "if instr" of the file name.

You can also check for the extension if you'd prefer to narrow down your search even more.  If you know you want excel files only (xlsx) then you can do the following to only check the name on xlsx files:

For Each file In colFiles
	If LCase(fs.GetExtensionName(file.Name)) = "xlsx" then
		strFileName = file.Name
		if instr(strFileName,"SomeTextYouWant") then
			'this is a file you're looking for
		else if instr(strFileName,"Anothersetoftextyouwant) then
			'this is another file you're looking for
		end if
	End If
Next

Open in new window

0
Akish1960Author Commented:
this is exactly what I needed. I adjusted for my own variable names, and it worked like a charm. Thank you very much. I knew I needed to use a dictionary, but was unsure how to script it. Thank you for showing me how to do this.
0
piattndCommented:
Glad that did it for you.  Good luck!
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
Microsoft Legacy OS

From novice to tech pro — start learning today.