I have a dilemma when reading from a very large look-up table (1000+ lines) that contains challenge phrases to determine if a file name (obtained from another delimited list) contains 3 of my 6 phrases. My If statement works only if it match's the challenge but I also need it to work if it only meets 1 specific phrase also from the look-up table. The look-up table example shows possible destination for only 1 user where I will have 100+ users to check against. I can get the correct result if it matches all of my challenges but if it does not it cycles through the rest of the lines containing the same user. Any suggestions to create a break would be much appreciated.
Below is the code I'm working with right now.
'Flags for files
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Dim strFDate: strFDate = DatePart("yyyy", Now) & Right("0" & DatePart("m",Date), 2) & Right("0" & DatePart("d",Date), 2) 'Formats date to YYYYMMDD
Dim strDate: strDate = Date 'Date with DD/MM/YYYY format
Set fso = CreateObject("Scripting.FileSystemObject")
Set fso2 = WScript.CreateObject("Scripting.Filesystemobject")
'Check for %Client%.csv then record file data
Dim strTriggerName: strTriggerName = InputBox("Enter the User name for Row Count, ie. test", "Enter File Path and File Name")
Dim FileDest: FileDest = "C:\Test" 'Destination for moves and extracts must be in "C:\Test" format not ending with "\"
'Check if output.csv exist if not create it
Set objFSO1 = CreateObject("Scripting.FileSystemObject")
If objFSO1.FileExists(srtMovePath & "\output.csv") Then
'Msgbox srtMovePath & "\output.csv Exists"
Else
Set objTXTFILE = objFSO1.CreateTextFile(srtMovePath & "\output.csv")
'Msgbox srtMovePath & "\output.csv Created"
objTXTFILE.close
End If
Set f = fso2.OpenTextFile("c:\output.csv", 2)
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objTextTrigger = objFSO2.OpenTextFile("d:\File_Receipt_Triggers\" & strTriggerName & ".trigger", 1)
If (fso.fileexists("d:\File_Receipt_Triggers\" & strTriggerName & ".trigger")) Then
'Read csv for Client Name, File Name and File Path
Set objFSO2 = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO2.OpenTextFile("d:\File_Receipt_Triggers\" & strTriggerName & ".csv", 1)
Do until objTextFile.AtEndOfStream
Dim arrStr: arrStr = Split(objTextFile.ReadLine, "|")
Dim strClient: strClient = arrStr(0)
Dim strFileName: strFileName = arrStr(1)
Dim strFullPath: strFullPath = arrStr(2)
'Compare file name to lookup table If strFileName contains (User & (C1 or C2) & (C3 or C4) & (C5 or C6)) then Destination
Set objFSO8 = CreateObject("Scripting.FileSystemObject")
Set objMoveTable = objFSO8.OpenTextFile("d:\Move_Lookup_Table.csv", 1)
Do until objMoveTable.AtEndOfStream
Dim arrStr2: arrStr2 = Split(objMoveTable.ReadLine, ",")
Dim User: User = arrStr2(0)
Dim C1: C1 = arrStr2(1)
Dim C2: C2 = arrStr2(2)
Dim C3: C3 = arrStr2(3)
Dim C4: C4 = arrStr2(4)
Dim C5: C5 = arrStr2(5)
Dim C6: C6 = arrStr2(6)
Dim Client: Client = arrStr2(7)
Dim Bank: Bank = arrStr2(8)
Dim OP: OP = arrStr2(9)
Dim Month: Month = arrStr2(10)
Dim Year: Year = arrStr2(11)
Dim strDestination: strDestination = arrStr2(12)
'ISSUE AREA
If ((strTriggerName) = User ) and (Instr( Ucase(strFileName), C1 ) <> 0 or Instr( Ucase(strFileName), C2 ) <> 0) and (Instr( Ucase(strFileName), C3 ) <> 0 or Instr( Ucase(strFileName), C4 ) <> 0) and (Instr( Ucase(strFileName), C5 ) <> 0 or Instr( Ucase(strFileName), C6 ) <> 0) Then
'msgbox strFileName & " is Good"
'<Add code here>
msgbox strFileName & " Should = " & " User: " & User & " and " & C1 & " or " & C2 & " and " & C3 & " or " & C4 & " and " & C5 & " or " & C6 & " Client: " & Client & " Bank: " & Bank & " OP: " & OP & " Month: " & Month & " Year: " & Year & " Dest: " & "\\nas2\BAIArchive\BAI" & strDestination & Bank
ElseIf ((strTriggerName) = User ) and Not
'msgbox strFileName & " does not match"
'<Add code to here>
End If
Loop
Loop
End If
An Example of my look-up table is here:
User,Contains1,Contains2,Contains3,Contains4,Contains6,Contains5,strClient,StrBank,strOP,strMonth,strYear,strDestination test,DPB,DB,5,May,2013,13,BAI,TEST,DPB,May,2013,\2013May\DPB\ test,DPB,DB,11,Nov,2013,13,BAI,TEST,DPB,Nov,2013,\2013Nov\DPB\ test,DPB,DB,5,May,2014,14,BAI,TEST,DPB,May,2014,\2014May\DPB\ test,DPB,DB,11,Nov,2014,14,BAI,TEST,DPB,Nov,2014,\2014Nov\DPB\ test,DPB,DB,5,May,2015,15,BAI,TEST,DPB,May,2015,\2015May\DPB\ test,DPB,DB,11,Nov,2015,15,BAI,TEST,DPB,Nov,2015,\2015Nov\DPB\ test,NII,CTS,5,May,2013,13,BAI,TEST,NII,May,2013,\2013May\NII\ test,NII,CTS,11,Nov,2013,13,BAI,TEST,NII,Nov,2013,\2013Nov\NII_CTS\ test,NII,CTS,5,May,2014,14,BAI,TEST,NII,May,2014,\2014May\NIICTS\ test,NII,CTS,11,Nov,2014,14,BAI,TEST,NII,Nov,2014,\2014Nov\NIICTS\ test,NII,CTS,5,May,2015,15,BAI,TEST,NII,May,2015,\2015May\NIICTS\ test,NII,CTS,11,Nov,2015,15,BAI,TEST,NII,Nov,2015,\2015Nov\NIICTS\ test,SBB,SB,5,May,2013,13,BAI,TEST,SBB,May,2013,\2013May\SBB\ test,SBB,SB,11,Nov,2013,13,BAI,TEST,SBB,Nov,2013,\2013Nov\SBB\ test,SBB,SB,5,May,2014,14,BAI,TEST,SBB,May,2014,\2014May\SBB\ test,SBB,SB,11,Nov,2014,14,BAI,TEST,SBB,Nov,2014,\2014Nov\SBB\ test,SBB,SB,5,May,2015,15,BAI,TEST,SBB,May,2015,\2015May\SBB\ test,SBB,SB,11,Nov,2015,15,BAI,TEST,SBB,Nov,2015,\2015Nov\SBB\
Aucun commentaire:
Enregistrer un commentaire