• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

VBScript to search for phrase in a row

Hi,

I have this code that contains an If Then statement on Line 73.  I just need to search through the "legal_sec" field and if anywhere within that 255 character wide field, if the word "personal" (not case sensitive) is found, to be included within that If Then statement.

"Personal" could be typed in any case combination "PERSONAL" or "Personal" or anything between so should not be case sensitive.


Thank you,

J
' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const xlSaveChanges = 1 'Excel Save Changes
Const xlAscending = 1 'Excel Sort Ascending
Const xlDescending = 2 'Excel Sort Decending
Const xlYes = 1 'Excel Header Row Exists

cTab = Chr(9)

' Get input and output file names from command line parms
If (WScript.Arguments.Count > 0) Then
    sInfile = WScript.Arguments(0)
Else
    WScript.Echo "No input filename specified."
    WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
    sOutfile = WScript.Arguments(1)
Else
    WScript.Echo "No output filename specified."
    WScript.Quit
End If

'Create Excel Object
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False 'Disable Overwrite Prompts
objExcel.Visible = True 'Hides Excel window

'Create Excel worksheet and add header row
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "Property ID"
objExcel.Cells(1, 2) = "Owner ID"
objExcel.Cells(1, 3) = "Base MNO Due"
objExcel.Cells(1, 4) = "Base INS Due"
objExcel.Cells(1, 5) = "Owner Name"
objExcel.Cells(1, 6) = "Address1"
objExcel.Cells(1, 7) = "Address2"
objExcel.Cells(1, 8) = "Address3"
objExcel.Cells(1, 9) = "City"
objExcel.Cells(1, 10) = "State"
objExcel.Cells(1, 11) = "ZipCode"
objExcel.Cells(1, 12) = "Legal Description"
intRow = 2 'Set first row to add data

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Read entire input file into a variable and close it
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)

Set objDic = CreateObject("Scripting.Dictionary")
    
intcount = 0
Do While Not oInfile.AtEndOfStream
    sLine = oInfile.ReadLine

    prop_id = Trim(Mid(sLine, 1, 12))
    owner_id = Trim(Mid(sLine, 68, 12))
    owner_name = Trim(Mid(sLine, 80, 70))
    addr_line1 = Trim(Mid(sLine, 150, 60))
    addr_line2 = Trim(Mid(sLine, 210, 60))
    addr_line3 = Trim(Mid(sLine, 270, 60))
    addr_city = Trim(Mid(sLine, 330, 50))
    addr_state = Trim(Mid(sLine, 380, 50))
    addr_zip = Trim(Mid(sLine, 430, 10))
    legal_desc = Trim(Mid(sLine, 486, 255))
    base_mno_due = Trim(Mid(sLine, 893, 16))
    base_ins_due = Trim(Mid(sLine, 909, 16))
  
           
    If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And legal_desc Then
        If Not objDic.Exists(CStr(prop_id)) Then

            objDic.Add CStr(prop_id), prop_id

            objExcel.Cells(intRow, 1) = prop_id
            objExcel.Cells(intRow, 2) = owner_id
            objExcel.Cells(intRow, 3) = base_mno_due
            objExcel.Cells(intRow, 4) = base_ins_due
            objExcel.Cells(intRow, 5) = owner_name
            objExcel.Cells(intRow, 6) = addr_line1
            objExcel.Cells(intRow, 7) = addr_line2
            objExcel.Cells(intRow, 8) = addr_line3
            objExcel.Cells(intRow, 9) = addr_city
            objExcel.Cells(intRow, 10) = addr_state
            objExcel.Cells(intRow, 11) = addr_zip
            objExcel.Cells(intRow, 12) = legal_desc
            

            intRow = intRow + 1
        End If
    End If
Loop

Set objDic = Nothing

'Format and Sort Excel workbook

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
objSheet.Columns("A:A").NumberFormat = "0"
Set objRange = objSheet.UsedRange
objRange.EntireColumn.AutoFit()
For iW = objRange.Column To objRange.Columns.Count
    colWidth = objSheet.Columns(iW).ColumnWidth
    objSheet.Columns(iW).ColumnWidth = colWidth + 5
Next
        
'Save Excel Workbook
objExcel.ActiveWorkbook.SaveAs(sOutfile)
'objExcel.ActiveWorkbook.Close xlSaveChanges
'objExcel.Quit
WScript.Echo "Script Finished"

' Cleanup and end
oInfile.Close
Set oInfile = Nothing
Set oFSO = Nothing

Open in new window

0
jon1966
Asked:
jon1966
  • 3
  • 2
  • 2
  • +1
1 Solution
 
sungenwangCommented:
Change line 73 to the following:

If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And InStr(UCase(legal_desc), "PERSONAL") > 0 Then

sew
0
 
jon1966Author Commented:
Man, I am sorry.  If the word personal *is* found, then that row should be discard.  My bad.
0
 
HainKurtSr. System AnalystCommented:
try this:

isPersonal = InStr(legal_desc, "personal",1) > 0
If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And isPersonal Then
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
HainKurtSr. System AnalystCommented:
then

isPersonal = InStr(legal_desc, "personal",1) > 0
If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And Not isPersonal Then
0
 
x-menIT super heroCommented:
you can use something like

