asked on

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

ASKER CERTIFIED SOLUTION

piattnd

membership

This solution is only available to members.

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

Start Free Trial

piattnd

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

Akish1960

ASKER

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.

piattnd

Glad that did it for you. Good luck!