Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Cheadle, Staffordshire
    Thanked 0 Times in 0 Posts

    I created some code to handle a text file and export to a location on the server.
    The code searches a string or value and replaces in a text file.
    The text file is then collected by an external website and uses the data.
    Originally the file was imported into an estimating system and then used on the website, this could take 3 days within our administration method, so to save these days I am going direct from the web to collect the data.
    I'm getting an error which ( I think) 0originates from XML language which for this case has no relevance, the error occurs if there is no value or null between the """" quotes in the file.

    To make this work correctly, I would have to check every line of code with an IF statement

    strSearchtext1 = "#ASSESS#"
    If strSearchtext1 = "" then
    strSearchstring1 = "."
    strReplacetext1 = Left(JN & Me.EST_NO & Space(100), Len(strSearchtext1))
    End if

    This would take ages to complete, my question is, is there a quicker way of doing this?

    Code as below:

    Private Sub Command43_Click()
    Dim strInputfile As String
    Dim strOutputfile As String
    Dim strHelpHireFile As String

    Dim JN As String

    JN = InputBox("Please Enter Your ATX Prefix", "")

    If JN = "" Then
    MsgBox "You Must Enter A Valid ATX Prefix" & vbCrLf & _
    "Please Try Again", vbInformation, "Information": Exit Sub
    End If

    If Len(JN) > 3 Then
    MsgBox "Too Many Characters" & vbCrLf & _
    "You Can Only Use 3 Characters" & vbCrLf & _
    "Please Enter A Valid 3 Character Prefix", vbInformation, "Information": Exit Sub
    End If

    If Len(JN) < 3 Then
    MsgBox "Not Enough Characters" & vbCrLf & _
    "You Need To Use 3 Characters" & vbCrLf & _
    "Please Enter A Valid 3 Character Prefix", vbInformation, "Information": Exit Sub
    End If

    strInputfile = "C:\MM-Utilities\ATX Import Project\AudaImport.txt"
    strOutputfile = "C:\MM-Utilities\" & JN & Me.EST_NO & ".txt"
    strHelpHireFile = "L:\ATX-Helphire-Export\" & JN & Me.EST_NO

    Dim strLine As String
    Dim strSearchtext1 As String
    Dim strReplacetext1 As String
    Dim strSearchtext2 As String
    Dim strReplacetext2 As String
    Dim strSearchtext3 As String
    Dim strReplacetext3 As String
    Dim strSearchtext4 As String
    Dim strReplacetext4 As String
    Dim strSearchtext5 As String
    Dim strReplacetext5 As String
    Dim strSearchtext6 As String
    Dim strReplacetext6 As String
    Dim strSearchtext7 As String
    Dim strReplacetext7 As String
    Dim strSearchtext8 As String
    Dim strReplacetext8 As String
    Dim strSearchtext9 As String
    Dim strReplacetext9 As String
    Dim strSearchtext10 As String
    Dim strReplacetext10 As String
    Dim strSearchtext11 As String
    Dim strReplacetext11 As String
    Dim strSearchtext12 As String
    Dim strReplacetext12 As String
    Dim strSearchtext14 As String
    Dim strReplacetext14 As String
    Dim strSearchtext15 As String
    Dim strReplacetext15 As String
    Dim strSearchtext16 As String
    Dim strReplacetext16 As String
    Dim strSearchtext17 As String
    Dim strReplacetext17 As String
    Dim strSearchtext18 As String
    Dim strReplacetext18 As String
    Dim strSearchtext19 As String
    Dim strReplacetext19 As String
    Dim strSearchtext20 As String
    Dim strReplacetext20 As String
    Dim strSearchtext21 As String
    Dim strReplacetext21 As String
    Dim strSearchtext23 As String
    Dim strReplacetext23 As String
    Dim strSearchtext24 As String
    Dim strReplacetext24 As String
    Dim strSearchtext25 As String
    Dim strReplacetext25 As String
    Dim strSearchtext26 As String
    Dim strReplacetext26 As String
    Dim strSearchtext27 As String
    Dim strReplacetext27 As String
    Dim strSearchtext28 As String
    Dim strReplacetext28 As String
    Dim strSearchtext29 As String
    Dim strReplacetext29 As String

    strSearchtext1 = "#ASSESS#"
    strReplacetext1 = Left(JN & Me.EST_NO & Space(100), Len(strSearchtext1))
    strSearchtext2 = "#DATE2222#"
    strReplacetext2 = Left(Date & Space(100), Len(strSearchtext2))
    strSearchtext3 = "#REGIS #"
    strReplacetext3 = Left(Me.REG & Space(100), Len(strSearchtext3))
    strSearchtext4 = "#CLAIMNUMBER2222222#"
    strReplacetext4 = Left(Me.CLM_NO & Space(100), Len(strSearchtext4))
    strSearchtext5 = "#SEARCHNAME22222222#"
    strReplacetext5 = Left(Me.OWN_NME & Space(100), Len(strSearchtext5))
    strSearchtext6 = "#ASSESSOR #"
    strReplacetext6 = " "
    strSearchtext7 = "#OWNER #"
    strReplacetext7 = Left(Me.OWN_NME & Space(100), Len(strSearchtext7))
    strSearchtext8 = "#ADDRESS1 #"
    strReplacetext8 = Left(Me.OWN_ADD_1 & Space(100), Len(strSearchtext8))
    strSearchtext9 = "#ADDRESS2 #"
    strReplacetext9 = Left(Me.OWN_ADD_2 & Space(100), Len(strSearchtext9))
    strSearchtext10 = "#ADDRESS3 #"
    strReplacetext10 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext10))
    strSearchtext11 = "#ADDRESS4 #"
    strReplacetext11 = Left(Me.OWN_ADD_4 & Space(100), Len(strSearchtext11))
    strSearchtext12 = "#PC##P#"
    strReplacetext12 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext12))
    strSearchtext14 = "#TEL1222222222222#"
    strReplacetext14 = Left(Me.OWN_TEL_H & Space(100), Len(strSearchtext14))
    strSearchtext15 = "#TEL2222222222222#"
    strReplacetext15 = Left(Me.OWN_TEL_W & Space(100), Len(strSearchtext15))

    strSearchtext16 = "#OWNER #"
    strReplacetext16 = Left(Me.OWN_NME & Space(100), Len(strSearchtext16))
    strSearchtext17 = "#ADDRESS1 #"
    strReplacetext17 = Left(Me.OWN_ADD_1 & Space(100), Len(strSearchtext17))
    strSearchtext18 = "#ADDRESS2 #"
    strReplacetext18 = Left(Me.OWN_ADD_2 & Space(100), Len(strSearchtext18))
    strSearchtext19 = "#ADDRESS3 #"
    strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19))
    strSearchtext20 = "#ADDRESS4 #"
    strReplacetext20 = Left(Me.OWN_ADD_4 & Space(100), Len(strSearchtext20))
    strSearchtext21 = "#PC##P#"
    strReplacetext21 = Left(Me.OWN_PCD & Space(100), Len(strSearchtext21))
    strSearchtext23 = "#TEL1222222222222#"
    strReplacetext23 = Left(Me.OWN_TEL_H & Space(100), Len(strSearchtext23))
    strSearchtext24 = "#TEL2222222222222#"
    strReplacetext24 = Left(Me.OWN_TEL_W & Space(100), Len(strSearchtext24))
    strSearchtext25 = "#MILE#"
    strReplacetext25 = Left(Me.VEH_MIL & Space(100), Len(strSearchtext25))
    strSearchtext26 = "#CHASSIS222222222222#"
    strReplacetext26 = Left(Me.VEH_CHA_NO & Space(100), Len(strSearchtext26))
    strSearchtext27 = "#POLICY2222222222222222222222222222#"
    strReplacetext27 = Left(Me.POL_NO & Space(100), Len(strSearchtext27))
    strSearchtext28 = "#COLOUR2222#"
    strReplacetext28 = Left(Me.VEH_PNT_COL & Space(100), Len(strSearchtext28))
    strSearchtext29 = "#EXCE#"
    strReplacetext29 = Left(Me.EXS & Space(100), Len(strSearchtext29))

    Open strInputfile For Input As #1 ' Open file for intput.
    Open strOutputfile For Output As #2 ' Open file for output.
    Open strHelpHireFile For Output As #3
    Do While Not EOF(1)
    Line Input #1, strLine

    strLine = Replace(strLine, strSearchtext1, strReplacetext1)
    strLine = Replace(strLine, strSearchtext2, strReplacetext2)
    strLine = Replace(strLine, strSearchtext3, strReplacetext3)
    strLine = Replace(strLine, strSearchtext4, strReplacetext4)
    strLine = Replace(strLine, strSearchtext5, strReplacetext5)
    strLine = Replace(strLine, strSearchtext6, strReplacetext6)
    strLine = Replace(strLine, strSearchtext7, strReplacetext7)
    strLine = Replace(strLine, strSearchtext8, strReplacetext8)
    strLine = Replace(strLine, strSearchtext9, strReplacetext9)
    strLine = Replace(strLine, strSearchtext10, strReplacetext10)
    strLine = Replace(strLine, strSearchtext11, strReplacetext11)
    strLine = Replace(strLine, strSearchtext12, strReplacetext12)
    strLine = Replace(strLine, strSearchtext14, strReplacetext14)
    strLine = Replace(strLine, strSearchtext15, strReplacetext15)
    strLine = Replace(strLine, strSearchtext16, strReplacetext16)
    strLine = Replace(strLine, strSearchtext17, strReplacetext17)
    strLine = Replace(strLine, strSearchtext18, strReplacetext18)
    strLine = Replace(strLine, strSearchtext19, strReplacetext19)
    strLine = Replace(strLine, strSearchtext20, strReplacetext20)
    strLine = Replace(strLine, strSearchtext21, strReplacetext21)
    strLine = Replace(strLine, strSearchtext23, strReplacetext23)
    strLine = Replace(strLine, strSearchtext24, strReplacetext24)
    strLine = Replace(strLine, strSearchtext25, strReplacetext25)
    strLine = Replace(strLine, strSearchtext26, strReplacetext26)
    strLine = Replace(strLine, strSearchtext27, strReplacetext27)
    strLine = Replace(strLine, strSearchtext28, strReplacetext28)
    strLine = Replace(strLine, strSearchtext29, strReplacetext29)

    Print #2, strLine
    Print #3, strLine

    Close #1
    Close #2
    Close #3
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    Hi Dave,

    I don't understand. You specify the search strings strSearchText1 etc. explicitly, and none of them is empty. So why would you have to check whether they're empty? You know in advance that they aren't...

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Cheadle, Staffordshire
    Thanked 0 Times in 0 Posts

    Sorry I didn't explain.
    The search text looks for place holders in the template file which is : C:\MM-Utilities\ATX Import Project\AudaImport.txt
    This file is used every time and contains everything.
    Its the replace text which can contain no record.

    OOOpps sorry !!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    But the replace text will never be empty either - it will be the same length as the search text...

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Cheadle, Staffordshire
    Thanked 0 Times in 0 Posts

    Sorry had to take a couple of days away from this.

    Take for instance:

    strSearchtext19 = "#ADDRESS3 #"
    strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19))

    strSearchtext looks for the string "#ADDRESS3 #" which is 11 characters and try's to replace it, if there is nothing to replace it with then it will use "" because there is nothing in the table or field. So I would have to use something like:

    strSearchtext19 = "#ADDRESS3 #" '(This will always be in the template - fixed length)
    If strReplacetext19 = "" then
    strReplacetext19 = " "
    strReplacetext19 = Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19)) ' (This could be empty if there is no data)
    end if

    Not sure if this makes sense but its the best way I can explain it.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    I'm sorry, it's still not clear to me.

    Left(Me.OWN_ADD_3 & Space(100), Len(strSearchtext19)) will never be an empty string, it's a string with the same length as strSearchText19, i.e. 11 characters. These could be 11 spaces, if Me.OWN_ADD_3 is null.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts