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

    devide data from changed var... (2000 sr 1)

    i am sorry Hans, but i have maked a serch, and not have found my old post on this argument...
    I ahve this macro to import text(naturally import text is the first my work object;-() i would want:
    Refered var "var_DIP" and this can assuming variuos value during the import, for example 4500, make a new sheet named 4500 and put in all impoed value referd actually var...
    The macro found 4501 make a new sheet and put in the value refered var var _Dip
    ecc...
    Naturally for the next import if the sheet exsitis not make another and put to the botton the new value imported.

    Only for note:
    The value refering are in order into text file:
    4500
    4501
    4502
    4503
    ....

    i am very sorry, but bilieve ne i have maked seriuos serach on Lounge Forum.
    Tks for patience.

    Sub L0747()

    Dim riga As String
    Dim cont As Integer

    cont = 3
    FASE = 0

    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, 10, 10), "SPORTELLO:") > 0 Then
    var_DIP = Mid(riga, 21, 4)
    var_CC = Mid(riga, 35, 6)
    var_TIPOPROD = Mid(riga, 65, 4)
    var_DESCR = Mid(riga, 79, 30)
    var_DATTIV = Mid(riga, 122, 10)
    FASE = 1
    End If

    If InStr(Mid(riga, 15, 20), "STATO DELLA PROPOSTA") > 0 Then
    var_STATO = Mid(riga, 66, 15)
    FASE = 2
    End If

    If InStr(Mid(riga, 16, 1), "/") > 0 And InStr(Mid(riga, 30, 1), "/") > 0 Then
    var_COPE = Mid(riga, 22, 8)
    var_NDG = Mid(riga, 31, 9)
    var_NOME = Mid(riga, 54, 33)
    var_CODRUOLO = Mid(riga, 98, 2)
    var_DESCR2 = Mid(riga, 110, 20)
    FASE = 3
    End If


    If FASE = 3 Then
    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DIP
    Foglio1.Range("B" & Trim(Str(cont))).Value = var_CC
    Foglio1.Range("C" & Trim(Str(cont))).Value = var_TIPOPROD
    Foglio1.Range("D" & Trim(Str(cont))).Value = var_DESCR
    Foglio1.Range("E" & Trim(Str(cont))).Value = var_DATTIV
    Foglio1.Range("F" & Trim(Str(cont))).Value = var_STATO
    Foglio1.Range("G" & Trim(Str(cont))).Value = var_COPE
    Foglio1.Range("H" & Trim(Str(cont))).Value = var_NDG
    Foglio1.Range("I" & Trim(Str(cont))).Value = var_NOME
    Foglio1.Range("J" & Trim(Str(cont))).Value = var_CODRUOLO
    Foglio1.Range("K" & Trim(Str(cont))).Value = var_DESCR2
    FASE = 0
    cont = cont + 1

    End If

    End If
    Wend
    Close #1
    MsgBox ("MACRO TERMINATA!")
    End Sub

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

    Re: devide data from changed var... (2000 sr 1)

    The code in your previous thread <!post=Create sheet in base of month... (2000 sr 1),503344>Create sheet in base of month... (2000 sr 1)<!/post> shows how to test if a sheet already exists, and how to create a new one if not. You need the same kind of code here, only the name of the sheet will not be related to the month, but to var_DIP. You should be able to modify the code yourself.

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

    Re: devide data from changed var... (2000 sr 1)

    hi my teacher, after 3 hours of strange work! (cur and past a milion of line from my vba archive)
    but:
    How:
    1) to use a template fro each sheet created if i have one shett named "TEST"?
    2 Why the copy of dta init from line 2 and not from line 3?
    3) Send me a comment on my code.;-) from you all is accepted.
    T.


    Sub L0747()

    Dim riga As String
    Dim cont As Integer
    Dim Foglio1 As Worksheet
    Dim str1 As String, str2 As String

    cont = 3
    FASE = 0

    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, 10, 10), "SPORTELLO:") > 0 Then
    var_DIP = Mid(riga, 21, 4)
    var_CC = Mid(riga, 35, 6)
    var_TIPOPROD = Mid(riga, 65, 4)
    var_DESCR = Mid(riga, 79, 30)
    var_DATTIV = Mid(riga, 122, 10)
    FASE = 1
    End If

    If InStr(Mid(riga, 15, 20), "STATO DELLA PROPOSTA") > 0 Then
    var_STATO = Mid(riga, 66, 15)
    FASE = 2
    End If

    If InStr(Mid(riga, 16, 1), "/") > 0 And InStr(Mid(riga, 30, 1), "/") > 0 Then
    var_COPE = Mid(riga, 22, 8)
    var_NDG = Mid(riga, 31, 9)
    var_NOME = Mid(riga, 54, 33)
    var_CODRUOLO = Mid(riga, 98, 2)
    var_DESCR2 = Mid(riga, 110, 20)
    FASE = 3
    End If


    If FASE = 3 Then
    str1 = var_DIP
    If str1 <> str2 Then
    For Each c In ActiveWorkbook.Sheets
    If c.Name = var_DIP Then
    Set Foglio1 = c: Exit For
    Else
    Set Foglio1 = Nothing
    End If
    Next c
    If Foglio1 Is Nothing Then
    Set Foglio1 = ActiveWorkbook.Sheets.Add
    Foglio1.Name = var_DIP
    End If
    Else
    str2 = str1
    End If

    cont = Foglio1.Range("A65536").End(xlUp).Row + 1
    Foglio1.Range("A" & Trim(Str(cont))).Value = var_DIP
    Foglio1.Range("B" & Trim(Str(cont))).Value = var_CC
    Foglio1.Range("C" & Trim(Str(cont))).Value = var_TIPOPROD
    Foglio1.Range("D" & Trim(Str(cont))).Value = var_DESCR
    Foglio1.Range("E" & Trim(Str(cont))).Value = var_DATTIV
    Foglio1.Range("F" & Trim(Str(cont))).Value = var_STATO
    Foglio1.Range("G" & Trim(Str(cont))).Value = var_COPE
    Foglio1.Range("H" & Trim(Str(cont))).Value = var_NDG
    Foglio1.Range("I" & Trim(Str(cont))).Value = var_NOME
    Foglio1.Range("J" & Trim(Str(cont))).Value = var_CODRUOLO
    Foglio1.Range("K" & Trim(Str(cont))).Value = var_DESCR2
    FASE = 0
    End If

    End If
    Wend
    Close #1
    MsgBox ("MACRO TERMINATA!")
    End Sub

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

    Re: devide data from changed var... (2000 sr 1)

    Sal,

    1) If you want to copy sheet TEST instead of cxreating a blank new sheet, replace the line

    Set Foglio1 = ActiveWorkbook.Sheets.Add

    with

    Sheets("TEST").Copy After:=Sheets(Sheets.Count)
    Set Foglio1 = Sheets(Sheets.Count)

    2) Hopefully, the above code will take care of this too.
    3) I cannot test the code, but it looks OK.

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

    Re: devide data from changed var... (2000 sr 1)

    Hans attached is my last code and a piece of txt file....
    i dont understand why the macro not get the value after the line:

    COPE/NDG: 45741674/010495867 NOMINATIVO: SIMEC SISTEMI S.R.L. COD RUOLO: 01 DESCR: TITOLARE

    not get the data refered this line:

    COPE/NDG: 45472305/005808913 NOMINATIVO: AVALLONE ANNA COD RUOLO: 76 DESCR: PROCURATORE

    RICODIFICATA FIRMA: NO

    4500 L0747/C - SITUAZIONE FIRME E POTERI IN ACCORDI ALLA DATA: 09/07/2005 PAG. 8



    RICODIFICATI POTERI: NO

    REGOLARE RISPETTO ALLA PRIVACY: SI

    REGOLARE RISPETTO ALL'ANTIRICICLAGGIO: SI

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

    Re: devide data from changed var... (2000 sr 1)

    In fact, many more lines are skipped. Try the attached version.

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

    Re: devide data from changed var... (2000 sr 1)

    Work naturally!
    But i not have see but the error is present already into your last code, but for you all is permit.

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

    Re: devide data from changed var... (2000 sr 1)

    I hadn't taken into account that several "COPE/NDG" lines could follow each other without other data in between, and also the last record immediately before a line with "SPORTELLO" wasn't treated correctly.

    By the way, all those text files must be reports from a large database system. It would be MUCH easier if the data were output in a tabular format instead of in a report format. After all, the data are stored in tabular format in the database.

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

    Re: devide data from changed var... (2000 sr 1)

    Hans, you are a very gentleman...
    You have explain naturally with the max clearify the error.
    And tks for explain refered BIG DATA into sheet.
    But for me not is completly clear to:

    ...It would be MUCH easier if the data were output in a tabular format instead of in a report format. After all, the data are stored in tabular format in the database.

    For example?
    ....
    Sal.

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

    Re: devide data from changed var... (2000 sr 1)

    I cannot give an example, but those text files look like reports generated by a database system such as SQL Server or Oracle. The data used to create the report are stored in the form of one or more tables in the database, the way they are stored in an Access database, or in your spreadsheets. If you could obtain an exported version of those tables instead of the exported reports, you wouldn't have to use all this code to convert the data back into the form of a table.

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

    Re: devide data from changed var... (2000 sr 1)

    Good.. but for me oracle or sql server is s service very professional for me;-(
    In this case i use Access...
    You, naturally have maked for me many line of code to integrate access and Excle.
    Tks Hans in this case your code about Access and Excel are preciuos.

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

    Re: devide data from changed var... (2000 sr 1)

    What I meant, Sal, is:

    Where do the text files come from? Are they created by your own company, or are they sent to you by another company, or ...?

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

    Re: devide data from changed var... (2000 sr 1)

    I dont know if you know Extra for Attachment connection. Is an host application based on CICS, you know?
    Well at every day one of mine employee request a txt file from Host in CICS , and i work at evry time this txt to make a "visible " report from my comunity of Bos or other employee in to my company...
    Clear?

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

    Re: devide data from changed var... (2000 sr 1)

    I'm afraid I have no idea how one would export data in table form from CICS.

Posting Permissions

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