Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional import from text file (2000 sr 1)

    Subject edited by HansV

    i have this macro it import, in a normal condition, a txt file and put the all data in a sheet.
    My problem is to import only the data from a txt file if the variable VAR_COD is equal to 442 skip the remain VAR_COD....

    i attache a piece of txt file and the vba

    http://www.gssitaly.com/l0785test.zip

    Note: for the first time test first in normal mode

    code:

    Sub CercaTrova()

    Dim RIGA As String
    Dim cont As Double

    Range("A3:S60000").ClearContents

    cont = 3

    Dim Vfile
    Vfile = Application.GetOpenFilename

    Open Vfile For Input As #1

    While Not EOF(1)

    Line Input #1, RIGA

    If Len(Trim(RIGA)) > 0 Then

    If InStr(Mid(RIGA, 1, 12), "DATA CONTAB.") > 0 Then
    var_DATACONT = Mid(RIGA, 15, 10)
    End If

    If InStr(Mid(RIGA, 1, 11), "DIPENDENZA:") > 0 Then
    var_DIP = Mid(RIGA, 14, 4)
    End If

    If InStr(Mid(RIGA, 5, 1), "-") > 0 And InStr(Mid(RIGA, 13, 1), "-") > 0 Then
    var_COD = Mid(RIGA, 1, 3)
    End If

    If InStr(Mid(RIGA, 71, 1), ",") > 0 And InStr(Mid(RIGA, 98, 1), "/") > 0 Then
    var_NUMCC = Mid(RIGA, 1, 6)
    var_NOME = Trim(Mid(RIGA, 8, 40))
    var_CAUS = Mid(RIGA, 50, 2)
    var_DARE = Trim(Mid(RIGA, 60, 14))
    var_AVERE = Trim(Mid(RIGA, 80, 14))
    var_VAL = Mid(RIGA, 96, 10)
    var_MITT = Mid(RIGA, 107, 4)
    var_ANOMAL = Trim(Mid(RIGA, 116, 10))

    If Not EOF(1) Then

    Line Input #1, RIGA
    var_DESCR = Mid(RIGA, 10, 40)
    var_DESCR2 = Mid(RIGA, 96, 30)
    Line Input #1, RIGA
    var_DESCR3 = Mid(RIGA, 10, 50)

    End If

    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DATACONT
    Foglio1.Range("B" & Trim(Str(cont))).Value = var_DIP
    Foglio1.Range("C" & Trim(Str(cont))).Value = var_COD
    Foglio1.Range("D" & Trim(Str(cont))).Value = var_NUMCC
    Foglio1.Range("E" & Trim(Str(cont))).Value = var_NOME
    Foglio1.Range("F" & Trim(Str(cont))).Value = var_CAUS
    Foglio1.Range("G" & Trim(Str(cont))).Value = var_DARE
    Foglio1.Range("H" & Trim(Str(cont))).Value = var_AVERE
    Foglio1.Range("I" & Trim(Str(cont))).Value = var_VAL
    Foglio1.Range("J" & Trim(Str(cont))).Value = var_MITT
    Foglio1.Range("K" & Trim(Str(cont))).Value = var_ANOMAL
    Foglio1.Range("L" & Trim(Str(cont))).Value = var_DESCR
    Foglio1.Range("M" & Trim(Str(cont))).Value = var_DESCR2
    Foglio1.Range("N" & Trim(Str(cont))).Value = var_DESCR3

    cont = cont + 1

    End If
    End If
    Wend
    Close #1
    'Call COD442
    MsgBox ("IMPORT TERMINATO!")
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional import from text file (2000 sr 1)

    You didn't attach a file yet, but try this:

    ...

    ' Only import into cells if VAR_COD is 442
    If VAR_COD = 442 Then
    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DATACONT
    ...
    Foglio1.Range("N" & Trim(Str(cont))).Value = var_DESCR3
    End If
    ...

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    My dear, i am sorry i have insert in the top of my message a link from my private site:

    http://www.gssitaly.com/l0785test.zip

    Make your test with this...

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional import from text file (2000 sr 1)

    I would suggest adding the lines like this:

    If InStr(Mid(RIGA, 5, 1), "-") > 0 And InStr(Mid(RIGA, 13, 1), "-") > 0 Then
    var_COD = Mid(RIGA, 1, 3)
    End If

    If var_COD = "442" Then 'NEW LINE
    If InStr(Mid(RIGA, 71, 1), ",") > 0 And InStr(Mid(RIGA, 98, 1), "/") > 0 Then
    ....
    End If
    End If
    End If 'NEW LINE

    to eliminate the need for some processing (even though I am not the "Wzard Hans"). <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    ok! i know your knoledgment in VBA and for me you and HansV are for me a WIZARD, you have helped me many time!
    You and HansV have solved a BIG problem. I have posted many question in another Forum for VBA/Excel but the speed to answer and the profesionality i have seen only in this Forum.
    Tks for your speedly help

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    .... ok! i dont have undestand where i put you line..
    If you like re-post my code with your modify.
    Tks.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional import from text file (2000 sr 1)

    Sub CercaTrova()

    Dim RIGA As String
    Dim cont As Double

    Range("A3:S60000").ClearContents

    cont = 3

    Dim Vfile
    Vfile = Application.GetOpenFilename

    Open Vfile For Input As #1

    While Not EOF(1)

    Line Input #1, RIGA

    If Len(Trim(RIGA)) > 0 Then

    If InStr(Mid(RIGA, 1, 12), "DATA CONTAB.") > 0 Then
    var_DATACONT = Mid(RIGA, 15, 10)
    End If

    If InStr(Mid(RIGA, 1, 11), "DIPENDENZA:") > 0 Then
    var_DIP = Mid(RIGA, 14, 4)
    End If

    If InStr(Mid(RIGA, 5, 1), "-") > 0 And InStr(Mid(RIGA, 13, 1), "-") > 0 Then
    var_COD = Mid(RIGA, 1, 3)
    End If

    If var_COD = "442" Then 'NEW LINE
    If InStr(Mid(RIGA, 71, 1), ",") > 0 And InStr(Mid(RIGA, 98, 1), "/") > 0 Then
    var_NUMCC = Mid(RIGA, 1, 6)
    var_NOME = Trim(Mid(RIGA, 8, 40))
    var_CAUS = Mid(RIGA, 50, 2)
    var_DARE = Trim(Mid(RIGA, 60, 14))
    var_AVERE = Trim(Mid(RIGA, 80, 14))
    var_VAL = Mid(RIGA, 96, 10)
    var_MITT = Mid(RIGA, 107, 4)
    var_ANOMAL = Trim(Mid(RIGA, 116, 10))

    If Not EOF(1) Then

    Line Input #1, RIGA
    var_DESCR = Mid(RIGA, 10, 40)
    var_DESCR2 = Mid(RIGA, 96, 30)
    Line Input #1, RIGA
    var_DESCR3 = Mid(RIGA, 10, 50)

    End If

    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DATACONT
    Foglio1.Range("B" & Trim(Str(cont))).Value = var_DIP
    Foglio1.Range("C" & Trim(Str(cont))).Value = var_COD
    Foglio1.Range("D" & Trim(Str(cont))).Value = var_NUMCC
    Foglio1.Range("E" & Trim(Str(cont))).Value = var_NOME
    Foglio1.Range("F" & Trim(Str(cont))).Value = var_CAUS
    Foglio1.Range("G" & Trim(Str(cont))).Value = var_DARE
    Foglio1.Range("H" & Trim(Str(cont))).Value = var_AVERE
    Foglio1.Range("I" & Trim(Str(cont))).Value = var_VAL
    Foglio1.Range("J" & Trim(Str(cont))).Value = var_MITT
    Foglio1.Range("K" & Trim(Str(cont))).Value = var_ANOMAL
    Foglio1.Range("L" & Trim(Str(cont))).Value = var_DESCR
    Foglio1.Range("M" & Trim(Str(cont))).Value = var_DESCR2
    Foglio1.Range("N" & Trim(Str(cont))).Value = var_DESCR3

    cont = cont + 1

    End If
    End If
    End if 'NEW LINE
    Wend
    Close #1
    'Call COD442
    MsgBox ("IMPORT TERMINATO!")
    End Sub


    Steve

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    it work very fine!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

    another little modify :-) i have seen that in the txt file is present a duplicate record line (ARG...) is possible to make a controlI in the column M during the import because is in this column are the index of duplicate....

    OK! I have finded in my archive an i have found this:

    End If
    .....
    ID = var_DESCR2
    Set Found_ID = Sheets("L0785").Columns("M:M").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then
    .....

    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DATACONT
    Foglio1.Range("B" & Trim(Str(cont))).Value = var_DIP
    Foglio1.Range("C" & Trim(Str(cont))).Value = var_COD

    ....

    and it work fine.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    my project is to finished at 50% ...

    Actually:

    this wbook is a simple paymnet un-payment controll

    In the sheet l0785_totale i import a txt file, the macro controll a duplicate and add up new record from txt file.

    my problem is:

    after the the import if the index in column S is equal (it change only from the variable Var_caus is possible 36 or 55 ) delete the two line and put in the sheet l0785_pagati only the line with the variable var_caus wit initi 36...

    if you see the line 116 and 117 is present the same index eccpt the initial in this case delete the two line from l0785_totale and put the entire line with init 36 in the seeh l0785_pagati make the controll from entire range of l0785_totale....

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional import from text file (2000 sr 1)

    1) Will the pairs with equal SERVIZIO (except for the initial 36 or 55) always be immediately below one another, or can there be other lines in between?

    2) Is it allowed to change the sort order of the list in L0785_TOTALE to bring the pairs together?

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    1) Will the pairs with equal SERVIZIO (except for the initial 36 or 55) always be immediately below one another, or can there be other lines in between? no, is a single case you serch in the total record of sheet... :-(

    2) Is it allowed to change the sort order of the list in L0785_TOTALE to bring the pairs together? is possible remain the same order but if you dont change is better :-(

    the best of the best is to make this controll during the import to save a time...

    if do you like i send the first and last txt file....?

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    i am very happy to to see again to you online....

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional import from text file (2000 sr 1)

    Yes, it would be handy if you could post a sample text file. Please keep it small, we only need a few lines.

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional import from text file (2000 sr 1)

    follow link this:

    old import 28052004
    www.gssitaly.com/l0785_28052004.zip

    new import
    www.gssitaly.com/l0785_31052004.zip



    soory if is big but I would want to be to capture the just cod 442

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional import from text file (2000 sr 1)

    Please create a small extract from those files and attach them to a post. I'm not going to work with 1700 KB large files.

Page 1 of 2 12 LastLast

Posting Permissions

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