Parsing a filename without knowing the entire filename

Posted on 2013-09-16
Medium Priority
Last Modified: 2013-09-19
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_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


' 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


End Sub
Question by:Akish1960
  • 3
LVL 12

Accepted Solution

piattnd earned 2000 total points
ID: 39496727
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

Open in new window

LVL 12

Expert Comment

ID: 39496748
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:


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

Open in new window


Author Closing Comment

ID: 39507476
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.
LVL 12

Expert Comment

ID: 39507551
Glad that did it for you.  Good luck!

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question