If (InStr(1,legal_desc,"personal",1) <> 0 ) Then
 wscrip.echo "Found personal in legal_desc variable"
End If
0
 
jon1966Author Commented:
HainKurt, received an error on that last code.

9May-1.vbs(71, 5) Microsoft VBScript runtime error: Type mismatch: 'legal_desc'



' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
Const xlSaveChanges = 1 'Excel Save Changes
Const xlAscending = 1 'Excel Sort Ascending
Const xlDescending = 2 'Excel Sort Decending
Const xlYes = 1 'Excel Header Row Exists

cTab = Chr(9)

' Get input and output file names from command line parms
If (WScript.Arguments.Count > 0) Then
    sInfile = WScript.Arguments(0)
Else
    WScript.Echo "No input filename specified."
    WScript.Quit
End If
If (WScript.Arguments.Count > 1) Then
    sOutfile = WScript.Arguments(1)
Else
    WScript.Echo "No output filename specified."
    WScript.Quit
End If

'Create Excel Object
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False 'Disable Overwrite Prompts
objExcel.Visible = True 'Hides Excel window

'Create Excel worksheet and add header row
objExcel.Workbooks.Add()
objExcel.Cells(1, 1) = "Property ID"
objExcel.Cells(1, 2) = "Owner ID"
objExcel.Cells(1, 3) = "Base MNO Due"
objExcel.Cells(1, 4) = "Base INS Due"
objExcel.Cells(1, 5) = "Owner Name"
objExcel.Cells(1, 6) = "Address1"
objExcel.Cells(1, 7) = "Address2"
objExcel.Cells(1, 8) = "Address3"
objExcel.Cells(1, 9) = "City"
objExcel.Cells(1, 10) = "State"
objExcel.Cells(1, 11) = "ZipCode"
objExcel.Cells(1, 12) = "Legal Description"
intRow = 2 'Set first row to add data

' Create file system object
Set oFSO = CreateObject("Scripting.FileSystemObject")

' Read entire input file into a variable and close it
Set oInfile = oFSO.OpenTextFile(sInfile, ForReading, False, TriStateUseDefault)

Set objDic = CreateObject("Scripting.Dictionary")
    
intcount = 0
Do While Not oInfile.AtEndOfStream
    sLine = oInfile.ReadLine

    prop_id = Trim(Mid(sLine, 1, 12))
    owner_id = Trim(Mid(sLine, 68, 12))
    owner_name = Trim(Mid(sLine, 80, 70))
    addr_line1 = Trim(Mid(sLine, 150, 60))
    addr_line2 = Trim(Mid(sLine, 210, 60))
    addr_line3 = Trim(Mid(sLine, 270, 60))
    addr_city = Trim(Mid(sLine, 330, 50))
    addr_state = Trim(Mid(sLine, 380, 50))
    addr_zip = Trim(Mid(sLine, 430, 10))
    legal_desc = Trim(Mid(sLine, 486, 255))
    base_mno_due = Trim(Mid(sLine, 893, 16))
    base_ins_due = Trim(Mid(sLine, 909, 16))       
    isPersonal = InStr(legal_desc, "personal",1) > 0
    
	If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And Not isPersonal Then
        If Not objDic.Exists(CStr(prop_id)) Then

            objDic.Add CStr(prop_id), prop_id

            objExcel.Cells(intRow, 1) = prop_id
            objExcel.Cells(intRow, 2) = owner_id
            objExcel.Cells(intRow, 3) = base_mno_due
            objExcel.Cells(intRow, 4) = base_ins_due
            objExcel.Cells(intRow, 5) = owner_name
            objExcel.Cells(intRow, 6) = addr_line1
            objExcel.Cells(intRow, 7) = addr_line2
            objExcel.Cells(intRow, 8) = addr_line3
            objExcel.Cells(intRow, 9) = addr_city
            objExcel.Cells(intRow, 10) = addr_state
            objExcel.Cells(intRow, 11) = addr_zip
            objExcel.Cells(intRow, 12) = legal_desc
            

            intRow = intRow + 1
        End If
    End If
Loop

Set objDic = Nothing

'Format and Sort Excel workbook

Set objSheet = objExcel.ActiveWorkbook.ActiveSheet
objSheet.Columns("A:A").NumberFormat = "0"
Set objRange = objSheet.UsedRange
objRange.EntireColumn.AutoFit()
For iW = objRange.Column To objRange.Columns.Count
    colWidth = objSheet.Columns(iW).ColumnWidth
    objSheet.Columns(iW).ColumnWidth = colWidth + 5
Next
        
'Save Excel Workbook
objExcel.ActiveWorkbook.SaveAs(sOutfile)
'objExcel.ActiveWorkbook.Close xlSaveChanges
'objExcel.Quit
WScript.Echo "Script Finished"

' Cleanup and end
oInfile.Close
Set oInfile = Nothing
Set oFSO = Nothing

Open in new window

0
 
sungenwangCommented:
Change line 73 to the following:

If (( base_mno_due <> "" And CLng(base_mno_due) >= 100 )) And InStr(UCase(legal_desc), "PERSONAL") <= 0 Then

You need to use UCase() to do case-insensitive search!

sew
0
 
jon1966Author Commented:
Thanks for all the help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now